Inside the Force.com Query Optimizer

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?

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.

query optimization

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

Query Execution 

query optimization

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.

query optimization

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.

1SELECT 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.

1SELECT 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.

  1. 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.

  1. 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.

  1. Using Leading % Wildcards 

A LIKE condition with a leading % wildcard does not use an index.

query optimization

This is the cases SOSL work better than SOQL. However, within a report/list view, the CONTAINS clause translates into ‘%string%’.

  1. 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.

query optimization

Summary 

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.

  1. Understand your schema and have proper indexes created.
  2. Apply as many filters as possible to reduce the result set.
  3. Minimize the amount of records in the Recycle Bin.
  4. Remember that NOT operations and LIKE conditions with a leading % wildcard do not use indexes, and complex joins might perform better as separate queries.

About MST

At MST Solutions our cornerstone is to adapt, engage and create solutions which guarantee the success of our clients. The talent of our team and experiences in varied business verticals gives us an advantage over other competitors.

Recent Articles

Work with us.

Our people aren’t just employees, they are key to the success of our business. We recognize the strengths of each individual and allow them time and resources to further develop those skills, crafting a culture of leaders who are passionate about where they are going within our organization.