Skip to main content

Resolving System.LimitException: Too Many SOQL Queries 101 in Apex Triggers

 It usually happens during a data migration, a complex API integration, or a mass update via Data Loader. Your transaction fails abruptly, rolling back all database changes, and logs the dreaded System.LimitException: Too many SOQL queries: 101 error.

This exception is a fundamental rite of passage for Salesforce developers. It indicates a critical architectural flaw in how your Apex code interacts with the platform's database. Resolving this issue requires shifting from record-centric processing to set-based processing.

Understanding Salesforce Governor Limits and the 101 Error

Salesforce operates on a multi-tenant architecture. This means your organization shares computing resources with thousands of other customers on the same server instance. To prevent a single poorly optimized script from monopolizing the database, Salesforce enforces strict execution boundaries known as Salesforce governor limits.

One of the most rigid limits dictates that a single synchronous transaction can execute a maximum of 100 SOQL queries. If your code executes a 101st query, the platform immediately kills the transaction.

This error almost exclusively originates from the N+1 query problem. This occurs when a developer places a SOQL query inside a for loop that iterates over a collection of records, such as Trigger.new.

The Anti-Pattern: Queries Inside a Loop

Consider the following trigger logic. It attempts to count the number of related Contacts for each Account being updated.

// ANTI-PATTERN: DO NOT USE IN PRODUCTION
trigger AccountTrigger on Account (before update) {
    for (Account acc : Trigger.new) {
        // A SOQL query executes for every single record in the transaction
        List<Contact> contacts = [
            SELECT Id 
            FROM Contact 
            WHERE AccountId = :acc.Id
        ];
        acc.Custom_Contact_Count__c = contacts.size();
    }
}

If a user manually updates a single Account in the UI, this code consumes exactly one SOQL query. The limit is not breached, and the code appears to work. However, if a Data Loader batch pushes 200 Accounts simultaneously, the loop executes 200 times. On the 101st iteration, the transaction violently fails.

The Solution: How to Bulkify Apex Triggers

To fix this, you must bulkify Apex triggers. Bulkification is the process of combining multiple granular database requests into a single, unified request.

The standard design pattern for bulkification involves three steps:

  1. Iterate over the trigger records to collect the necessary IDs into a Set.
  2. Perform a single SOQL query outside the loop using the IN bind operator, storing the results in a Map.
  3. Iterate over the trigger records a second time, using the Map to retrieve the related data in memory.

The Corrected Bulkified Implementation

To maintain clean architecture, we will place this logic inside an Apex handler class rather than the .trigger file itself. We will also utilize an AggregateResult query, representing a superior approach to Salesforce SOQL optimization.

public class AccountTriggerHandler {
    
    public static void updateContactCounts(List<Account> accounts) {
        // 1. Initialize a Set to collect unique Account IDs
        Set<Id> accountIds = new Set<Id>();
        for (Account acc : accounts) {
            accountIds.add(acc.Id);
        }

        // 2. Prevent consuming a query if the Set is empty
        if (accountIds.isEmpty()) {
            return;
        }

        // 3. Query once and store results in a Map to relate Contact counts to Account IDs
        Map<Id, Integer> contactCountMap = new Map<Id, Integer>();
        
        for (AggregateResult ar : [
            SELECT AccountId, COUNT(Id) contactCount 
            FROM Contact 
            WHERE AccountId IN :accountIds 
            GROUP BY AccountId
        ]) {
            Id accId = (Id) ar.get('AccountId');
            Integer count = (Integer) ar.get('contactCount');
            contactCountMap.put(accId, count);
        }

        // 4. Iterate over the trigger records again to apply the changes from memory
        for (Account acc : accounts) {
            // Default to 0 if the Map does not contain the Account ID (meaning no Contacts exist)
            Integer count = contactCountMap.containsKey(acc.Id) ? contactCountMap.get(acc.Id) : 0;
            acc.Custom_Contact_Count__c = count;
        }
    }
}

Code Breakdown: Why This Salesforce SOQL Optimization Works

The updated logic guarantees that regardless of whether Trigger.new contains 1 record or 10,000 records, the transaction will only consume exactly one SOQL query.

Step 1: Memory Collection via Sets

We first isolate the data required to filter our database query. By storing Account.Id values in a Set<Id>, we automatically deduplicate the identifiers. This ensures our WHERE clause is as lean as possible.

Step 2: The Aggregate SOQL Query

Instead of querying full Contact SObjects just to check the size() of the list, we utilize COUNT(Id) and GROUP BY AccountId. Returning thousands of SObjects risks hitting a different governor limit: the 50,000 SOQL returned row limit. Grouping at the database level minimizes both query counts and heap size memory consumption.

Step 3: O(1) Time Complexity with Maps

The final loop iterates through the Accounts and looks up the related count using contactCountMap.get(acc.Id). In Apex, Map lookups execute in O(1) constant time complexity. This means fetching the related data from memory is near-instantaneous and has zero impact on database CPU limits.

Common Pitfalls and Edge Cases

Even seasoned developers can introduce scaling issues when refactoring for the "Too many SOQL queries 101" limit. Be mindful of these common edge cases.

Querying Empty Collections

Always check if your Set or List is empty before executing a SOQL query. If accountIds is empty, [SELECT ... WHERE AccountId IN :accountIds] will still execute and return zero rows. This wastes one of your precious 100 queries for absolutely no reason. Use if (accountIds.isEmpty()) return; to short-circuit the execution.

Trigger Recursion

If your newly bulkified trigger updates related records, which then fire their own triggers, which then update the original records, you create an infinite loop. This recursion will rapidly exhaust your 100 SOQL queries across multiple execution contexts. Always implement a static boolean flag or a dedicated recursion control framework in your trigger handlers to ensure logic runs only once per transaction.

Nested SOQL Queries (Parent-to-Child)

If you legitimately need the child SObject data (not just a count), use a subquery to retrieve both parent and child records simultaneously.

// Valid bulkified approach for retrieving full child records
List<Account> accountsWithContacts = [
    SELECT Id, (SELECT Id, FirstName, LastName FROM Contacts) 
    FROM Account 
    WHERE Id IN :accountIds
];

This pattern allows you to loop through Account.Contacts in memory without issuing separate queries for each parent record. However, be cautious with subqueries on extremely large datasets, as iterating over massive nested lists can strain the Apex CPU time limit (10,000 milliseconds for synchronous transactions).

Labels: