SOQL is a tool that lets you access records in your Salesforce database.
when you don't write good SOQL queries you're going to hit the governor limit of non selective query.
Query Error : 'System.QueryException : Non-selective query against large object type'
System.QueryException :
Non-selective query against large object type(more than 100000 rows).
Consider an indexed filter or contact salesforce.com about custom indexing.
Even if a field is indexed a filter might still not be selective when :
1.The filter value includes null(for instance binding with that list that contains null).
2.Data skew exists whereby the number of matching rows is very large
(for instance,filtering for a particular foreign key value that occurs many times ).
To avoid this error,ensure that the SOQL query is selective.
What is a selective query?
A selective query is a query that leverages indexes in filters to avoid full table scans
and to reduce the number of records in your result set below the selectivity threshold.
Selective SOQL Query Criteria :
1.A query is selective when one of the query filters is on an indexed field.
2.The query filter reduces the resulting number of rows below a system-defined threshold.
3.The performance of the SOQL query improves when two or more filters used in the
WHERE clause meet the mentioned conditions.
4. 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
5.For a custom index,the selectivity threshold is 10% of the first million records and less than 5% of the records
after the first million records,up to a maximum of 3,33,333 records.
6.In some circumstances, for example with a query filter that is an indexed standard field,
the threshold can be higher.Also, the selectivity threshold is subject to change.
7.If the filter exceeds the threshold, it won’t be considered for optimization.
8.If the filter doesn’t exceed the threshold, this filter is selective, and the query optimizer will consider it for optimization.
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.
Note :
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:
1.Not In (….)
2.Like with wildcards "%"
3.!=
4.Excludes for picklist values
5.Not Like
6.Comparison Operators Paired With Text Fields. Text_Field <, >, <=, >=
Note :
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.
ex :
1.When Index field is used in SOQL properly
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.
2.When non Indexed field is used in 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.
3.Passing null value in parameters
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
Advantage of Selective Filters :
1.Reduces the number of records in your result set.
2.Leverages indexes.
3.Avoids full table scans.
Note :
1.Selectivity thresholds determine if an index is considered.
2.Not Equals filters will not leverage indexes.
3.Be careful filtering on Null.
4.And conditions involve an INTERSECTION of indexes.
5.OR conditions involve an ADDITION of indexes.
6.ORDER BY with a LIMIT on an index can make up for non-selective filters.
Note :
1.Query Performance improves with indexes.
2.Use selective filters to reduces result set.
3.Query Optimizer chooses the best table/index to drive a query
4.Skinny Tables may help when indexing is exhausted.
Skinny Table :
1.Contact Salesforce to enable it.
2.Can contain only 100 fields.
3.Change in field type used in Skinny table would make it invalid.
4.Skinny Table is not copied to sandbox after refresh (Except Full Copy Sandbox).
5.Useful for read operations.
6.Cannot contain fields from other object/Parent.
Selective Query Definition :
Selectivity is determined by the index state of the query filter conditions
and the number of records the filter returns (Selectivity) versus the object total records.
The thresholds below show the difference between the selectivity calculation for a standard index versus a custom index.
Selectivity Thresholds :
Standard Index - 30% (first 1M) then 15%
Custom Index - 10% (first 1m) then 5%
Unary filter :
ex : SELECT Name FROM Account where IndexedField__c='ABC'
With a custom index on IndexedField__c the filter must return <10% of the total records
in the object to be considered selective - up to the first 1 million records from that point the threshold drops to 5%.
Multiple filters AND (exclusively):
ex : SELECT Name FROM Account WHERE IndexedField__c='ABC' AND SecondIndexedField__c='123'
The Query Optimiser will set the leading operation on the basis of lowest cost.
if no filters are selective a table scan is performed.
If all filters have an index then a Composite Index Join optimization can be applied.
In this case each filter must be less than 2x(two-times) the selectivity threshold.
All filters combined must be less than selectivity threshold.
If all filter fields are standard then use the standard index selectivity threshold-otherwise use custom index selectivity threshold.
Multiple filters OR ( at least one) :
ex : SELECT Name FROM Account WHERE IndexedField__c='ABC' OR SecondIndexedField__c='123'
Selective AND filter indexes cloud be set as the Leading Operation - if none exist,then a table scan occurs
unless all filters have an index then a Composite Index Union optimisation becomes possible.
In this case each filter must be less than selectivity threshold.
All filters combined must be less than selectivity threshold.
If all fields are standard then use the standard index selectivity threshold-otherwise use the custom index selectivity threshold.
Note : When using OR condition, all filters must be indexed and under the 10% threshold.
Parent Field Filter :
ex : SELECT Name FROM Contact WHERE IndexedField__c=’ABC’ and Account.IndexedField__c=’ABC’
Where parent object fields are referenced in a filter, each filter index is individually and the lowest cost option selected as the leading operation.
Note, the parent field is not indexed on the queried object, so Account.Id can incur a table scan on Opportunity
whereas AccountId may allow the standard index to become the leading operation.
Evaluate the Selectivity :
==========================
In order to evaluate the selectivity of a given query – the following 2 approaches can be applied.
1.REST API Query Resource Feedback Parameter
2.Developer Console Query Plan
Query Resource Feedback Parameter :
The Force.com REST API exposes a Query resource that accepts an explain parameter which can set with a SOQL query, List View Id or Report Id.
The results show the options considered by the Query Optimiser and the lowest cost option (leading operation) taken.
A relative cost value of less than 1 indicates a selective query, anything higher indicates non-selective
ex :
/services/data/v52.0/query/?explain=
SELECT+Name+FROM+Merchandise__c+WHERE+CreatedDate+=+TODAY+AND+Price__c+>+10.0
/services/data/v52.0/query/?explain=00Ow0000007lLgO
Query Plan in Salesforce :
The Query Plan tool shows the cost of Salesforce executing a given SOQL query given the database
statistics know at that time.
The new Query Plan tool in the Developer Console can help speed up SOQL queries done over large volumes.
Use the Query Plan tool to optimize and speed up queries done over large volumes.
The Query Plan tool will show a list of available plans that our Query Optimizer can utilize for the query provided
and will be arranged by cost ascending. Each Plan will contain information on Cardinality, Operation Type, Cost, sObject Type, and more.
Each plan has a “Leading Operation Type”, for example, Field Index or Full Table Scan.
The plan with the lowest cost is the plan that is used for driving the query execution.
The Query Plan window displays all query operations and the cost of each.
1.Cardinality – The estimated number of records that will be returned by the query.
2.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.
3.Leading Operation Type – The primary operation type that Salesforce will use to optimize the query.
a.Index – The query will use an index on the queried object.
b.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.
c.TableScan – The query will scan all records for the queried object.
d.Other – The query will use optimizations internal to Salesforce.
4.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.
5.SObject Cardinality – The approximate record count for the queried object.
6.Sobject Type - The name of the query object.
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.
There are below reasons for not showing index fields in Query plan.
1.index will never be used when comparisons are being done with an operator like “NOT EQUAL TO”
2.index will never be used when comparisons are being done with a null value like “Name = ””
3.Leading ‘%’ wildcards are inefficient operators that also make filter conditions non-selective
4.When using an OR comparison, all filters must be indexed for optimized results.
Note :
Don't forget about deleted records in your recycle bin.
Did you know that deleted records can effect your query performance.
2 ways to solve this problem :
1.Add isDeleted = false to your queries.This field is available on all standard and custom objects.
2.Empty the recycle bin.
Note :
MAX # of Master-Detail Relationship Fields, per Object (Standard or Custom) -- 2
MAX # of total Relationship Fields (Master-Detail + Lookup), per Object (Standard or Custom) -- 40
Relationship Query :
1.Child to parent
SELECT Id,Name,Broker__c,Broker__r.Id,Broker__r.Region__c FROM Property__c WHERE Status__c='Available'
2.Parent to Child
SELECT Id,Name,Region__c,(SELECT Id,Name,Status__c FROM Properties__r) FROM Broker__c WHERE Region__c='North'
Querying for Intersection :
SELECT Id,Name,Broker__r.Name,Broker__r.Email__c FROM Property__c WHERE Broker__r.Region__c='North'
Aggregate Queries :
SELECT City__c,COUNT(Id) houseCount,MAX(Price__c),AVG(Price__c) avgValue FROM Property__c GROUP BY City__c
