If you want your Salesforce apps to run fast and scale effortlessly, mastering SOQL (Salesforce Object Query Language) optimization is non-negotiable. Even experienced developers can fall into traps that slow down their org or hit governor limits. In this post, let’s dive deep into advanced SOQL optimization techniques—backed by real-world examples and expert insights—to help you write efficient, scalable, and future-proof queries.
SOQL queries that filter on indexed fields (like Id, Name, CreatedDate, or custom fields marked "External ID" or "Unique") are highly selective and run blazing fast. Avoid filtering on non-indexed fields for large data volumes.
Pro Tip: Use the
Query Plantool in Developer Console to check selectivity and see if your query is using an index.
SOQL supports parent-to-child and child-to-parent queries. Keep your relationship depth to a minimum—going more than one level deep can dramatically increase resource usage and response times.
SELECT Name, (SELECT LastName FROM Contacts) FROM Account
Keep it to 1:1 or 1:many, avoid chaining three or more levels.
Always filter your data using WHERE clauses. Never use SELECT * FROM Object (i.e., without a filter) unless absolutely necessary. This prevents unnecessary data retrieval and governor limit issues.
Specify only the fields you need in your SELECT statement. This reduces heap size and improves performance, especially when objects have many fields.
When querying for multiple records, use the IN clause—just be mindful of the 100,000-character limit for SOQL queries.
SELECT Id, Name FROM Account WHERE Id IN :accountIds
Aggregate functions (COUNT(), SUM(), AVG(), etc.) are powerful, but they can be resource-intensive. Use them only when necessary and always filter the data set.
For objects with millions of records:
- Filter on indexed fields.
- Use skinny tables if available.
- Batch your queries and process in chunks.
The Salesforce Query Plan Tool helps you inspect how queries are executed. Always check the "Cost" and "Cardinality" to ensure your queries are efficient.
Query related data in one go, instead of inside loops. Use maps to relate data after querying.
// Bad: Query inside loop
for (Account acc : accList) {
Contact[] contacts = [SELECT Id FROM Contact WHERE AccountId = :acc.Id];
}
// Good: Query once, then map
Map<Id, List<Contact>> accountContacts = new Map<Id, List<Contact>>();
for (Contact c : [SELECT Id, AccountId FROM Contact WHERE AccountId IN :accIds]) {
// Build your map here
}
Always keep governor limits in mind:
- 50,000 records per query
- 100 SOQL queries per transaction
Design your logic to stay well below these thresholds.
Optimizing SOQL isn’t just about speed—it’s about scalability, reliability, and building world-class Salesforce apps. Regularly review your queries, use the tools Salesforce provides, and never stop learning.
What’s your best SOQL optimization tip? Drop it in the comments below!
Subscribe for more Salesforce developer tips, code samples, and deep dives!
Post a Comment