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.
1. Click on Setup and then Developer Console
2. Click Help from the top and select Preferences
3. Check Enable Query Plan checkbox
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.
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.
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.
Here is a list of unsupported operations:
When using an OR comparison, all filters must be indexed for optimized results.
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.
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.
SOQL: Select id, name from Account where Account_Code__c =’ProQuest’
You can observe the cost of SOQL. It is 0.0000970 which is very low and will be best to use.
SOQL: Select id, name from Account where Mobile_Phone__c = ‘00000’
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.
SOQL: select id from Opportunity where Booking_External_Id__c IN (‘B10098’,null)
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