SOQL Query Plan & SOQL Tips

May 21, 2016

With developing on the Salesforce platform since 2011, I thought I had seen all the Salesforce tools one would need. Wrong!

The other day I came across the Query Plan tool in the Developer Console. The Query Plan tool shows the cost of Salesforce executing a given SOQL query given the database statistics known at that time.

Enabling Query Plan

Salesforce doesn’t have the Query Plan feature enabled by default. To enable it, open the Developer Console –> Help –> Preferences and then check “Enable Query Plan”.

Using Query Plan

After the Query Plan is enabled, open the Query Editor tab at the bottom, enter a SOQL query, and click the Query Plan button. This will open a modal showing the cost of the SOQL query along with helpful tips.

QueryPlan

  • Cardinality – The estimated number of records that will be returned by the query.
  • Fields – The indexed field(s) used by the Query Optimizer. If the leading operation type is Index, the fields value is Index. Otherwise, the fields value is null.
  • Leading Operation Type – The primary operation type that Salesforce will use to optimize the query.
    • Index – The query will use an index on the queried object.
    • Sharing – The query will use an index based on the sharing rules associated with the user who is executing the query. If there are sharing rules that limit which records that user can access, Salesforce can use those rules to optimize the query.
    • TableScan – The query will scan all records for the queried object.
    • Other – The query will use optimizations internal to Salesforce.
  • Cost – The cost of the query compared to the Force.com Query Optimizer’s selectivity threshold. Values above 1 mean that the query won’t be selective.
  • SObject Cardinality – The approximate record count for the queried object.

Saelsforce Query Plan How To

Indexes

Salesforce leverages indexes heavily to optimize query performance whenever possible. An index is a separate data structure that stores field values in such a way that it’s fast to search on them to identify the rows we’d like returned. The general concept is the same as a book’s index. If you want to know more about “X”, see pages 10, 50, 60, etc except in this case, it’s records instead of page numbers.

What fields are indexed?

  • Object Ids
  • Object Name – Auto Number or Text Field.
  • Custom Relationsips – Master Details and Lookups.
  • External Ids
  • Unique Fields
  • Owner Field

Not sure if a field is indexed or not? Open an object’s definition page and notice that there’s an “Indexed” column. If it’s checked, it’s indexed.

AccountIndexFields

Can non-indexed fields become indexed?

Yes but it requires submitting a Salesforce case and working with Salesforce support to add the index(es) or “Skinny Tables”.

SOQL Performance Tips

When it comes to performance, one can optimize it, in general, by lessening the amount of work that the machine has to do. Here are a few ways for SOQL. Many of these are common sense, but I’m still amazed how prevalent these are violated.

  • Select only the fields needed. Select * is generally bad because more fields need to be queried, analyzed for security permissions and visibilitiy, and brought over the network to name a few.
  • Use the limit operator to reduce how many records are returned. Showing thousands of records on a page is generally a bad practice and bad user experience. Using limit with the Offset keyword can be used to implement “paging”.
  • Ensure that a SOQL query only runs once during a given execution context. In a complex application, the same SOQL query can run multiple times in a given execution context because various parts of the application depend on the same data set. One solution to prevent this is to cache the data after it’s queried and return the result set for subequent requests.
  • Use an index in the where clause whenever possible. However, this doesn’t guarantee that it will be used. Salesforce has various rules around when an index will be used or not based on its selectivity.
    • An indexed field will not be used under the following conditions:
      • Not In (….)
      • Like with wildcards “%”
      • !=
      • Excludes for picklist values
      • Not Like
      • Comparison Operators Paired With Text Fields. Text_Field <, >, <=, >=

For a deep-dive into SOQL performance considerations, take a look at the Force.com Query Optimizer FAQ and the Query & Search Optimization Cheat Sheet.

What Query Plan information do you have to share? What SOQL Performance Tips & Resources do you have?

Happy Coding,

Luke