Efficient SOQL Queries

Here are some recommendations to follow to improve SOQL queries performance:

Apply logic inside queries

Instead of fetching all records and then applying your filter logic, you should only fetch records, and fields which are necessary.

For instance:

List<Contact> conlist = [SELECT Id,BillingState FROM Contact];
List<Contact> conListFlorida = new List<Contact>();
for(Contact conObj: conlIst){
	if(conObj.BillingState == 'Florida'){
		conListFlorida.add(conObj);
  }
}

Here, we could have easily created conListFlorida by:

List<Contact> conlistFlorida = [SELECT Id,BillingState 
  FROM Contact
  WHERE BillingState = 'Florida'];

We discussed wildcards in the LIKE operator. When the search is on a large number of records and contains wildcards this might degrade performance. Salesforce has a native SOSL (Salesforce Object Search Language) which can help to improve search speed.

Avoid using comparison operators with Text fields

It is difficult to compare 2 Strings and might take a long time if your object has a large number of rows. So, as much as possible we should not compare a field’s value with comparison operators (<, >, <=, >=).

Explicitly filter out Null values

Example:

SELECT Id, account.BillingState 
FROM Contact
WHERE AccountId != Null
AND Account.BillingState = 'Florida'

Here only those contacts will be fetched which have an accountID, and then the filter will be applied on account’s billing state.

SOQL query is termed selective if we apply a where clause to an index field. If you have studied databases in-depth, you might know those database columns that are indexed can be searched faster and more efficiently.

By default, Salesforce index the following fields:

  • Standard fields: Id, Name, CreatedDate, LastModifiedDate
  • Relationships fields: lookup and master-detail relationships
  • Text fields marked as unique
  • External ID fields

These are standard indexes. You can also raise a case to Salesforce to make a custom field as indexed. This might be helpful when you are querying on a field too often, and it is causing performance issues.

Thus applying a WHERE filter on index fields can make a query selective. There is an exception though. If there are more than 1 million records of the object being queried, the index filter should return 30% threshold of first million, and 15% threshold for the remaining records, otherwise the index will not be applied and the query will be non-selective. In other words, if the SOQL query engine accepts the index to run the query, we call it a selective query.

Note that this threshold is 10% and 5% for custom indexed fields, instead of 30%, and 15%.

The following query is a selective query as the filter is applied on createdDate which is a standard index field.

Select Id, Name 
FROM Appointment__c 
WHERE CreatedDate = this_year

In queries containing more than 1 filter_expression, the query engine determines if, and which index should be applied.

Apply Sort Optimization

Let’s say you applied an index on a custom field to improve performance by raising a case with Salesforce. It worked perfectly, but as data grew, and grew, the index is no longer applied because it does not meet the threshold requirement (10%). And your query is now running slow.

Interestingly, you can apply ORDER BY clause with a LIMIT clause to mitigate this. You might wonder, that a ORDER BY operation might increase the load of the query, instead of helping it. The reality is indexed fields are always sorted, and thus no extra load is shed on the query. Together with a limited number of rows to fetch, thanks to LIMIT clause, the SOQL engine might select the index even if it does not meet the threshold requirement. However make sure that the field you are sorting on does not contain NULL values, otherwise, the index will not be applied.

Sort optimization is useful, when you are querying most recent updated records, or want to fetch records in a boundary (Top 50, Last 50, etc).

Avoid filtering on NULL values

By default, the NULL values in a custom field index are not maintained in index tables, and if we want to filter a field which is equal to null, the index will not be applied. Consequently, the query will scan all millions of rows to find the required data, and performance would degrade.

Example:

Select Id 
FROM Contact 
WHERE MRN__c = null

Here even if a custom index is applied on MRN__c, it will not be selected, and the query will not be selective.

Consider using skinny tables

Salesforce can allow you to create skinny tables, which are a copy of the object’s table, can contain up to 100 fields, can include both standard and custom fields.

How does a Skinny table increase performance:

  1. There is no need to join standard and custom field tables. Conceptually, Salesforce maintains a separate table for standard and custom field for objects and performs an internal join while doing the queries. For a skinny table, this join is eliminated.
  2. Skinny tables do not contain soft deleted records(records in the recycle bin) and therefore run faster.

These Principles work for Report and List Views as well

Many of the features we discussed, work equally well, and should be applied while creating Salesforce Reports and List Views. As you can imagine, both Salesforce Reports and List Views display a set of fields, and we can apply filters and sorting on the fields of the report. The underlying architecture for them is, you already know, SOQL.

Always plan for the future ahead

While designing your queries you must always see how that data is going to grow over time, and how it might degrade performance. A non-efficient query might be working just fine, for now, but can fail when data size grows. To prevent a break/ performance degrade always apply the best practices mentioned above.