Cost of your SOQL query in salesforce

Salesforce is a platform which comes with various governor limits and one of them is an inefficient SOQL query.

Query Plan is a way to know how much resource power salesforce needs to execute your query. The lower the score the faster your query.

you can check the cost of the query in salesforce developer console.

Help -> Preferences -> Enable Query Plan

Lets take an example to understand this.

I have written an basic Query get all account and checked its cost in query plan.

The leading operation type is table scan and cost is 3.81..

If queries are written this way, it might end up fetching more than 50k records and operations will fail with error System.LimitException: Too many query rows: 50001.

The 1.0 Rule

If your query score is above 1.0. salesforce is likely to do a full table scan.One way to optimize the query is by adding where clause on fields which are indexed.

I have added id field in where clause which is a standard field indexed by slesforce by default.

Now the leading operation is Index and the cost has comedown to 0.33.

This is a good way to know if the leading operation is full table scan to index scan and optimize it for performance.

Salesforce provided few standards fields indexed.

The platform maintains indexes on the following fields for most objects.

• RecordTypeId
• Division
• CreatedDate
• Systemmodstamp (LastModifiedDate)
• Name
• Email (for contacts and leads)
• Foreign key relationships (lookups and master-detail)
• The unique Salesforce record ID, which is the primary key for each object.

Avoid the Index Killers

if we use operator “like %keyword%” , “!=” ,”NOT IN”in where clause indexing will be ignored and a full table scan will be performed.Its important that queries are optimized for index scans.

In case you have lot of custom fields, you can ask salesforce support to index custom field.

References : https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm