Apex Query Optimization

Understanding SOQL Selectivity

SOQL (Salesforce Object Query Language) is a powerful tool for querying data in Salesforce. Optimizing your queries is crucial to ensure that your applications run efficiently and quickly. Selectivity is a key factor that determines whether a query can be optimized by the Salesforce query optimizer.

Selectivity is based on how many records a query returns compared to the total number of records in the object. A selective query returns a small percentage of the total records.

Using Selective Filters

To make your SOQL queries selective, use filters that significantly reduce the number of records returned. Indexes play an essential role here. Salesforce automatically indexes some fields—such as the primary key, foreign keys, audit dates, and custom fields marked as 'External ID' or 'Unique'.

Consider the following selective query example:

In the above example, the BillingState and Industry fields should be indexed to ensure selectivity. This query will only return records where both conditions are met, reducing the dataset size significantly.

Avoiding Full Table Scans

Queries that aren't selective may lead to full table scans, which can significantly impact performance. A full table scan occurs when Salesforce needs to examine every record in the table to find those that meet the query criteria. This is time-consuming and resource-intensive.

To avoid full table scans, ensure that your query filters are based on indexed fields and that the filters reduce the result set to a small percentage of the total table.

Using Query Plan Tool

Salesforce provides a Query Plan tool that helps developers understand how selective their queries are. This tool shows which parts of the query are using indexes and provides insights into whether a query will be efficient.

To use the Query Plan tool, you can leverage the Developer Console in Salesforce:

The Query Plan tool will provide a cost associated with each query plan. A lower cost indicates a more efficient query.

Reducing Data Returned

Another way to optimize queries is to limit the data returned by using the LIMIT clause. This is especially useful when you're only interested in a subset of the data.

For example:

By specifying a LIMIT, you can reduce the processing time and improve the performance of your query, ensuring that only the necessary data is retrieved.