Fine Tune Your Salesforce Queries

We create custom applications in Salesforce that are built on top of customer or transactional data from Salesforce data tables. Writing efficient queries is crucial to maintaining the performance of these applications and ensuring that we don't run into Salesforce limits. There are certain optimization techniques you need to follow to make your query efficient. This article will shed light on those techniques.

About Query Performance

It is obvious that the performance of your query completely depends on the complexity of data you currently have in your production org. You can write an efficient query that works in one environment but could fail in a different environment. So it is important to understand the current state of your data in the production environment. You should also have some idea regarding the future growth of your production data so that you can plan your queries accordingly. Make planning queries as part of your development cycle. Make a routine to revisit old queries in your production environment to make sure that those queries are still efficient. This article will also walk you through tools you could use to measure query performance in Salesforce. 

The first step towards an optimized query is to use index fields in your WHERE clause filters.

Indexes

Certain fields are indexed out of the box in Salesforce. When writing queries, make sure you use these fields as filters so that your query is optimized. 

Other than the default indexed fields, you can also make custom fields as indexes. You need to contact Salesforce support to do this. Please note that external ID from the list above falls under the custom index category. 

If indexing is the way to go, is it all about making all filters as index and you are good? The answer is, "No." Salesforce applies a "Selectivity Threshold" for your queries, and as long as your query is pulling data under that threshold, your query is selective or optimized. What are the thresholds? 

• Standard index threshold: 30% of the first 1M records and 15% of the remaining records with a maximum limit of 1M records
• Custom index threshold: 10% of the first 1M records and 5% of the remaining records with a maximum limit of 333,333 records

For example, consider the following query and suppose you have approximately 2 million opportunity records. 

SELECT Id, name from Opportunity where RecordTypeId = '1234'

Since you are using a standard index, your threshold is 450k (300k + 150k). If your query returns more than 450k records, your query is not selective, and Salesforce Optimizer would rather go for a table scan. If you use a custom index filter, your threshold would be 150k. 

Common Mistakes in Queries

1. Using != or NOT - Even if the field is indexed, using != or NOT will not make your queries selective. Instead, use IN or =. 
2. Use %wildcards% - If you are running into using %wildcards in your queries, step back and ask yourself if SOSL is a better option. 
3. Avoid nulls - Consider the following code:

List<Opportunity> oppList = [SELECT id, customLookup__c from Opportunity];
list<String> Ids = new List<String>();
for (Opporunity opp : oppList){
Ids.add(opp.customLookup__c);
}

List<customObject__c> objs = [SELECT id from customObject__c where Id in: Ids];

If you have an opportunity with a customlookup__c=null, your list will have a null value, and your query on the customObject__c is not going to be selective. To fix this, add customLookup__c to Ids list only if customLookup__c !=null. 

4. Deleted records can affect query performance - Use isDeleted = false or empty the recycle bin to improve query performance. 

Check Your Query Performance

There are two ways you can check the performance of your query without actually running the query.

1. Salesforce Query REST resource with an 'Explain' parameter 
2. Query Plan from the developer console

Query Performance Attributes

When you run the query plan using the above two methods, you will see "query plans" with the following key attributes. 

• Cardinality - The estimated number of records the leading operation type would return 
• SObject Cardinality - The approximate count of records for the query object
• Leading operation type - The primary operation type that Salesforce will use to optimize your query; Two common types are index and tablescan. 
• relativeCost or Cost - Cost of the query compared to the selectivity threshold; If cost is above 1, the query is not selective. 

Please note that you would find multiple plans in the response most of the time. The plan with the minimum cost will be used by Salesforce.

Conclusion

Writing efficient queries is critical in maintaining the performance of your system in Salesforce. There are certain techniques to make your query effective, such as using indexes as filters and keeping your query results within the selectivity threshold. There are also tools such as query plan in the developer console or using the explain parameter in query REST resource to measure the query performance. Make sure you use these techniques and tools in your design sessions so that you can write selective queries and ensure the top performance of your applications.