System.LimitException: Too many SOQL queries: 101

A bulk job that updates 200 accounts dies with System.LimitException: Too many SOQL queries: 101. The trigger code looks tidy: for each account, query a setting, query related contacts, query an opportunity count. Three queries per account, two hundred accounts, six hundred queries, transaction blows up at query 101. The fix is structural, mechanical, and known by every Apex developer eventually.

The 100-query cap

Apex limits a single transaction to 100 SOQL query statements. The 101st query throws the LimitException you're seeing. The cap is per-transaction, not per-method or per-class; every query in every helper, trigger, and managed-package call counts together.

The 100 is the synchronous cap. Async contexts (Queueable, Batch execute, @future) get 200 SOQL queries per execution. So an architectural move toward async doubles your query headroom, separately from the bulkification fix.

The cap exists for the same reason every Apex governor exists: the platform's database serves many tenants, and an uncapped transaction could starve everyone else. Forcing the developer to bulkify keeps the platform fast for all users.

The broken example

A trigger that enriches accounts with related data:

trigger AccountEnricherTrigger on Account (before insert, before update) {
    for (Account a : Trigger.new) {
        // Query 1: opportunity count
        Integer oppCount = [SELECT COUNT() FROM Opportunity WHERE AccountId = :a.Id];
        a.Opportunity_Count__c = oppCount;

        // Query 2: most recent contact
        List<Contact> contacts = [SELECT Email FROM Contact WHERE AccountId = :a.Id ORDER BY LastModifiedDate DESC LIMIT 1];
        a.Last_Contact_Email__c = contacts.isEmpty() ? null : contacts[0].Email;

        // Query 3: setting value
        Account_Setting__c setting = [SELECT Value__c FROM Account_Setting__c WHERE Region__c = :a.BillingCountry LIMIT 1];
        a.Default_Value__c = setting.Value__c;
    }
}

Three queries per account in the loop. For batch sizes above 33, the transaction hits 101 queries.

The fix: hoist queries out of the loop

Collect every record id (or every filter value) before the loop, run one query per filter set, then walk the results inside the loop. The pattern is mechanical: never put a [SELECT ...] inside a for loop.

trigger AccountEnricherTrigger on Account (before insert, before update) {
    Set<Id> accountIds = new Set<Id>();
    Set<String> countries = new Set<String>();
    for (Account a : Trigger.new) {
        if (a.Id != null) accountIds.add(a.Id);
        if (a.BillingCountry != null) countries.add(a.BillingCountry);
    }

    // One query each, bulk-safe.
    Map<Id, Integer> oppCountByAccount = new Map<Id, Integer>();
    for (AggregateResult ar : [
        SELECT AccountId acct, COUNT(Id) c
        FROM Opportunity
        WHERE AccountId IN :accountIds
        GROUP BY AccountId
    ]) {
        oppCountByAccount.put((Id) ar.get('acct'), (Integer) ar.get('c'));
    }

    Map<Id, String> emailByAccount = new Map<Id, String>();
    for (Contact c : [
        SELECT AccountId, Email
        FROM Contact
        WHERE AccountId IN :accountIds
        ORDER BY LastModifiedDate DESC
    ]) {
        if (!emailByAccount.containsKey(c.AccountId)) {
            emailByAccount.put(c.AccountId, c.Email);
        }
    }

    Map<String, String> defaultByRegion = new Map<String, String>();
    for (Account_Setting__c s : [SELECT Region__c, Value__c FROM Account_Setting__c WHERE Region__c IN :countries]) {
        defaultByRegion.put(s.Region__c, s.Value__c);
    }

    for (Account a : Trigger.new) {
        a.Opportunity_Count__c = oppCountByAccount.containsKey(a.Id) ? oppCountByAccount.get(a.Id) : 0;
        a.Last_Contact_Email__c = emailByAccount.get(a.Id);
        a.Default_Value__c = defaultByRegion.get(a.BillingCountry);
    }
}

Three queries total, regardless of the batch size. The transaction uses 3 of its 100 budget. Plenty of headroom.

The fixed example, end to end

A service class that wraps the enrichment pattern:

public class AccountEnricher {
    public static void enrich(List<Account> accounts) {
        if (accounts.isEmpty()) return;

        Set<Id> accountIds = new Set<Id>();
        Set<String> countries = new Set<String>();
        for (Account a : accounts) {
            if (a.Id != null) accountIds.add(a.Id);
            if (a.BillingCountry != null) countries.add(a.BillingCountry);
        }

        Map<Id, Integer> oppCountByAccount = buildOppCountMap(accountIds);
        Map<Id, String> emailByAccount = buildLatestEmailMap(accountIds);
        Map<String, String> defaultByRegion = buildDefaultsMap(countries);

        for (Account a : accounts) {
            a.Opportunity_Count__c = oppCountByAccount.containsKey(a.Id) ? oppCountByAccount.get(a.Id) : 0;
            a.Last_Contact_Email__c = emailByAccount.get(a.Id);
            a.Default_Value__c = defaultByRegion.get(a.BillingCountry);
        }
    }

    private static Map<Id, Integer> buildOppCountMap(Set<Id> accountIds) {
        Map<Id, Integer> result = new Map<Id, Integer>();
        if (accountIds.isEmpty()) return result;
        for (AggregateResult ar : [
            SELECT AccountId acct, COUNT(Id) c FROM Opportunity
            WHERE AccountId IN :accountIds GROUP BY AccountId
        ]) {
            result.put((Id) ar.get('acct'), (Integer) ar.get('c'));
        }
        return result;
    }

    // ... two more similar helpers ...
}

trigger AccountEnricherTrigger on Account (before insert, before update) {
    AccountEnricher.enrich(Trigger.new);
}

The service is unit-testable independently of the trigger. The query-builder helpers are small and focused. The pattern scales to any batch size.

The "query inside a recursive trigger" trap

A trigger that updates records of the same type fires itself recursively (the second invocation is the same trigger on the records you just changed). Each recursive level adds its own queries to the transaction's budget. A trigger that runs 3 queries in its main path, and is invoked 5 times recursively, uses 15 queries. Pile in batch automation, and 100 is reachable.

Recursive trigger handling is its own topic (most teams use a static flag to prevent recursion). The query-count side: if you can't easily bulkify a recursive trigger, your only options are to short-circuit the recursion or to move some work to async.

Hidden queries from managed packages

Managed packages can add queries to a transaction without your code's knowledge. A managed-package trigger on the same object as yours might add 5 queries per save. If your trigger uses 90 queries, the package pushes you over.

The diagnostic: turn on Apex debug logs with "SOQL Queries" set to FINE. The log shows every query in order with the class that issued it. Managed-package queries show up with the package's namespace prefix. If a package is consuming significant budget, plan around it (move your work async, or open a support case with the package vendor).

The 100 vs 50,000 row cap

Two governors limit SOQL. The 100-statement cap is one. The other is the 50,000-row aggregate cap: across all SOQL queries in the transaction, the total number of returned rows can't exceed 50,000. A single [SELECT Id FROM Account] in a large org can hit the row cap on its own.

The two caps fail with different exceptions: Too many SOQL queries: 101 for the statement cap, Number of rows: 50001 too large for the row cap. Both are fixed by bulkification, but row-cap problems often require WHERE clauses or Batch Apex, not just hoisting out of a loop.

Diagnostic: Limits.getQueries

Limits.getQueries() returns the count of SOQL statements used so far. Drop debug lines at suspected hot spots:

System.debug('Queries: ' + Limits.getQueries() + '/' + Limits.getLimitQueries());

For long methods, instrument the start and end. The delta tells you how many queries that method contributed. Compare across runs to confirm a fix actually reduced the count.

The bulkification mental model

A trigger that's truly bulk-safe satisfies one invariant: the number of queries it does is independent of the batch size. Bulkify until that's true.

Apply the invariant to every other governor too:

• Number of DML statements: independent of batch size.
• CPU time: scales linearly but doesn't bottleneck.
• Heap size: independent of batch size for streaming operations.

A trigger that satisfies these invariants for every governor is bulk-safe at any reasonable batch size. The invariants are what bulkification actually means.

Async as a partial escape

If your code legitimately needs 200 queries (because of a complex enrichment workflow), an async context's 200-query cap accommodates it. But "needs" is rare. Most appearances of "I need more queries" are unbulkified code in disguise. Verify the bulkification is genuinely complete before reaching for async.

Test patterns

A test that verifies bulkification:

@isTest
static void enrich_isBulkSafe() {
    List<Account> accounts = new List<Account>();
    for (Integer i = 0; i < 200; i++) {
        accounts.add(new Account(Name = 'Test ' + i, BillingCountry = 'US'));
    }
    Integer queriesBefore = Limits.getQueries();
    Test.startTest();
    AccountEnricher.enrich(accounts);
    Test.stopTest();
    Integer queriesAfter = Limits.getQueries();
    System.assert(queriesAfter - queriesBefore <= 5,
        'Enrichment should use 5 or fewer queries regardless of batch size');
}

The test pins the query count below a small constant. Regressions that reintroduce queries-in-loop fail immediately.

A pattern for repeatable bulk-safety

Build a Selector class per object that owns all SOQL queries against that object. The selector exposes typed methods that take collections and return collections (or maps). Callers can't write loose SOQL; they go through the selector.

public class OpportunitySelector {
    public static Map<Id, Integer> countsByAccountId(Set<Id> accountIds) {
        Map<Id, Integer> result = new Map<Id, Integer>();
        if (accountIds.isEmpty()) return result;
        for (AggregateResult ar : [
            SELECT AccountId acct, COUNT(Id) c FROM Opportunity
            WHERE AccountId IN :accountIds GROUP BY AccountId
        ]) {
            result.put((Id) ar.get('acct'), (Integer) ar.get('c'));
        }
        return result;
    }
    // ... other typed queries ...
}

Every consumer that needs opportunity counts by account id calls OpportunitySelector.countsByAccountId(ids). The selector centralizes the query and guarantees bulk-safety because callers pass collections.

The pattern is called the Selector Pattern (popularized by the Apex Enterprise Patterns library). It scales gracefully and makes code review easier: a reviewer checks the selector class once, and every consumer is bulk-safe by construction.

A common subtle case: querying through map keys

A pattern that looks bulkified but isn't:

Map<Id, Account> existing = new Map<Id, Account>([SELECT Id, Name FROM Account WHERE Id IN :ids]);
for (Id targetId : ids) {
    if (!existing.containsKey(targetId)) {
        Account a = [SELECT Id FROM Account WHERE External_Id__c = :buildExternalId(targetId) LIMIT 1];   // Hidden query in loop
    }
}

The map lookup is fine; the inline SOQL inside the loop is the bug. Code reviewers sometimes miss this because the loop body is short. The fix is to collect the external ids first and run one query for all of them.

Architecture: when to push to batch

If you find yourself fighting the 100-query cap repeatedly across the same trigger, the architecture might be wrong for the scale. Batch Apex gives each execute() invocation a fresh 200-query budget, and the platform handles chunking for you. A trigger that needs many queries per save is often a sign that the work should be batch-processed instead of triggered-processed.

The cost is latency: batch runs on the platform's schedule, not in the user's save context. For UX-critical operations, batch isn't a substitute. For background enrichment, it almost always is.

A practical refactor checklist

When you see Too many SOQL queries: 101 in a production log, walk this list:

1. Open the failing trigger's debug log. Identify which method ran the 101st query.
2. Trace from that method up to the trigger entry point. Find every [SELECT ...] in the chain.
3. For each query, ask: is it inside a loop?
4. For each query inside a loop, ask: can I hoist it out with IN :collection?
5. Apply the hoist. Add a unit test that exercises a 200-record batch.
6. Re-run the failing scenario. Confirm the query count drops below 100.

The checklist takes thirty to ninety minutes per incident. Each application teaches the team to spot the pattern earlier; over time, new code starts shipping bulk-safe by default.

Related governors to know

The query count cap doesn't operate alone. When a transaction approaches it, you're usually also approaching:

• 50,000 query rows (the row-cap governor).
• 150 DML statements (if you're doing DML alongside queries).
• 10,000 DML rows.
• Apex CPU time (especially if the query results are processed inline).

Resolving the SOQL count by bulkifying often resolves these other governors too. The same hoist that takes you from 200 queries to 3 also takes you from 200 result-set iterations to 1, dropping CPU substantially.

Why the platform makes this limit visible at runtime

The platform could silently slow you down past 100 queries; instead, it throws a hard exception. The reason is the same as for the DML statement cap: silent slowdown produces hard-to-diagnose performance complaints, but an exception names the exact problem.

The trade-off is that uncaught code crashes. The diagnostic clarity is the benefit.

Reading the debug log for query origins

For a failing transaction with many queries, the Apex debug log's SOQL_EXECUTE_BEGIN events tell you exactly which class and line issued each query. The log format includes:

• The query text.
• The class and method name.
• The line number within the source.
• The elapsed time for the query.

Scan the log for the 101 lines. Group by class to see which class is responsible for the most queries. Often a single helper method is the source of dozens; fixing that one method drops the count substantially.

For long debug logs, save them locally and grep:

grep "SOQL_EXECUTE_BEGIN" debug.log | sort | uniq -c | sort -rn | head -20

The top of the output is the most-frequently-issued queries. Those are the ones that benefit most from bulkification.

A quirk: managed-package queries can't be optimized by you

If a managed package contributes many queries to your transaction, your only options are to reduce your own count to make room or to ask the vendor for a fix. You can't restructure the package's queries.

This is one of the reasons to evaluate managed packages for query footprint before installing. A package that issues 30 queries per save in a busy trigger context can hurt every save on that object.

Further reading from Salesforce

• Apex Developer Guide: SOQL and SOSL Queries
• Apex Developer Guide: Bulk Apex Triggers
• Apex Developer Guide: Execution Governors and Limits
• Apex Developer Guide: Limits Class
• Trailhead: Apex Triggers and Bulk Processing