QUESTION:
I have been getting the following error(s) when attempting access data from Salesforce, how can I resolve?
- "The operation has timed out"
- "QUERY_TIMEOUT: Your request was running for too long"
ANSWER:
These errors occur occasionally and typically during peak business hours when there is a lot of API traffic and the job is trying to access a large amount of data, or trying to access a subset of data from a very large table.
****The operation has timed out****
This is a Salesforce API .net timeout error (the API never responds to our query request to download the records). Try changing the “Salesforce Timeout (in minutes)” setting in DemandTools Options to 10 minutes. This tells the API to wait 10 minutes before aborting the .net request (the default setting is 2 minutes). It does not guarantee the job will start, but it at least allows more time in the queue to kick off the job.
****QUERY_TIMEOUT: Your request was running for too long****
This error is being generated by the Salesforce API and is slightly different than "The operation has timed out" error described above.
In this particular case the API has accepted the query (our .net request) but it internally timed out within the API before completing the request. These errors typically occur after the job has been running for 2 minutes. This is an internal setting within the Salesforce API and CANNOT be adjusted in DemandTools Options like the "Salesforce Timeout(in minutes)" setting.
****Possible ways to get the job to start include****
- Subset where possible to look at smaller groups of records. When subsetting ideally use an indexed field, otherwise subsetting may not work either due to the number of records it needs to churn through to get those specific records.
Note: Per the detailed suggestions provided by Salesforce below, an index is NEVER USED if the filter is "NotEqualTo". In DemandTools that is the != operator. Whenever possible, change logic to use an = condition.
- Keep trying the job. Hitting the API at just the right time may result in the job starting.
- Try running the job after hours (i.e. in the evening or early in the morning - outside of peak business hours).
If this continues to be a problem, we suggest contacting Salesforce Premier Support so they can look at API resources specifically for your Salesforce organization to see if any adjustments can be made on their end to allow larger queries to process without encountering this error.
Salesforce Partner Support also provided the following information regarding indexed fields, and suggestions for subsetting.
*****Salesforce API Timeout Suggestions*****
**Standard Fields that are automatically indexed. Standard Index.
# Record_Type_Id
# Division
# Created_Date
# System_Modstamp(Last_Modified_Date)
# Name
# Lookup/MasterDetail fields
# Email (Contacts and Leads)
** Custom fields that are automatically indexed. Custom Index
# Custom fields with Unique attribute.
# Custom fields with ExternalId attribute.
# Any fields manually indexed Salesforce support.
Suggestions on efficient use of indexed filter fields
- Indexes are used by the query optimizer whenever possible to improve query performance. There are some rules however that the query optimizer follows before using an index.
- It is possible for an index not to be used if they fail to meet these rules. In such cases, the query optimizer has to perform a row by row read of the database to get records, which can increase the amount of time for the query run time.
- The larger an object grows, the need for using efficient filter criteria’s also grows.
Rules are around whether the filter field that is indexed would be selective enough
- Current criteria for a Standard Index being selective is if the filter criteria by itself returns 30% upto 1 million records of the total data for the Object.
- Current criteria for a custom index being selective is if the filter criteria by itself returns 10% upto 333K records of the total data for the Object.
- When there are 2 or more OR filter criteria, all the fields in the OR group must be indexed. The cumulative total of all the filters in the OR group must be below the main criteria.
- If there is a mix of custom and standard index fields in the OR filter group, the custom Index criteria is used.
- Index is never used if the filter is a NotEqualTo filter.
Examples
Scenario: Account object with 5 million records total. The Division field is standard indexed and "ExternalId__c" is a custom indexed field. Type__c field is not indexed.
Query 1:
Select id from account where Division = 'West' and type__c='partner';
For the "Division" index to be used,
(Select count() from account where Division = 'West') must be 1 million records or less.
Query 2:
Select id from account where ExternalId__c = '1234' and type__c='partner';
For the "ExternalId__c" index to be used,
(Select count() from account where ExternalId__c = '1234') must be 333k records or less.
Query 3#1:
Select id from account where (Division = 'West' OR ExternalId__c = '1234') and type__c='partner';
For the OR group index to be used ,
(Select count() from account where Division = 'West') + (Select count() from account where ExternalId__c = '1234') must be 333k records or less.
Using the threshold for custom index as there is a custom indexed field in the OR group
Query 3#2:
Select id from account where (Division = 'West' OR type__c='partner');
The OR group will not be used as one of the OR fields is not indexed.
Query 4:
Select id from account where Division != 'West' and type__c='partner';
No index is used as the only indexed field in filter criteria is using NotEqalTo filter.
Salesforce support assistance for query performance issues
For administrators trying to determine efficient filter criteria’s, you may open a case with salesforce support for the following:
- Request current list of manual custom indexed fields.
- Request a custom index field
- Support will need to get a sample of the query that is currently timing out as we have to evaluate possible alternatives before creating custom index.
- Additionally, there is an evaluation of the requested index itself to determine if it will be beneficial.
- Indexes do have an overall performance cost by themselves, and we must be diligent to ensure that the indexes we apply are beneficial.
- Disable api query timeout on Salesforce side.
- Support will need to get a sample of the query that is currently timing out as we have to evaluate possible alternatives before disabling the query timeout.
- Support will first evaluate possible query changes and/or custom indexes first before considering disabling the api query timeout limit.
Additional items that can be done by customers
- Ensure that your recycle bin is emptied frequently as these records do impact query performance overall.
- For very large data sets, consider breaking up jobs into smaller batches and/or evaluating your data model to address the issues.
- One can manually create a custom indexed field by making the field either UNIQUE or an EXTERNALID. E.G, an autonumber ExternalId field used to segment data for efficient queries.
