How to optimize SOQL queries to avoid the “Too Many SOQL Queries: 101”?
Here’s a code example that demonstrates how to optimize SOQL queries to avoid the “Too Many SOQL Queries: 101” error in Salesforce:
Original Code (Causes Error):
In this original example, a SOQL query is executed inside a loop, which can quickly exceed the governor limit if there are many Account records.
public class AccountProcessor {
public static void updateContactsForAccounts(List<Account> accounts) {
for (Account acc : accounts) {
List<Contact> contacts = [SELECT Id, Email FROM Contact WHERE AccountId = :acc.Id];
for (Contact con : contacts) {
con.Email = acc.Email__c;
update con;
}
}
}
}Optimized Code (Avoids Error):
In the optimized code, the SOQL query is moved outside the loop, and a map is used to associate Contact records with their corresponding Account. This reduces the number of SOQL queries and allows for bulk processing of records.
public class AccountProcessor {
public static void updateContactsForAccounts(List<Account> accounts) {
// Step 1: Collect all Account IDs
Set<Id> accountIds = new Set<Id>();
for (Account acc : accounts) {
accountIds.add(acc.Id);
}
// Step 2: Bulk query Contacts related to the Accounts
Map<Id, List<Contact>> contactsByAccount = new Map<Id, List<Contact>>();
for (Contact con : [SELECT Id, Email, AccountId FROM Contact WHERE AccountId IN :accountIds]) {
if (!contactsByAccount.containsKey(con.AccountId)) {
contactsByAccount.put(con.AccountId, new List<Contact>());
}
contactsByAccount.get(con.AccountId).add(con);
}
// Step 3: Process and update Contacts in bulk
List<Contact> contactsToUpdate = new List<Contact>();
for (Account acc : accounts) {
if (contactsByAccount.containsKey(acc.Id)) {
for (Contact con : contactsByAccount.get(acc.Id)) {
con.Email = acc.Email__c;
contactsToUpdate.add(con);
}
}
}
update contactsToUpdate; // Perform a single DML operation
}
}In this optimized code, the SOQL query is executed only once, outside the loop, which significantly reduces the number of queries. The results are stored in a map, allowing for efficient processing of related records. Finally, the Contact records are updated in a single bulk DML operation, further optimizing performance and resource usage.
