Salesforce Object Query Language (SOQL) is a powerful tool for retrieving data from Salesforce's database. As a developer or administrator, following best practices when writing SOQL queries is essential for ensuring your queries are efficient, scalable, and maintainable. Below are some SOQL best practices that can help you get the most out of your queries:
Use LIMIT to control the number of records returned: Always specify a limit to avoid overloading your system with too many records, especially when working with large datasets. For example, if you only need the first 100 accounts, use LIMIT 100 to restrict the query to those records.
sql
SELECT Name FROM Account LIMIT 100
Avoid returning all records in production: In a production environment, it’s best to avoid running queries that return millions of records unless necessary. Always test your queries in a controlled environment first.
Leverage Indexed Fields: Salesforce indexes certain fields to speed up queries, such as RecordTypeId, OwnerId, CreatedDate, and other standard fields like Id. Always try to filter or sort based on indexed fields if possible.
Custom Indexed Fields: For custom fields, you can request Salesforce to create a custom index if the query performance becomes an issue.
Avoid Using LIKE with Wildcards at the Start: Using LIKE with a leading wildcard (e.g., LIKE '%abc') can slow down your queries because it doesn't leverage indexing efficiently. Try to avoid it or use LIKE with a trailing wildcard only.
sql
SELECT Name FROM Account WHERE Name LIKE 'Acme%'
Instead of:
SELECT Name FROM Account WHERE Name LIKE '%Acme%'
Select only the fields you need: Avoid SELECT * or querying more fields than necessary. Always query only the fields that are actually required for the task at hand to reduce query execution time.
SELECT Id, Name FROM Account WHERE Industry = 'Technology'
Avoid:
SELECT Id, Name, Phone, Website, BillingAddress FROM Account WHERE Industry = 'Technology'
Use INNER JOIN via Relationship Queries: In Salesforce, relationship queries allow you to retrieve related records (parent-to-child or child-to-parent). Use them carefully to minimize the number of queries executed.
Parent-to-Child (Subquery): When querying related child records (e.g., related Contacts to an Account), use subqueries.
SELECT Name, (SELECT LastName FROM Contacts) FROM Account WHERE Industry = 'Technology'
Child-to-Parent Relationship: For child-to-parent queries, use the relationship name to query parent fields.
SELECT Name, Account.Name FROM Contact WHERE Account.Industry = 'Technology'
Avoid Deep Nested Queries: Avoid queries with deep subqueries as they can lead to performance issues. Limit the depth of your relationships to a manageable level.
Filter Data Early: Use WHERE clauses to limit the scope of your query, reducing the volume of data returned. This is especially important in large datasets.
SELECT Name FROM Account WHERE CreatedDate > 2023-01-01
Be mindful of date filters: Always use date filters efficiently when possible, as Salesforce can leverage indexes on CreatedDate, LastModifiedDate, and other date fields.
Avoid SELECT in Loops: One of the most important best practices in Apex is to avoid placing SOQL queries inside loops. Running a query inside a loop leads to "too many SOQL queries" limits being exceeded.
// Bad Example:
for (Account acct : accounts) {
List
contact
contacts = [SELECT Id FROM Contact WHERE AccountId = :acct.Id];
}
Better Approach:
Perform the SOQL query before the loop and store results in a map or list.
Use a Map to link related records efficiently.