Salesforce Query Plan Optimiser for Large Data Volumes
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 Salesforce Object Query Language (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 optimiser cannot effectively optimize Salesforce queries.
SOQL query plan is designed to increase or decrease the operation times. The Force.com query plan optimiser helps the database system’s optimiser 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 Optimiser
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 Optimiser
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 the 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 optimiser 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 optimiser’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 that developers do. Although index field is used, it can’t optimize SOQL because null is present in a parameter. The query optimiser will do a Table scan instead of applying an index.
Need help in implementing Salesforce Query Plan Optimiser? Send us a message so we can help you navigate!
