SOQL & SOSL Performance Tuning: Writing Efficient Queries in Salesforce

In Salesforce, efficient data retrieval is critical for maintaining application performance, scalability, and user satisfaction. Poorly optimized queries can lead to hitting Salesforce governor limits, slower page loads, and even transaction failures. Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL) are powerful tools, but their misuse can degrade performance. This blog explores best practices for tuning SOQL and SOSL queries, ensuring they run efficiently while adhering to Salesforce’s constraints. Join us to learn about SOQL & SOSL Performance Tuning: Writing Efficient Queries in Salesforce.

Understanding Governor Limits and Performance Impact

Governor limits in Salesforce are platform-enforced restrictions to ensure fair resource usage across all users in its multi-tenant environment. Exceeding these limits can lead to runtime errors, slower performance, and transaction failures. Efficient query design and resource management are crucial to avoid hitting these constraints. By adhering to best practices, developers can optimize performance and maintain application scalability. Learn about SOQL best practices.

Key limits include:

• SOQL Queries : 100 synchronous and 200 asynchronous per transaction.
• SOSL Queries : 20 per transaction.
• Query Timeouts : Queries exceeding 120 seconds are terminated.
• Record Limits : 50,000 rows returned per query.

Exceeding these limits causes runtime errors. Efficient queries minimize resource consumption, reduce latency, and prevent bottlenecks.

SOQL Performance Tuning

1. Use Selective Queries with Indexed Fields

Using selective queries with indexed fields is critical for optimizing query performance in Salesforce. A query is considered selective when it filters on indexed fields, which allows Salesforce to quickly locate records without scanning the entire database. Indexed fields include primary keys like Id and Name (on certain objects), foreign keys in lookup relationships, audit fields such as CreatedDate and SystemModstamp, and custom fields marked as Unique or External ID. By leveraging these indexed fields in your WHERE clauses, you can significantly reduce query execution time and avoid performance bottlenecks. Always aim to design queries that utilize indexed fields to ensure efficient data retrieval.

Example:  

Non-selective Query (avoids index):

SELECT Id FROM Account WHERE Industry = 'Tech' AND AnnualRevenue > 1000000

Optimized Query (uses CreatedDate index):  

SELECT Id FROM Account WHERE Industry = 'Tech' AND CreatedDate = LAST_N_DAYS:30

2. Optimize Clauses (WHERE, LIMIT, ORDER BY)

Optimizing clauses like WHERE, LIMIT, and ORDER BY is essential for writing efficient queries in Salesforce. In the WHERE clause, always filter on indexed fields and avoid using functions (e.g., CALENDAR_YEAR(CreatedDate)), as they prevent Salesforce from leveraging indexes, leading to slower performance. Use LIMIT 1 when retrieving a single record to reduce unnecessary data processing and improve query speed. For ORDER BY, sort on indexed fields to avoid additional sorting overhead, ensuring faster and more efficient query execution. By fine-tuning these clauses, you can significantly enhance query performance and reduce resource consumption.

Avoid Complex Filters:  

 Expensive due to non-indexed function

SELECT Id FROM Order WHERE CALENDAR_YEAR(CreatedDate) = 2023;

3. Bulkify Queries

Bulkifying queries is a critical practice in Salesforce to ensure efficient data processing and avoid hitting governor limits. Moving queries outside loops prevents the same query from being executed repeatedly, which can quickly exhaust the allowed number of queries per transaction. Instead, gather all necessary data in a single query and store it in collections like lists or maps for easy access. This approach not only reduces the risk of exceeding limits but also improves performance by minimizing database calls. By designing your code to handle bulk operations efficiently, you ensure scalability and reliability in your Salesforce applications.

Bad Practice: 

for (Contact contact : contacts) {
    Account acc = [SELECT Name FROM Account WHERE Id = :contact.AccountId];
}

Good Practice: 

Set<Id> accountIds = new Set<Id>();
for (Contact contact : contacts) {
    accountIds.add(contact.AccountId);
}
Map<Id, Account> accounts = new Map<Id, Account>([SELECT Name FROM Account WHERE Id IN :accountIds]);

4. Relationship Queries and Joins

Using relationship queries and joins in Salesforce allows you to retrieve related data efficiently in a single query, reducing the need for multiple database calls. Parent-child subqueries, like SELECT Name, (SELECT LastName FROM Contacts) FROM Account, enable you to fetch related records (e.g., contacts for accounts) in one go, minimizing query count and improving performance. Similarly, you can directly retrieve parent object fields like SELECT Contact.Account.Name FROM Contact without running separate queries, saving resources and time. By leveraging these techniques, you streamline data retrieval, avoid hitting governor limits, and enhance the overall efficiency of your Salesforce queries.

Example:

Parent-child:

SELECT Name, (SELECT LastName FROM Contacts) FROM Account

Retrieve parent fields directly instead of separate queries: 

SELECT Contact.Account.Name FROM Contact WHERE Id = '003xx000004Tmi';

5. Handling Large Data Volumes

Handling large data volumes in Salesforce requires strategic approaches to avoid performance issues and governor limits. For datasets exceeding 50,000 rows, use date ranges or Batch Apex to process data in smaller, manageable chunks, ensuring efficient execution and scalability. Avoid using OFFSET for pagination, as it can be resource-intensive and slow for large datasets; instead, use indexed filters (e.g., WHERE CreatedDate > :lastProcessedDate) to navigate through records. These techniques help maintain query performance, prevent timeouts, and ensure your application can handle large volumes of data effectively.

SOSL Performance Tuning

1. Precise Search Terms

Using precise search terms in SOSL queries is crucial to avoid retrieving excessive or irrelevant results. Broad searches, such as searching for “apple”, can return a large number of records, increasing processing time and resource usage. Narrow down your search by incorporating logical operators like AND, OR, and NOT to refine the results. For example, FIND {apple AND (fruit OR tech)} ensures the query targets specific contexts, improving accuracy and efficiency. By focusing on precise search terms, you enhance query performance and deliver more relevant results to users.  

FIND {apple AND (fruit OR tech)} IN ALL FIELDS RETURNING Account(Name), Contact(LastName)

2. Limit Object and Field Scope

Limiting object and field scope in SOSL queries is essential for improving performance and reducing unnecessary data retrieval. Use the `IN` clause to restrict the search to specific objects, such as `IN NAME FIELDS` or `IN EMAIL FIELDS`, to narrow down the search context. Additionally, use the `RETURNING` clause to specify only the required fields and objects, avoiding the overhead of fetching irrelevant data. For example, `FIND {Acme} RETURNING Account(Name), Contact(LastName)` ensures the query retrieves only the necessary information. By narrowing the scope, you optimize query efficiency and reduce the risk of hitting governor limits.

FIND {Acme} IN NAME FIELDS RETURNING Account(Name, BillingCity), Opportunity(Amount)

3. Manage Result Limits

Managing result limits in SOSL queries is crucial to avoid exceeding Salesforce’s constraints and ensure efficient data retrieval. SOSL queries can return up to 2,000 records in total, which may be distributed across multiple objects. To control the number of records returned, use the LIMIT clause per object within the RETURNING statement. For example, FIND {Cloud} RETURNING Account(Name LIMIT 100), Contact(LastName LIMIT 50) restricts the results to 100 accounts and 50 contacts. By setting appropriate limits, you prevent unnecessary data processing, improve query performance, and stay within platform boundaries.

FIND {Cloud} RETURNING Account(Name LIMIT 100), Contact(LastName LIMIT 50)

General Best Practices

1. Cache Results

Store frequently accessed data in collections or static variables: 

private static Map<Id, Account> accountCache = new Map<Id, Account>([SELECT Id, Name FROM Account]);

2. Analyze Performance

Query Plan Tool: In Developer Console, check if queries use indexes.

Debug Logs: Monitor query execution time and row counts.

3. Use Efficient Data Structures

Convert query results into maps for O(1) access:  

Map<Id, Account> accounts = new Map<Id, Account>([SELECT Id FROM Account]);

Conclusion

Optimizing SOQL and SOSL queries is essential for building scalable Salesforce applications. By leveraging indexed fields, minimizing data retrieval, bulkifying operations, and using monitoring tools, developers can ensure efficient performance and avoid governor limits. Continuously test and refine queries to adapt to evolving data volumes and business needs.
Final Tip: Always design  queries with scalability in mind – what works for hundreds of records may fail with millions. Happy querying! 🙂