SOQL is Salesforce Object Query Language and is used for fetching the data from Salesforce standard and custom objects. It is similar to SQL which is used in normal databases like SQL Server or MySQL. However, there are key factors which make SOQL different than SQL like
- SOQL is only used for fetching the records or we can say it only supports SELECT statements. DML is not allowed in SOQL
- SOQL has his own way of handling the relationships between different objects and does not support JOIN keyword
You can check out this article to get the complete list of difference.
Why do we see SOQL query performance degradation over the time?
The data in your organization is never same and keeps on increasing over the time. If you do not have proper data purging and archiving mechanism in place, your object’s data may increase drastically over the time. In this situation, your SOQL query may start behaving differently and you may notice performance degradation over the time. This happens because your object’s record size is increasing and SOQL needs to go through all those records now. More the number of records more is the time taken by the query.
How to increase the performance of your SOQL query or keep your query performance consistent?
There is no hard and fast rule which can increase your query performance or guarantee the same performance always. However, by following some best practices, you can reduce the chances of query performance degradation.
1. Use of appropriate filters and avoid using != or null filters
- Only fetch those fields which are important for your functionality
- Use appropriate filters and always try to get exact data with the proper filter in place
- Avoid using “not equal” (!=) as they lead to full table scan
- Avoid using leading “%” in LIKE operator as this lead to full table scan
- Try to index fields in your filter conditions.
2. Use Indexed fields in the filter
- Using indexed fields in the filter can increase query performance. Always try to use indexed filters in your query for better performance
- Salesforce automatically indexes some of the standard fields like primary key (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate, SystemModstamp)
- Salesforce automatically indexes all Unique and ExternalID custom fields.
- If your filter does not have above standard or custom fields, you can raise a case to customer support to have your custom field indexed. Salesforce supports custom indexes on custom fields, except for multi-select picklists, text areas (long), text areas (rich), non-deterministic formula fields, and encrypted text fields.
Does indexed filters always give best query performance?
Having indexed filter will definitely improve your query performance if they are being used. Yes, indexed fields are not being used always and there are certain limitations about this as well. Indexed fields are only considered when the query is Selective. If a query is non-selective, this will lead to full table scan and index filters will not be used. The full table will definitely give you the poor performance compared to indexed tables.
What is Selective Query?
You can consider a query as selective if:
- If Standard Indexed Fields are used and the filter matches less than 30% of the first million records and less than 15% of additional records, up to a maximum of one million records.
- If Custom Indexed Fields are used and the filter matches less than 10% of the total records, up to a maximum of 333,333 records.
Also, there are different considerations for AND and OR conditions as well in your WHERE clause:
- For AND, the Force.com query optimizer uses the indexes unless one of them returns more than 20% of the object’s records or 666,666 total records.
- For OR, the Force.com query optimizer uses the indexes unless they all return more than 10% of the object’s records or 333,333 total records.
Note: All fields in the OR clause must be indexed for any index to be used.
- For LIKE, the Force.com query optimizer does not use its internal statistics table. Instead, it samples up to 100,000 records of actual data to decide whether to use the custom index. Avoid using leading % in LIKE operator.
What is Two Column Indexing?
Two-column custom indexes are a specialized feature of the Salesforce platform. They are useful for list views and situations where you want to use one field to select records to display and another field to sort them. For example, an Account list view that selects by State and sorts by City can use a two-column index with State in the first column and City in the second.
Two-column indexes are subject to the same restrictions as single-column indexes, with one exception. Two-column indexes can have nulls in the second column, whereas single-column indexes can’t unless Salesforce Customer Support explicitly enabled the option to include nulls.
What if the query still timeouts even after following all above best practices?
If you have followed all above best practice and have included only indexed filter in your query and your query still timeouts, then Salesforce has very powerful tool for you called Skinny Table which can certainly increase chances of better query performance.
What is Skinny Table and how to use these?
Skinny table is the most powerful solution to improve your query performance. For each object table, Salesforce maintains other, separate tables at the database level for standard and custom fields. This separation ordinarily requires a join when a query contains both kinds of fields. A skinny table contains both kinds of fields to avoid joins and does not include soft-deleted records.
Skinny table contains organization-specific data only and can contain up to 100 fields.Skinny tables can contain the following types of fields.
- Date and time
- Picklist (multi-select)
- Text area
- Text area (long)
Things to keep in mind:
Skinny tables are most useful with tables containing millions of records to improve the performance of read-only operations. such as reports. Skinny tables aren’t a magic wand to wave at performance problems. There’s overhead in maintaining separate tables that hold copies of live data. Using them in an inappropriate context can lead to performance degradation instead of improvement.
Skinny tables can contain a maximum of 100 columns.
Skinny tables cannot contain fields from other objects.
Salesforce provides different tool and mechanism to keep your SOQL performance up to the mark provided you are making use of these tools and follow best practices. One of the most important factor which I did not discuss in this article is Data Purging and Archiving, which is equally important so that your organization does not overload with data and impact your SOQL performance.