Monitoring Soql Query Performance: Strategies For Optimization

SOQL, or Salesforce Object Query Language, is a powerful query language used to extract insights from Salesforce databases. As a business's Org grows, the queries initially designed may suffer degradation, and this can go unnoticed until it is too late. To avoid this, Salesforce developers need to monitor SOQL query performance to ensure their applications run smoothly and efficiently. This can be done by using a tool such as Query Performance Detector, which allows users to check how Salesforce Optimizer is evaluating ListViews, Reports and Dashboards. There are also several tips for writing efficient SOQL queries, such as limiting the number of fields and records returned, using filters and indexes, and optimizing query order.

Use filters and indexes

When working with large data sets, using filters and indexes can significantly improve SOQL query performance. This involves using WHERE clauses to narrow down the data you're querying and leveraging indexed fields to optimise search performance.

Salesforce automatically indexes standard fields like Id, Name, and CreatedDate, but you can also create custom indexes for specific use cases. For example, if you have a query with a WHERE clause that includes an AND condition, you can use a composite filter to improve performance.

To create a selective SOQL query, ensure that at least one of the query filters is on an indexed field, and that the 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 these conditions.

Additionally, you can use the LIMIT keyword to restrict the number of rows returned by your query, preventing large result sets from consuming excessive resources. This is especially useful when you only need a sample of the data or when working with paginated results.

Limit fields and records returned

When writing SOQL queries, it is important to limit the number of fields and records returned to only what is necessary. This helps to optimise query performance by reducing the amount of processing and memory required. Here are some ways to achieve this:

Select Only the Necessary Fields

Avoid using "SELECT *" or retrieving all fields when you only need a subset of them. By limiting the number of fields returned, you can reduce the query's processing time and memory consumption. This is because retrieving a large number of fields can slow down the query, especially if those fields are not indexed. It is more efficient to explicitly list the fields you require in your query.

Limit the Number of Records

To prevent large result sets from consuming excessive resources, use the "LIMIT" keyword to restrict the number of rows returned by your query. This is particularly useful when you only need a sample of data or when working with paginated results. By limiting the number of records returned, you reduce the amount of data that needs to be processed and transferred, improving the overall performance of your query.

Use Filters

When querying large datasets, using filters can significantly improve performance. The "WHERE" clause allows you to narrow down the data you're querying by specifying certain conditions. Additionally, leveraging indexed fields in your filters can further optimise search performance. Salesforce automatically indexes standard fields like "Id", "Name", and "CreatedDate", but you can also create custom indexes for your specific requirements.

Optimise Query Order

The order in which you structure your query can also impact its performance. It is generally recommended to filter your data first using the "WHERE" clause before performing operations like sorting or aggregating. This ensures that these operations are only carried out on the data you need, reducing unnecessary processing time.

By following these guidelines and continually monitoring and refining your queries, you can ensure optimal performance as your Salesforce organisation evolves.

Use relationship queries

When dealing with SOQL, relationship queries are an important tool to have in your arsenal. They allow you to query related objects, such as parent-child relationships, in a single query, instead of writing separate queries for each object. This not only simplifies the query-building process but also improves performance by reducing the overall number of queries executed.

There are two primary types of relationship queries: child-to-parent and parent-to-child. In a child-to-parent relationship query, the relationship name will be the parent object, which acts as a foreign key. For example, consider the relationship between Contact (child) and Account (parent). To select the first name from Contact and the name from Account, the query would look like this:

> SELECT Contact.FirstName, Contact.Account.Name FROM Contact

When writing a child-to-parent relationship query, it's important to note that the relationship name for custom objects in the SOQL query should have ObjectName__r appended to it. Additionally, when querying from a child to a parent, the relationship will be a master-detail field name or a lookup field name.

On the other hand, when writing a query to refer to child object records from a parent object, the child object name will be the relationship name. For example, if you want to select the account name and the first and last names of the associated contacts, the query would be structured as follows:

> SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.contacts) FROM Account

It's worth noting that when dealing with standard objects, the child object name should be referred to by its plural name.

Relationship queries can also be used with custom objects. When writing a query to refer to custom child object fields from a parent-to-child relationship, you need to append the child object plural name__r. For instance, if you want to select the parent name and age from the parent object and the child name and date of birth from the child object, the query would resemble the following:

> [SELECT ParentName__c, Age__c, (SELECT Child_Name__c, Dob__c FROM Child__r) FROM Parent__c]

By utilising relationship queries, you can streamline your SOQL queries and improve overall performance by reducing the number of individual queries needed to retrieve data from related objects.

Optimise query order

The order in which you structure your query can impact its performance. Here are some tips to optimise the order of your SOQL queries:

Use Filters and Indexes

When dealing with large data sets, using filters can significantly improve performance. Make use of WHERE clauses to narrow down the data you’re querying, and leverage indexed fields to optimise search performance. By default, Salesforce automatically indexes standard fields like Id, Name, and CreatedDate. However, you can also create custom indexes for your specific use cases.

Avoid Non-Selective SOQL Queries

Non-selective queries can lead to errors, especially when executed against objects with more than 100,000 records. To avoid this, ensure that your query is selective by including a query filter on an indexed field that reduces the resulting number of rows below a system-defined threshold.

Avoid Querying for Null Rows

Queries that search for records with empty or null fields can be non-selective. For example, a query like "SELECT Id, Name FROM Account WHERE Custom_Field__c = null" is not optimal.

Avoid Negative Filter Operators

Negative filter operators such as !=, NOT LIKE, or EXCLUDES can hinder query performance. Instead of using "SELECT CaseNumber FROM Case WHERE Status != 'Closed'", it is better to use "SELECT CaseNumber FROM Case WHERE Status IN ('Open', 'In Progress')".

Avoid Using Text Fields with Comparison Operators

Using comparison operators such as >, <, >=, or <= with text-based fields can impact query performance. For example, a query like "SELECT AccountId, Amount FROM Opportunity WHERE Order_Number__c > 10" is not optimal.

Avoid Querying on Formula Fields

By default, formula fields do not have underlying indexes, requiring full scans to find target records. While you can request custom indexes on formula fields, it is generally better to avoid filtering with formula fields that contain dynamic, non-deterministic references.

Use Relationship Queries

SOQL allows you to query related objects using relationship queries. Instead of writing separate queries for parent and child objects, you can retrieve data from both in a single query, reducing the number of queries executed and improving overall performance.

Avoid real-time queries

When dealing with large data sets, it is advisable to avoid real-time queries. Instead, consider using asynchronous processing or batch Apex to manage data processing in the background. This ensures that your application remains responsive to user interactions.

Real-time queries can cause SOQL query timeouts, which occur when a query takes too long to execute, and Salesforce is forced to terminate it. This can result in incomplete or inaccurate data retrieval and affect your application's performance, reliability, and user experience.

To prevent timeouts and improve performance, it is crucial to write efficient and scalable SOQL queries. Utilize filters and conditions to limit the number of records returned. For example, use the WHERE clause to specify criteria, the LIMIT clause to set the maximum number of records, or the OFFSET clause to skip records.

Additionally, leveraging indexes is essential. Indexes are data structures that help Salesforce locate records faster. While some fields, such as Id, Name, and OwnerId, are automatically indexed, you can also create custom indexes for specific use cases using the External ID or Unique attributes.

By following these optimization techniques and avoiding real-time queries for large data sets, you can enhance the performance and responsiveness of your Salesforce applications.

Frequently asked questions