How to optimize SOQL queries to avoid the “Too Many SOQL Queries: 101”?

How to optimize SOQL queries to avoid the “Too Many SOQL Queries: 101”?

On January 3, 2024, Posted by , In Salesforce, With Comments Off on 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.

Comments are closed.