Oracle Web Services On Demand Guide > Best Practices for Designing Client Application > Best Practices for Integration Performance >

API Calls

Whenever possible, it is recommended that queries be as specific as possible to reduce the number of records in the result set. You must restrict the fields returned by queries to only the fields that are required by your process.

Queries that involve related child objects (that is child objects that are top-level objects), or complex queries that involve criteria from both parent and related child objects, might perform better if they are separated into multiple requests.

The following are also recommended:

• For Web Services v1.0 calls, use the child methods DeleteChild, InsertChild, and UpdateChild for child delete, insert, and update operations.
• Whenever possible, store your company's unique identifiers in the external system ID field on objects.

Performance of the Update and Insert Methods Versus the InsertOrUpdate Method

If you use Web Services v1.0, designing your application to use the Insert and Update methods can result in an increase in throughput compared to using the InsertOrUpdate method. This is due to the additional business logic and SQL statements executed during the InsertOrUpdate operation to determine whether the submitted records match any existing records in the Oracle CRM On Demand database. The performance and throughput improvement might not be apparent at lower volumes, but high volume applications could benefit from the direct update and insert operation, which does not require the additional business logic to determine whether the records already exist.

Working with Attachments

For a number of record types, you can include attachments with Web services requests. If you add attachments, remember that:

• Requests with large attachments perform more slowly than those with smaller attachments or no attachments. The maximum attachment size supported is 9MB.
• Requests with many attachments perform more slowly than those with a single attachment or no attachments.

If you are adding the same attachment to multiple records, you can take advantage of a reuse facility for attachments, see Attaching a File to Multiple Records. In this way, you do not include the content for each record in the request, you use the ContentId on one record and reference that ContentId from other records. For more information about using attachments in Web Services On Demand, see Using Attachments with Web Services On Demand.

Querying Login History and User Usage

When you use the LoginHistoryQueryPage and UserUsageQueryPage methods, the queries must be as specific as possible, that is, you must not query for all records with every request. For example, you can narrow the search results by:

• Querying for records owned by a specific UserID
• Querying for records covering a period of time

For more information, see LoginHistoryQueryPage and UserUsageQueryPage.

Using the QueryPage Method

The following are best practices for using the QueryPage method and similar methods like MetadataChangeSummaryQueryPage and SalesProcessQueryPage:

• If you intend to paginate through the snapshot of data returned by QueryPage, it is recommended to use a stateful Web services request. Using a stateless Web services request is not recommended because it can return the same record in multiple pages as you paginate though the results returned.
• If you do use stateless QueryPage operations and then paginate through the snapshot of data returned, it is recommended that the QueryPage request uses sort criteria.
• When formulating a query, use indexed fields, which are highlighted in green in the New List page in the Oracle CRM On Demand UI. Indexed fields are optimized for fast retrieval. As an example, use the indexed field ModifiedDateExt rather than the ModifiedDate field in QueryPage operations to provide better performance.
• Specify the best operators to make queries faster. For example, use the equality (=) operator instead of the * wildcard. Other operators might provide some functional flexibility, but can severely impair performance. Therefore, you must only use other operators when absolutely required.
• If filtering on a nonequality operator and not combining with any other filter criteria, change the sort order so that you are sorting on the same field that you are filtering.
• Specify only fields that you are intending to use. Adding all fields or specifying fields that are not required impacts the response time or throughput.
• If filtering on a custom field, make sure that the custom field is an indexed custom field. It might be required to migrate data from the existing field to the indexed custom field. For more information, see Using Indexed Custom Fields.
• Constrain filter criteria to return the least number of records possible (that is, queries must be as specific as possible).
• If you must return many sorted records, make sure that you sort on an indexed field.
• If you are using manager visibility (ViewMode="Manager" or ViewMode="EmployeeManager"), the query must contain an equality operator on an indexed field.
• Avoid unnecessary use of attachments by accessing attachments through the Oracle CRM On Demand UI whenever possible.
• Use the Web Services v2.0 API when querying for associated records, as Web Services v2.0 supports filtering and paging at the child level.
• Avoid filtering on a calculated field because it impacts performance.
• Use targeted searches and smaller page sizes for QueryPage operations to return less data in each request

  NOTE:  This can result in higher Web Service Operations Allotment usage if the user needs to page through multiple sets of records to find the right record.

For more information about the QueryPage method, see QueryPage, or QueryPage.

Querying Records with Foreign Key fields with the No Match Row Id Value

The No Match Row Id value for a foreign key field indicates one of the following:

• The foreign key refers to a nonexistent record.
• The foreign key value is empty.

There are different cases for which No Match Row Id is set for a foreign key field. For example, if you create a record without specifying a foreign key field in the insert operation, then the foreign key value is empty and is therefore set to No Match Row Id. In this case, querying the record returns <ForeignKeyId>No Match Row Id</ForeignKeyId>.

However, if you update a record and specify blank as the foreign key value, then it is set to empty. In this case, querying the record returns <ForeignKeyId></ForeignKeyId>.

You must take the No Match Row Id value into consideration for foreign key fields when querying records. If you try to retrieve all records where the foreign key value is not null, then the query returns records including those with the No Match Row Id value. Therefore, when querying a foreign key, the query criteria must include checking for null or not null as well as the No Match Row Id value.

For example, when querying for accounts with primary contact associated to the account, the search criteria must specify that the PrimaryContactId is not null and not equal to No Match Row Id, as shown in the following code sample:

<?xml version="1.0" encoding="utf-16" standalone="no"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Body>

<AccountQueryPage_Input xmlns="urn:crmondemand/ws/ecbs/account/10/2004">

<ListOfAccount>

   <Account searchspec="[PrimaryContactId] IS NOT NULL AND [PrimaryContactId] &lt;&gt; 'No Match Row Id'">

   <AccountName />

   <PrimaryContactId></PrimaryContactId>

   </Account>

</ListOfAccount>

</AccountQueryPage_Input>

</soap:Body>

</soap:Envelope>

Using Indexed Custom Fields

To optimize performance, you can use custom fields that have been indexed for specific record types. Indexed fields are special fields that improve the response time during the search process or sorting on a particular list. Indexed custom fields are preconfigured in the Oracle CRM On Demand database. You can change the labels on the indexed custom fields, but you cannot change the integration tags.

NOTE:  As an option, you can choose to migrate your data from nonindexed to indexed custom fields to increase the performance of the Web services queries that your users execute. To migrate the existing data to the available record types, use the export and import functionality of Oracle CRM On Demand or Web services. For more information about exporting and importing data, see Oracle CRM On Demand Online Help.

Indexed custom fields are prefixed with Indexed as a default.