Have you worked on Large data volume systems, which have millions of records in an object? If yes, then you would have definitely faced challenges when doing SOQL on those objects.

The Salesforce platform is quite flexible, where applications can scale to large volumes of data very quickly. Large data volume systems always require proper design and a few considerations to work in multi-tenant environments. Salesforce’s multi-tenant architecture uses the database to store metadata, which is different than the conventional database. That’s why traditional optimizer cannot effectively optimize Salesforce queries.

SOQL query is designed to increase or decrease the operation times. The Force.com query optimizer helps the database system’s optimizer produce effective execution plans for Salesforce queries, and it is a major factor in providing efficient data access in Salesforce.

How to Enable Query Plan Optimizer

1. Click on Setup and then Developer Console

2. Click Help from the top and select Preferences

3. Check Enable Query Plan checkbox

Why use Query Plan Optimizer

It can be used on any SOQL Query which is running slowly on Salesforce Platform or giving a time out error. It will provide the cost of using the index compared to a full table scan. If the cost of SOQL is more than 1, then it’s more likely to provide a slow result.

How to know if filter is index

Primary key present on each object (Id, Name, OwnerId), a foreign key (CreatedById, LastModifiedById, lookup, master-detail relationship), and an audit field (CreatedDate, SystemModstamp) are index fields. If parameter value of index field is null in SOQL, then it won’t be considered as an index field. Custom fields will have an index if they have been marked as Unique or External Id. Custom index can be enabled by contacting Salesforce.com on non-index fields.

If the filter has an index, determine how many records it would return:
• 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.
• 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.
• If the filter exceeds the threshold, it won’t be considered for optimization.
• If the filter doesn’t exceed the threshold, this filter is selective, and the query optimizer will consider it for optimization.

How Query plan calculates cost

Each plan has its own cost value. The cost value is derived from the latest gathered database (DB) statistics on the table and values. The plan with the lowest cost will be the plan used. If the Cost is above 1, it means that the query won’t be selective.

Reasons for not showing index fields in Query plan

Here is a list of unsupported operations:

• index will never be used when comparisons are being done with an operator like “NOT EQUAL TO”
• index will never be used when comparisons are being done with a null value like “Name = ””
• Leading ‘%’ wildcards are inefficient operators that also make filter conditions non-selective

When using an OR comparison, all filters must be indexed for optimized results.

What information can be collected from Query plan

Cardinality: The estimated number of records returned by SOQL.

Fields: The name of indexed field used by the query.

Leading Operation Type: The primary operation type that Salesforce will use to optimize the query.

• Index – The query will use an index on the query object.
• Table Scan – The query will scan all records for the query object.

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: Total number for records in the query object.

Sobject Type: The name of the query object.

Examples

• Example 1: When Index field is used in SOQL properly

SOQL: Select id, name from Account where Account_Code__c =’ProQuest’

Analysis

You can observe the cost of SOQL. It is 0.0000970 which is very low and will be best to use.

• Example 2: When non Indexed field is used in SOQL

SOQL: Select id, name from Account where Mobile_Phone__c = ‘00000’

Analysis

You can observe the cost of SOQL. It is 0.84 which is much higher. Although there are only 50 records found which have this phone no. It is not ideal to use this SOQL.

• Example 3:  Passing null value in parameters

SOQL: select id from Opportunity where Booking_External_Id__c IN (‘B10098’,null)

Analysis

This is the common problem what developers do. Although index field is used but it can’t optimize SOQL because null is present in a parameter. Query optimizer will do Table scan instead of applying index

References

1. https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_case_studies.htm
2. https://help.salesforce.com/articleView?id=000199003&type=1