Introduction
In Salesforce “Too many SOQL queries: 101” exception is one of the most common runtime exceptions encountered by Salesforce developers.
This error occurs when your Apex code exceeds the governor limit of 100 SOQL queries in a single transaction.
In this blog, we will explore:
- What causes the “Too many SOQL queries” error.
- Salesforce’s governor limits.
- Examples of problematic code.
- Strategies to avoid exceeding SOQL limits.
- Best practices for writing efficient Apex code.
1. What Causes the “Too Many SOQL Queries” Error?
Salesforce imposes strict governor limits to ensure optimal performance and equitable resource allocation in its multi-tenant environment. The “Too many SOQL queries: 101” error occurs when:
- Your code runs more than 100 SOQL queries in a single transaction (for synchronous Apex).
- A loop or recursive trigger performs a SOQL query for each record, leading to excessive queries.
2. Salesforce SOQL Governor Limits
What Are SOQL Limits?
- Synchronous Transactions: A single transaction can execute a maximum of 100 SOQL queries.
- Asynchronous Transactions (e.g., Batch Apex, Future methods): A single transaction can execute up to 200 SOQL queries.
Why These Limits Exist?
Governor limits prevent poorly written code from monopolizing resources, ensuring fair usage across all Salesforce tenants.
3. Examples of Problematic Code
Scenario 1: SOQL Inside a Loop
This is the most common reason for hitting the SOQL limit. For example:
public void updateContacts(List<Account> accounts) {
for (Account acc : accounts) {
// Query for each account in the loop
List<Contact> contacts = [SELECT Id FROM Contact WHERE AccountId = :acc.Id];
for (Contact con : contacts) {
con.Description = 'Updated via SOQL in loop';
}
update contacts;
}
}
- Problem: If the accounts list contains 150 records, this code will execute 150 SOQL queries (one per account), exceeding the limit of 100.
- Outcome: Throws “Too many SOQL queries: 101”.
Scenario 2: Recursive Triggers
A trigger that performs SOQL queries and updates related records can lead to recursion if another update triggers the same logic.
trigger AccountTrigger on Account (after update) {
for (Account acc : Trigger.new) {
List<Contact> contacts = [SELECT Id FROM Contact WHERE AccountId = :acc.Id];
for (Contact con : contacts) {
con.Description = 'Updated by trigger';
}
update contacts; // This can re-trigger the same trigger
}
}
- Problem: Recursive updates re-execute the trigger, compounding the SOQL queries.
- Outcome: Quickly exceeds the SOQL limit and throws the exception.
4. Strategies to Avoid the SOQL Limit
To avoid this exception, there are some strategies that Salesforce developers needs to implement while writing the Apex class.
Strategy 1: Bulkify Your Code
- Always write Apex code to handle multiple records in a single transaction.
- Avoid SOQL queries inside loops.
Example of Bulkified Code:
public void updateContacts(List<Account> accounts) {
// Collect all Account IDs
Set<Id> accountIds = new Set<Id>();
for (Account acc : accounts) {
accountIds.add(acc.Id);
}
// Query contacts for all accounts at once
List<Contact> contacts = [SELECT Id, AccountId FROM Contact WHERE AccountId IN :accountIds];
for (Contact con : contacts) {
con.Description = 'Updated via bulkified SOQL';
}
// Perform a single update
update contacts;
}
- Improvement: One SOQL query is executed regardless of the number of accounts.
Strategy 2: Use Collections Efficiently
- Use Sets and Maps to store and process data for efficient querying and updates.
Example Using Maps:
public void updateContacts(List<Account> accounts) {
Map<Id, Account> accountMap = new Map<Id, Account>(accounts);
// Query contacts for all accounts
List<Contact> contacts = [SELECT Id, AccountId FROM Contact WHERE AccountId IN :accountMap.keySet()];
for (Contact con : contacts) {
con.Description = accountMap.get(con.AccountId).Name + ' related update';
}
update contacts;
}
- Why It’s Better: Reduces repeated logic and ensures efficient access to data using Maps.
Strategy 3: Optimize Triggers with Frameworks
- Use a trigger framework to control execution flow and avoid recursion.
- Use a static variable to prevent re-entry during recursive updates.
public class AccountTriggerHandler {
private static Boolean isFirstRun = true;
public static void handleAfterUpdate(List<Account> accounts) {
if (!isFirstRun) return;
isFirstRun = false;
// Query and update logic here
}
}
Trigger Example:
trigger AccountTrigger on Account (after update) {
AccountTriggerHandler.handleAfterUpdate(Trigger.new);
}
Strategy 4: Leverage Batch Apex for Large Data Sets
For operations on large volumes of data, use Batch Apex to break transactions into manageable chunks.
Batch Apex Example:
global class UpdateContactsBatch implements Database.Batchable<sObject> {
global Database.QueryLocator start(Database.BatchableContext context) {
return Database.getQueryLocator('SELECT Id, AccountId FROM Contact');
}
global void execute(Database.BatchableContext context, List<Contact> scope) {
for (Contact con : scope) {
con.Description = 'Updated via batch';
}
update scope;
}
global void finish(Database.BatchableContext context) {}
}
Benefit:
Batch Apex allows up to 200 SOQL queries per execution, avoiding the synchronous limit of 100.
Strategy 5: Avoid Redundant Queries
Cache query results whenever possible to avoid repeating the same SOQL query.
public void processAccounts(List<Account> accounts) {
List<Account> cachedAccounts = [SELECT Id, Name FROM Account WHERE Id IN :accounts];
// Use cachedAccounts for further logic
}
5. Best Practices to Avoid SOQL Exceptions
- Bulkify All Code: Always design triggers, controllers, and batch jobs with bulk data handling in mind.
- Use Relationships in Queries: Leverage parent-child relationships in SOQL to reduce queries.
List<Account> accounts = [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account];
- Static Variables in Triggers: Use static variables to control recursion.
- Limit Query Fields: Fetch only the fields you need to minimize resource usage.
- Asynchronous Processing: Offload long-running processes to Batch Apex or Queueable Apex.
To learn more about SOQL best practices and how to optimize your queries, be sure to check out the post linked below.
Steps to optimize SOQL Query in Salesforce
Apart from Salesforce 101 Exception – Too Many SOQL, Salesforce developers may encounter various other exceptions while designing and implementing various Salesforce business requirements.
Explore the post below to learn about these different types of exceptions, complete with examples.
Explore different exceptions in Salesforce with Examples
6. Conclusion
The “Too many SOQL queries: 101” exception is a safeguard in Salesforce’s multi-tenant architecture to ensure fair resource usage. While it can be frustrating, following best practices like bulkification, efficient use of collections, and avoiding redundant queries can eliminate this error.
With proper design and adherence to Salesforce guidelines, you can create scalable, efficient, and reliable Apex solutions.
