Why SOQL Optimization is Needed ?
SOQL is a tool that lets you access records in your Salesforce database. When you don’t write good SOQL queries you’re going to hit the governor limit of non selective query. There is a very common issue that most of us have came across when there are more than 100k records in a given table. For e.g. If we cross the threshold of 100k records, we might start to see some “non-selective query” type errors mentioned below:
SOQL query should always be selective meaning it must contain at least one query filter on an indexed field and that filter in turn reduces the resulting number of rows below a Salesforce-determined threshold*.
Analyze SOQL queries using Query Plan tool
When it comes to optimize the SOQL, we first need to analyze the SOQL to identfiy the weak areas of SOQL and it’s running performance. The Query Plan tool shows the cost of Salesforce executing a given SOQL query given the database statistics known at that time.
Enabling Query Plan
Salesforce doesn’t have the Query Plan feature enabled by default. To enable it, open the Developer Console –> Help –> Preferences and then check “Enable Query Plan”.
Using Query Plan
After the Query Plan is enabled, open the Query Editor tab at the bottom, enter a SOQL query, and click the Query Plan button. This will open a modal showing the cost of the SOQL query along with helpful tips.
- Cardinality – The estimated number of records that will be returned by the query.
- Fields – The indexed field(s) used by the Query Optimizer. If the leading operation type is Index, the fields value is Index. Otherwise, the fields value is null.
- Leading Operation Type – The primary operation type that Salesforce will use to optimize the query.
- Index – The query will use an index on the queried object.
- Sharing – The query will use an index based on the sharing rules associated with the user who is executing the query. If there are sharing rules that limit which records that user can access, Salesforce can use those rules to optimize the query.
- TableScan – The query will scan all records for the queried object.
- Other – The query will use optimizations internal to Salesforce.
- Cost – The cost of the query compared to the Force.com Query Optimizer’s selectivity threshold. Values above 1 mean that the query won’t be selective.
In above example, we query all Accounts with non-selective filter which is “Is Deleted”, the cost is high.
- SObject Cardinality – The approximate record count for the queried object.
Optimize SOQL using Filters and Custom Index :
- We must only select the fields which are needed but not all fields.
- We should try to use the limit operator to reduce how many records are returned.
- We must use indexed fields- selective filters
Here are the three examples which shows how the initial non-selective query is being optimized with adding more filters to it.
// Non- Selective Query- This will return everything from the table
List<Contact> = [SELECT Id, Name, Email, OwnerId from Contact ];
// Better Query than above one, we’ve got a filter but it is not on an indexed field
List<Contact> = [SELECT Id, Name, Email, OwnerId from Contact Where Status = ‘Active’];
// Even better since Owner is one of the standard indexed fields. The performance of the SOQL //query improves when two or more filters used in the WHERE clause meet the mentioned //conditions.
List<Contact> = [SELECT Id, Name, Email, OwnerId from Contact Where Status = ‘Active’ and OwnerId IN (‘005d0000001acWo’,’005d0000001mnTo’) ];
Developer must use Indexed fields to improve the performance of SOQL query.
Custom Index and Considerations
Salesforce leverages indexes heavily to optimize query performance whenever possible. An index is a separate data structure that stores field values in such a way that it’s fast to search on them to identify the rows we’d like returned. The general concept is the same as a book’s index.
What fields are pre- indexed?
- Object Ids
- Object Name – Auto Number or Text Field.
- Custom Relationsips – Master Details and Lookups.
- External Ids
- Unique Fields
- Owner Field
Use an index in the where clause whenever possible. However, this doesn’t guarantee that it will be used. Salesforce has various rules around when an index will be used or not based on its selectivity.
- An indexed field will not be used under the following conditions:
- Not In (…)
e.g. Status__c Not IN (‘Active’) – Even if Status__c is indexed field, it won’t be considered as indexed field and might occur non-selective error.
- Like with wildcards “%Field%”
- Excludes for picklist values
- Not Like
- Comparison Operators Paired With Text Fields. Text_Field <, >, <=, >
Not sure if a field is indexed or not ? Open an object’s definition page and notice that there’s an “Indexed” column. If it’s checked, it’s indexed.
How to get field Indexed which is not indexed ?
Please submit a case with Salesforce Support.