Improve SOQL Performance

SOQL (Salesforce Object Query Language) used to search Salesforce data for specific information. SOQL allows us to specify source object, a list of fields to retrieve and condition for selecting rows in source object. SOQL returns List<Sobject>

To avoid long execution time, non-selective SOQL queries may be terminated by the system to improve the performance.

A query is selective when one of the query filters is on an indexed field and query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

Following fields are indexed by default and when used in SOQL WHERE clause improved the permormance.

1. Primary Key (Id, Name and Owner fields)
2. Foreign Keys ( lookup or master-detail relationship fields).
3. Audit dates ( systemModStamp).
4. Custom fields marked as External ID or Unique.

NOTE: You can raise an case with Salesforce Support to custom Indexes fields upon request.

But Custom index cannot be created on following types of fields

1. Multi-Select Picklists.
2. Currency fields in a Multicurrency Organization.
3. Long text fields.
4. Binary fields (field of type Blob, file, or encrypted text.).

Selective SOQL Queries example:

SELECT Id FROM Account WHERE Id IN (<list of Account IDs>)

SELECT Id FROM Account WHERE Name != ''

SELECT Id FROM Account WHERE Name != '' AND CustomField__c = 'ValueA'

SELECT Id FROM Account WHERE FormulatField__c = 'ValueA'

following rules have to be true in order to index a Formula Field

• The formula field contains fields from a single object only ( not relationship fields).
• The formula field doesn't reference any non-deterministic functions ( e.g. SYSDATE).
• The formula field contains references to Primary Keys(e.g. Id).
• The formula field doesn't reference any non-supported fields for including in indexes.