Introduction
SOQL (Salesforce Object Query Language) is a powerful tool for querying Salesforce data.
However, poorly written SOQL queries can impact performance, hitting governor limits or slowing down operations.
In this blog, we’ll explore best practices for optimizing SOQL queries, ensuring they are efficient and scalable.
Why Optimize SOQL Queries?
- Avoid Governor Limits: Salesforce enforces strict limits on SOQL queries (e.g., 100 SOQL queries per transaction).
- Improve Performance: Faster queries lead to better user experience and quicker data retrieval.
- Handle Large Data Volumes: Efficient queries scale better with growing data.
Best Practices to Optimize SOQL Queries
Below are some best practises which every Salesforce developer should follow while writing a SOQL query.
1. Use Selective Filters
A selective filter reduces the number of records scanned by the query.
It ensures that Salesforce uses indexed fields for faster data retrieval.
Example:
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];
Why it is better?
This is better because – Filtering the output by Industry narrows down the results.
Avoid queries without filters:
List<Account> accounts = [SELECT Id, Name FROM Account];
This retrieves all the records, which is inefficient for large datasets.
2. Query Only Required Fields
Retrieve only the fields you need instead of using all the fields present in that object.
Example:
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];
Why it’s better: Fetching fewer fields reduces data transfer and improves query performance.
❌ Avoid fetching unnecessary fields:
List<Account> accounts = [SELECT Id, Name, AnnualRevenue, Phone, BillingAddress FROM Account];
If only Name is needed, querying additional fields wastes resources.
3. Use Indexed Fields
Indexed fields improve query performance significantly. Common indexed fields include:
- Record Id (Id)
- External IDs
- Master-Detail or Lookup relationships
- Custom fields marked as “External ID” or “Unique”
✅ Example:
List<Account> accounts = [SELECT Id, Name FROM Account WHERE CreatedDate > LAST_N_DAYS:30];
❌ Avoid non-selective filters like:
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Description LIKE '%Technology%'];
4. Leverage Query Plan Tool
Use the Query Plan Tool in Developer Console to analyze your queries.
It helps identify whether your query is using indexed fields and provides optimization suggestions.
Steps:
- Open Developer Console.
- Navigate to Query Plan.
- Paste your SOQL query and analyze the output.
5. Use Relationships Wisely
Query related records efficiently using relationship queries.
✅ Example (Parent-to-Child):
List<Account> accounts = [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Industry = 'Technology'];
✅ Example (Child-to-Parent):
List<Contact> contacts = [SELECT Id, Name, Account.Name FROM Contact WHERE Account.Industry = 'Technology'];
Why it’s better: These queries reduce the need for multiple queries.
6. Use LIMIT to Control Result Size
Limit the number of records returned to only what you need.
✅ Example:
Why it’s better: This prevents fetching unnecessary records.
List<Contact> contacts = [SELECT Id, Name FROM Contact WHERE LastName = 'Smith' LIMIT 50];
7. Avoid NULL Filters
Filtering on NULL fields prevents indexes from being used, leading to full table scans.
Instead, use meaningful filters to avoid relying on NULL values.
❌ Example to avoid:
List<Account> accounts = [SELECT Id, Name FROM Account WHERE BillingCity = NULL];
8. Use Bulkified Queries
Query in bulk when processing multiple records to avoid hitting governor limits.
✅ Example:
public static List<Account> getAccountsByIds(Set<Id> accountIds) {
return [SELECT Id, Name FROM Account WHERE Id IN :accountIds];
}
Why it’s better: This processes multiple records in one query instead of running separate queries for each Id.
9. Use SOSL for Wider Searches
For scenarios requiring text searches across multiple objects, consider using SOSL instead of SOQL.
✅ Example:
List<List<SObject>> searchResults = [FIND 'Technology' IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name)];
Why it’s better: SOSL is faster for searching across multiple objects.
10. Batch Large Queries
For large datasets, use Batch Apex to process records in chunks, avoiding heap size and query limits.
✅ Example:
- Querying too many records: Use filters and limits to refine results.
- Using non-indexed fields: Always prefer indexed fields for WHERE clauses.
- Overloading queries: Avoid combining unrelated filters that slow down execution.
global class AccountBatch implements Database.Batchable<SObject> {
global Database.QueryLocator start(Database.BatchableContext context) {
return Database.getQueryLocator('SELECT Id, Name FROM Account');
}
global void execute(Database.BatchableContext context, List<SObject> scope) {
// Process records in chunks
}
global void finish(Database.BatchableContext context) {
// Final steps
}
}
Common Mistakes To Avoid:
- Querying too many records: Use filters and limits to refine results.
- Using non-indexed fields: Always prefer indexed fields for WHERE clauses.
- Overloading queries: Avoid combining unrelated filters that slow down execution.
Conclusion
Optimizing SOQL queries ensures that your Salesforce applications remain fast, scalable, and within governor limits.
By following these best practices, you can write efficient queries that handle data effectively, even as your Salesforce instance grows.
Key Takeaways:
- Use selective filters and indexed fields.
- Fetch only the necessary data.
- Test queries with the Query Plan Tool.
Start applying these tips today, and watch your queries perform like a pro! 🚀
