Too Many SOQL Queries: 101 Error causes with Complex Operations

Too Many SOQL Queries: 101 Error causes with Complex Operations

On February 21, 2024, Posted by , In Salesforce, With Comments Off on Too Many SOQL Queries: 101 Error causes with Complex Operations

The “Too Many SOQL Queries: 101” error in Salesforce can also occur when complex operations, such as nested loops with SOQL queries or multiple operations within a single transaction, are performed. Here’s an example of a complex operation that might cause this error:

Suppose you have a requirement to update all related Contact records when an Account is updated. For each Contact, you need to check if there are any related Opportunities with a certain status and then update the Contact based on that information.

Code That Causes the Error:

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) {
            List<Opportunity> opps = [SELECT Id, StageName FROM Opportunity WHERE AccountId = :acc.Id];
            for (Opportunity opp : opps) {
                if (opp.StageName == 'Closed Won') {
                    con.Status__c = 'High Value';
                    break;
                }
            }
            update con;
        }
    }
}

In this example, there are nested loops with SOQL queries inside them. This can quickly lead to exceeding the SOQL query limit if there are many Account records being processed, each with multiple related Contact and Opportunity records.

Solution: Optimize the Code

To avoid the “Too Many SOQL Queries: 101” error, you should optimize the code by reducing the number of SOQL queries and using bulk operations:

trigger AccountTrigger on Account (after update) {
    Set<Id> accountIds = new Set<Id>();
    for (Account acc : Trigger.new) {
        accountIds.add(acc.Id);
    }

    Map<Id, List<Contact>> contactsByAccount = new Map<Id, List<Contact>>();
    for (Contact con : [SELECT Id, 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);
    }

    Set<Id> highValueAccountIds = new Set<Id>();
    for (Opportunity opp : [SELECT Id, AccountId FROM Opportunity WHERE StageName = 'Closed Won' AND AccountId IN :accountIds]) {
        highValueAccountIds.add(opp.AccountId);
    }

    List<Contact> contactsToUpdate = new List<Contact>();
    for (Id accountId : highValueAccountIds) {
        for (Contact con : contactsByAccount.get(accountId)) {
            con.Status__c = 'High Value';
            contactsToUpdate.add(con);
        }
    }

    update contactsToUpdate;
}

In the optimized code, SOQL queries are moved outside the loops, and bulk operations are used to update the Contact records. This reduces the number of SOQL queries and helps avoid hitting the governor limit.

← →
Comments are closed.