Selective SOQL Queries in Salesforce

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.

Table of Contents

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:

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 selective queries in Salesforce SOQL.

1. Use a selective filter on an indexed field:

Below is an example of a query with a filter on a default indexed field.

SELECT Id, Name FROM Account WHERE Name = 'Burlington'

In the above SOQL query, the name field is indexed by default, and by adding a filter for the Name, we reduced the number of records to be scanned, which makes this SOQL query selective.

Though using the indexed field is the criterion to make the query selective, it is NOT selective when it returns more records than the limit.

2. Avoid Null checks on the Non-Indexed Fields

SOQL queries with ‘WHERE Field__c = null’ or ‘Field__c != null’ on non-indexed fields are less selective. Instead, try to include additional conditions involving indexed fields.

3. Using ‘FORCE’ or ‘USE’ indexing:

If necessary, and if you want to use indexes explicitly, include the FORCE INDEX clause. A SOQL using the FORCE index will be written using the following syntax:

SELECT Id, Name FROM Account WHERE IsActive = true AND Industry = 'Technology' FORCE INDEX

4. Limit the number of records

Using indexed fields but returning more records than the limit, the SOQL query will not be selective. To handle this situation, use the ‘LIMIT’ keyword to restrict the number of records returned.

The SOQL query written using LIMIT will be written using the following syntax:

SELECT Id, Name FROM Opportunity WHERE StageName = 'Closed Won' LIMIT 100

With the approaches in the above examples, you can write selective SOQL queries effectively. By using indexed fields and ensuring the returned records do not exceed the limit, we can ensure selective query performance, minimize resource consumption, and enhance user experience.

Methods to Avoid That Can Cause a Query NON-SELECTIVE

Now, we will see the following reasons that can make a query NON-SELECTIVE so that we can avoid them while writing selective SOQL queries.

1. Using negative operators (! = and NOT)

When we use negative operators such as != and NOT in SOQL, we force full table scans and evaluate all records to find those that do not meet the condition. This bypasses indexing and results in a full scan of the table, making the query non-selective.

Non-selective SOQL query with negative operators:

 SELECT Id, Name FROM Opportunity WHERE Name != 'Edge Emergency Generator'

2. Comparing with an empty or null value:

For selective SOQL queries, comparing with Empty or Null values is inefficient. Comparisons with Null or Blank (‘ ‘) cannot use indexes because null values are not indexed in Salesforce. Thus, the query must scan every record to identify matches.

A selective SOQL query with a NULL or empty value.

 SELECT Id, Name FROM Opportunity WHERE Name != ''

3. Using Comparison Operators on Text Fields:

Using custom operators (>, <, >=, <=) with text-based fields leads to complex string comparison. Text fields are not suited for comparison operators because these queries involve lexical (alphabetical) comparisons, which are resource-intensive and cannot utilize indexing efficiently.

Example of SOQL query with comparison on text fields:

SELECT AccountId, OrderReferenceNumber FROM Order WHERE OrderReferenceNumber > 100

4. Using a LIKE condition with a leading % wildcard:

An SOQL query with a leading ‘%’ wildcard indicates that the search can match any part of the text, resulting in no fixed starting point for indexes to start with. As a result, Salesforce must perform a full scan of all records in the queried table.

 SELECT Id, Name FROM Opportunity WHERE Name LIKE '%a%

By avoiding the methods that we discussed in the above examples, we can ensure that Selective queries will not become non-selective.