Do you have a query problem? There is a silent killer lurking in your org right now and it’s really hurting user adoption due to query performance. So, what does it mean? These are some symptoms that uses complaint slowness about unresponsive report, dashboard taking a long time to load, list view taking forever display, slowness on visualforce pages and you frequently getting Apex/ SOQL errors & timeouts. If you have seen these in your org, then you may have SOQL problem, but don’t be afraid–we will help to optimize the query by using lot of tips and tricks.
The Force.com query optimizer tool optimizes the SOQL used to fetch data by inspecting SOQL, Reports, and List Views.
The Force.com query optimizer is an engine that sits between your SOQL, reports, and list views and the database itself. Because of salesforce.com’s multitenancy, the optimizer gathers its own statistics instead of relying on the underlying database statistics.
Pre-computed Statistics contains the following information’s,
- Row Count
- User Visibility
- Custom index
- Owner row count
First, Force.com executes “pre-queries” that consider the multitenant-aware statistics. Then, using the results returned by the pre-queries, the service builds an optimal underlying database query for execution in the specific setting. Using both these statistics and pre-queries, the optimizer generates the most optimized SQL to fetch your data. It looks at each filter in your WHERE clause to determine which index, if any, should drive your query.
To determine if an index should be used to drive a query, the Force.com query optimizer checks the number of records targeted by the filter against selectivity thresholds.
For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million. In addition, the selectivity threshold for a standard index maxes out at 1 million total targeted records, which you could reach only if you had more than 5.6 million total records.
So, if you had 2.5 million accounts, and your SOQL contained a filter on a standard index, that index would drive your query if the filter targeted fewer than 525,000 accounts.
|1||SELECT id FROM Account WHERE CreatedDate > 2013-01-01T00:00:00Z|
(30% of 1 to 1 million targeted records) + (15% of 1 million to 2.5 million targeted records) = 300,000 + 225,000 = 525,000
For a custom index, the selectivity threshold is 10 percent of the first million targeted records and 5 percent all records after that first million. In addition, the selectivity threshold for a custom index maxes out at 333,333 targeted records, which you could reach only if you had more than 5.6 million records.
If the previous query were changed so that it used a filter on a field with a custom index, the threshold for 2.5 million accounts would be 175,000.
|1||SELECT id FROM Account WHERE CustomIndexedDate__c > 2013-01-01T00:00:00Z|
(10% of 1 to 1 million targeted records) + (5% of 1 million to 2.5 million targeted records) = 100,000 + 75,000 = 175,000
In these standard index and custom index examples, the Force.com query optimizer does use the standard and custom indexes, as each number of targeted records falls below the appropriate selectivity threshold. If, on the other hand, the number of targeted records exceeds an index’s selectivity threshold, the Force.com query optimizer does not use that index to drive the query.
Common causes of Non-Selective SOQL Queries
There are several factors that can prevent your SOQL queries from being selective.
- Having Too Much Data
Whether you’re displaying a list of records through a Visualforce page or through a list view, it’s important to consider the user experience. Pagination can help, but will your users really go through a list with thousands of records? You might not have this much data in your current implementation, but if you don’t have enough selective filters, these long lists can easily become an issue as your data grows. Design your SOQL, reports, and list views with large data volumes in mind.
- Using NOT and !=
When your filter uses != or NOT—which includes using NOT EQUALS/CONTAINS for reports, even if the field is indexed—the Force.com query optimizer can’t use the index to drive the query. For better performance filter using = or IN, and the reciprocal values.
- Using Leading % Wildcards
A LIKE condition with a leading % wildcard does not use an index.
This is the cases SOSL work better than SOQL. However, within a report/list view, the CONTAINS clause translates into ‘%string%’.
- Avoiding nulls
Many of you seen below code numerous time and what happens if an opportunity doesn’t have a value for custom LookupField. In that case, Force.com query optimizer scans the entire table and return results. This is a big problem when you have large number data in the object.
The Force.com query optimizer takes your SQL, reports, and list views and generates optimized queries. It chooses which index, if any, should drive your query, and it uses its selectivity thresholds to make that decision. If the number of records returned for an index is larger than a threshold, query performance wouldn’t improve using that index.
To ensure that your queries are selective, avoid some common pitfalls.
- Understand your schema and have proper indexes created.
- Apply as many filters as possible to reduce the result set.
- Minimize the amount of records in the Recycle Bin.
- Remember that NOT operations and LIKE conditions with a leading % wildcard do not use indexes, and complex joins might perform better as separate queries.