Optimize SOQL Filter in Apex Code

We create new custom applications in Salesforce or customize the existing applications using custom code like Visual Force Page, Aura component, LWC component, and Apex code. While creating custom code, we need to keep in mind application/page/component performance. For better performance data retrieval is one of the major factors. Our query should be selective for better performance. This post will explain how to optimize SOQL filter in apex code to make it selective.

Before we move ahead, let us first see what is selective query in Salesforce. As per Salesforce documentation

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold.

It means we should add at least one filter criteria in SOQL which is on the index field. By default, we have the following standard fields which are index fields.

• Primary keys – Id, Name, and OwnerId fields
• Foreign keys – CreatedById, LastModifiedById, lookup, master-detail relationship fields
• Audit dates – CreatedDate and SystemModstamp fields
• RecordType fields – Indexed for all standard objects
• Custom fields that are marked as External ID or Unique

We should add one of these fields in SOQL filter criteria to make it selective. In case our query is returning huge records even after applying these selective indexes then we should also handle the maximum threshold limit of returned records. Let us see this maximum threshold limit concept in detail.

Below is rule to identify what is maximum threshold for any selective query

1. For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million.
2. The maximum threshold limit for the standard index is 1 million records which will be reached when we have 5.6 million records in total
3. For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent of all records after that first million.
4. The maximum threshold limit for the custom index is 333,333 records which will be reached when we have 5.6 million records in total

Standard Index Threshold

The below image is showing the maximum threshold for the query results for the standard index.

So take an example we have below query to fetch Opportunity records

SELECT Id FROM Opportunity  WHERE CreatedDate = THIS_YEAR

On the basis of the above query here is the maximum threshold for this query to become selective.

So if our Opportunity has 3,43,329 records then the maximum threshold will be 102,999. If we have 80,000 records for last year in Opportunity object then it will be under our maximum threshold. So this index on a created date is selective and optimized by a query analyzer. If last year’s opportunity record is beyond 102,999 then it will exceed the maximum threshold, so the query will not be optimized.

If our Opportunity has 24,33,290 records then the maximum threshold will be 5,14,993. If we have 180,000 records for last year in Opportunity object then it will be under our maximum threshold. So this index on a created date is selective and optimized by a query analyzer. If same time we have 6,00,009 records in Opportunity last year then it will exceed the maximum threshold and not be optimized.

Custom Index Threshold

In some situations standard index is not used in SOQL in that case we should get a custom index created. This is required when we run SOQL for reporting or batch processing. Custom index is created by the Salesforce Support team, so if you need it then create a case with complete requirement detail.

The below image is showing the maximum threshold for the query results for the custom index.

So take an example we have below query to fetch Opportunity records. Status__c field is custom indexed.

SELECT Id FROM Opportunity  WHERE Staus__c= 'Closed'

On the basis of the above query here is the maximum threshold for this query to become selective.

So if our Opportunity has 3,43,329 records then the maximum threshold will be 34,332. If we have 80,000 records for last year in Opportunity object then it will be under our maximum threshold. So this index on a Status field is selective and optimized by a query analyzer. If the opportunity record is beyond 34,332 then it will exceed the maximum threshold, so the query will not be optimized.

If our Opportunity has 24,33,290 records then the maximum threshold will be 1,17,664. If we have 1,80,000 closed records in the Opportunity object then it will be under our maximum threshold. So this index on a Status field is selective and optimized by a query analyzer. If at the same time we have 6,00,009 records in Opportunity which are closed then it will exceed the maximum threshold and not be optimized by the optimizer.

Both Standard and Custom Index in query

• If both standard and custom indexes are used in SOQL filters then the system will use an index that is under the threshold and it will be a selective query.
• If both standard and custom indexes are used in SOQL filters and both are under threshold then a cost-effective index will be used.
• If both indexes are returning records beyond the maximum threshold then that query will not be selective and not optimized.

Is index always required in SOQL?

We should always try to use one index in the SOQL query especially where we are querying a large set of records for batch processing or report creation.

Even we have a configuration object or small object, there also we should use an index. What we can do is, we can create a default record type in the configuration object and use that in SOQL. This way queries will be optimized.

What should avoid in SOQL Filter?

We should avoid the below points for making query selective.

• We should avoid !=null (not equal to null) condition in where clause
• Avoid wildcard (‘%salesforce%’) condition. Instead of this, SOSL is a better fit in most the situation
• Avoid null in contains like a city in (‘Delhi’,’Mumbai,null)

Summary:

SOQL is important for application performance. Our query should be selective always to optimize it. For this. try to use standard index, if that is not fitting to your use case and huge records are returning then get custom index created from Salesforce Support. Try to avoid nulls in filter conditions.

Similar Posts:

Optimizing Salesforce Apex Code

Optimizing Loop in Apex Code

Optimize Code by Disabling Debug Mode

Optimize Apex Code by Metadata Caching

Referenced Posts for Optimize soql:

Make SOQL query selective

Working with Very Large SOQL Queries