Selective SOQL Queries in Salesforce

Updated by

In Salesforce SOQL, a query is considered selective when it contains at least one selective filter. For the best performance, SOQL queries must be selective, particularly those inside triggers. A non-selective query may cause different programmatic elements to fail.

In this Salesforce tutorial, I will explain the importance of Selective SOQL queries in Salesforce and the approaches to writing them.

What are Selective SOQL Queries in Salesforce?

In Salesforce SOQL, selective queries efficiently fetch records from the database using indexed fields and conditions. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions. Salesforce evaluates the selectivity of a query filter condition based on the indexes and the percentage of records filtered. 

Example of Selective and Non-Selective Queries in Salesforce

Now, we will execute a couple of SOQL queries where we will see the difference between the selective and non-selective queries in Salesforce Apex.

Selective SOQL Query:

SELECT Id, Name 
FROM Account 
WHERE CreatedDate >= LAST_YEAR
AND Industry = 'Technology'

Output:

Selective Salesforce SOQL queries

We use indexed fields in the selective query. In the above query, we have CreatedDate and Industry as indexed fields.

Non-Selective Query:

SELECT Id, Name 
FROM Account 
WHERE Name LIKE '%Tech%' 
OR NumberOfEmployees > 1000

In the above Non-selective SOQL query, the operator with ‘%’ prevents index usage. The OR operator without indexed fields might lead to performance issues.

Criteria of Selective SOQL Queries

The following criteria are used to add an indexed formula field and make a SOQL query selective.

  • The WHERE clause should filter a limited set of records.
  • No more than 30% of records should be returned for a single object.
  • No more than 15% of records for related objects should be returned for the child relationship.
  • Limits should vary based on the total number of records.
  • The formula referencing the value of format DATEVALUE(Date/Time field) cannot be indexed.
  • If the formula references any Lookup fields, ensure that the field’s deletion behavior is not set to “Clear the value of this field” under the option “What to do if the lookup record is deleted?“.
  • The formula field must not reference unsupported fields for inclusion in indexes.

Approaches to Implement Selective Queries

Now, we will examine the following approaches to implementing