Selective SOQL Queries

First of all, I would like to mention why I am writing this blog. This topic is very less talked about topic but while working for big clients who are having large database , we encounter lot of issues while fetching data from data-base. So , this blog can help you get some insights how to deal with it.

Also , this is one of the very hot topic from where questions are asked very frequently specially for good Product Based Companies. So, If someone is preparing for Product Based Companies interview then it may help you.

What happens while working with Very Large SOQL Queries -

Salesforce Object Query Language (SOQL) queries can be very practical if written efficiently. However, some queries can have extremely long execution times, especially when run on non-indexed fields. These are called non-selective queries.

SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore.

For example, if the results are too large, this syntax causes a runtime exception:

Account[] accts = [SELECT Id FROM Account];

Instead, use a SOQL query for loop as in one of the following examples:

// Use this format if you are not executing DML statements 

// within the for loop
for (Account a : [SELECT Id, Name FROM Account 
                  WHERE Name LIKE 'Acme%']) {
    // Your code without DML statements here
}

// Use this format for efficiency if you are executing DML statements 
// within the for loop
for (List<Account> accts : [SELECT Id, Name FROM Account
                            WHERE Name LIKE 'Acme%']) {
    // Your code here
    update accts;
}

Selective SOQL Queries-

For best performance, SOQL queries must be selective, particularly for queries inside triggers. To avoid long execution times, the system can terminate nonselective SOQL queries. Developers receive an error message when a non-selective query in a trigger executes against an object that contains more than 200,000 records. To avoid this error, ensure that the query is selective.

Criteria for Selective SOQL Query

A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.

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 333,333 records. 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.

Custom Index Considerations for Selective SOQL Queries-

When a field is indexed, its values are stored in a more efficient data structure. This takes up more space but improves performance when at least two filters with indexed fields are used in a query.

The following fields are indexed by default.

• Primary keys (Id, Name, and OwnerId field
• Foreign keys (lookup or master-detail relationship fields)
• Audit dates (CreatedDate and SystemModstamp fields)
• RecordType fields (indexed for all standard objects that feature them)
• Custom fields that are marked as External ID or Unique

When the Salesforce optimizer recognizes that an index can improve performance for frequently run queries, fields that aren’t indexed by default are automatically indexed.

A custom index can't be created on these types of fields: multi-select picklists, currency fields in a multicurrency organization, long text fields, some formula fields, and binary fields (fields of type blob, file, or encrypted text.) New data types, typically complex ones, are periodically added to Salesforce, and fields of these types don’t always allow custom indexing.

Note- You can’t create custom indexes on formula fields that include invocations of the TEXT function on picklist fields.

Example of Selective Query-

To better understand whether a query on a large object is selective or not, let's analyze below query. For this query, assume that there are more than 200,000 records for the Account sObject. These records include soft-deleted records, that is, deleted records that are still in the Recycle Bin.
SELECT Id FROM Account WHERE Id IN (<list of account IDs>)

The WHERE clause is on an indexed field (Id). If SELECT COUNT() FROM Account WHERE Id IN (<list of account IDs>) returns fewer records than the selectivity threshold, the index on Id is used. This index is typically used when the list of IDs contains only a few records.

PS- 
Content Source - Developer Guide
For more detail- Please go through this link - Check more about Selective Query
Indexing in SOQL- Indexing in SOQL

Stay tuned for next Blog .

Thanks for reading and happy learning.