In this blog we would look at how to make a SOQL query selective.
Selective – High performance
Explanation: Salesforce being a multi-tenant architecture, it is necessary that any operation in an Org is done systematically. For a SOQL to run in an organised manner and not eating up extra resources, a few considerations must be taken into consideration.
The performance of a SOQL depends on the presence of a selective filter. If a SOQL query contains at least 1 selective filter, the query is said to be selective. If the SOQL query doesn’t contain a selective filter, the query is said to be un-selective and will require a full table scan.
So what’s a selective filter?
Let us first understand what a filter is.
Filters are necessary to retrieve precise data. From the above example, I need Accounts with the name Salesforce Binge. But consider if I did not write the highlighted part, the SOQL would return all the Accounts that there are in the system.
So adding filter(s) to a SOQL help get the required data. Salesforce advices that the usage of a selective filter, i.e., use of an indexed filter in SOQL, decides its performance.
How to determine if a filter/field has an index.
- If the filter is on a standard field, it’ll have an index if it is a primary key (
Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate, SystemModstamp). - Custom fields will have an index if they have been marked as
Unique or External Id
