Common Causes of SOQL 101 Errors & How to Refactor for Bulk Ingestion

What the SOQL 101 error means

The full message is System.LimitException: Too many SOQL queries: 101. Salesforce allows 100 SOQL queries per synchronous transaction and 200 per asynchronous one (Batch Apex, Queueable and future methods). The 101st query hits the limit, which is where the error gets its name.

There are three things worth knowing about this limit:

• It's per transaction, not per trigger. Every trigger, Flow, validation-driven automation and uncertified managed package that runs during the save shares the same count. Certified managed packages get their own allocation.
• It can't be caught. LimitException can't be handled with try/catch, so the whole transaction rolls back. For an integration, that can mean the whole batch of records fails.
• Volume exposes it. Triggers process records in chunks of 200, and all the chunks from one Apex DML statement share one transaction. Code that's fine with one record from the UI can fail as soon as an integration sends a batch.

Some calls count even though they don't look like queries: Database.query, Database.countQuery and Database.getQueryLocator all count. Queries against custom metadata types don't count towards the limit.

The 5 usual causes

1. SOQL inside a loop

This is the classic cause. A query inside for (Opportunity opp : Trigger.new) runs once per record, so 101 records means 101 queries.

2. Hidden queries in helper methods

The loop looks clean, but it calls AccountService.getRegion(opp.AccountId) and that method queries. Reviewers often miss this because the query sits in a different class from the loop.

3. Recursion and re-entry

An after-update trigger updates related records, their triggers update the original object, and the original trigger runs its queries again. Each pass spends more of the shared budget. A badly placed recursion guard can make things worse, because it can skip the logic in later chunks of 200.

4. Flows that query inside loops, alongside Apex

Flow Builder makes it easy to put a Get Records element inside a Loop. Each iteration is a query. Record-triggered Flows and Apex triggers on the same object then add up against one limit, so neither looks bad on its own and together they fail. The Flow runtime does batch the same element across interviews in a bulk save, but it can't batch elements that run inside a loop.

5. Reference data queried repeatedly

Record type IDs, configuration settings or owner mappings get queried every time a method runs, sometimes many times per transaction. These are better served from static caches, Schema describe methods, or custom metadata types, which don't count towards the query limit.

Before and after: refactoring a trigger for bulk ingestion

Take a common requirement: when an Opportunity is created or updated, copy the Account's region and assign an owner from a regional mapping.

Before. This version runs two queries per record and puts logic in the trigger body:

trigger OpportunityTrigger on Opportunity (before insert, before update) {
    for (Opportunity opp : Trigger.new) {
        Account acc = [SELECT Id, Region__c FROM Account WHERE Id = :opp.AccountId];
        opp.Region__c = acc.Region__c;

        Region_Owner__c mapping = [
            SELECT Owner_Id__c FROM Region_Owner__c
            WHERE Region__c = :acc.Region__c LIMIT 1
        ];
        opp.OwnerId = mapping.Owner_Id__c;
    }
}

With 51 records this reaches 102 queries and fails. It also throws a QueryException if any Opportunity has no Account or no mapping exists.

After. One trigger per object passes control to a handler, which runs exactly two queries however many records arrive:

trigger OpportunityTrigger on Opportunity (before insert, before update) {
    OpportunityTriggerHandler.beforeSave(Trigger.new);
}

public with sharing class OpportunityTriggerHandler {

    public static void beforeSave(List<Opportunity> opps) {
        Set<Id> accountIds = new Set<Id>();
        for (Opportunity opp : opps) {
            if (opp.AccountId != null) {
                accountIds.add(opp.AccountId);
            }
        }
        if (accountIds.isEmpty()) {
            return;
        }

        // Query 1: all parent Accounts for this chunk
        Map<Id, Account> accounts = new Map<Id, Account>(
            [SELECT Id, Region__c FROM Account WHERE Id IN :accountIds]
        );

        Set<String> regions = new Set<String>();
        for (Account acc : accounts.values()) {
            if (acc.Region__c != null) {
                regions.add(acc.Region__c);
            }
        }

        // Query 2: all regional owner mappings needed
        Map<String, String> ownerByRegion = new Map<String, String>();
        for (Region_Owner__c m : [
            SELECT Region__c, Owner_Id__c FROM Region_Owner__c
            WHERE Region__c IN :regions
        ]) {
            ownerByRegion.put(m.Region__c, m.Owner_Id__c);
        }

        for (Opportunity opp : opps) {
            Account acc = accounts.get(opp.AccountId);
            if (acc == null) {
                continue;
            }
            opp.Region__c = acc.Region__c;
            if (ownerByRegion.containsKey(acc.Region__c)) {
                opp.OwnerId = ownerByRegion.get(acc.Region__c);
            }
        }
    }
}

The pattern to remember: collect keys, query once with IN, build maps, loop in memory. Because this runs before save, the field changes need no extra DML either. For frequently used reference data, move the mapping to a custom metadata type and the second query stops counting towards the limit.

Flow equivalent. In a record-triggered Flow, move Get Records out of the loop. Fetch the related collection once before the loop, using a filter such as "Id In" where your Flow version supports it, and use Assignment elements inside the loop instead of data elements. For same-record field updates, use a before-save (Fast Field Updates) Flow.

Testing with 200+ records

A test that inserts one record proves almost nothing about bulk behaviour. Test with more than 200 records, so the trigger runs over more than one chunk in a single transaction, and assert on the query count:

@IsTest
private class OpportunityTriggerHandlerTest {

    @TestSetup
    static void setup() {
        insert new Region_Owner__c(Region__c = 'EMEA', Owner_Id__c = UserInfo.getUserId());
        insert new Account(Name = 'Bulk Test Ltd', Region__c = 'EMEA');
    }

    @IsTest
    static void assignsRegionForBulkInsert() {
        Account acc = [SELECT Id FROM Account LIMIT 1];
        List<Opportunity> opps = new List<Opportunity>();
        for (Integer i = 0; i < 250; i++) {
            opps.add(new Opportunity(
                Name = 'Bulk ' + i, AccountId = acc.Id,
                StageName = 'Prospecting', CloseDate = Date.today().addDays(30)));
        }

        Test.startTest();
        insert opps;
        Integer queriesUsed = Limits.getQueries();
        Test.stopTest();

        // Two chunks (200 + 50), two queries each; allow headroom for other automation
        Assert.isTrue(queriesUsed <= 10, 'Query count should not scale with record count: ' + queriesUsed);
        Assert.areEqual(250, [SELECT COUNT() FROM Opportunity WHERE Region__c = 'EMEA']);
    }
}

A few more practices that catch problems early:

• Run bulk tests for insert, update and delete, including updates where only some records meet the criteria.
• Include mixed data: records with null lookups, several parent records and several regions.
• Load representative volumes (thousands of records) in a full or partial sandbox using the same tool as production, whether that's Data Loader, Bulk API 2.0 or your middleware, and read the LIMIT_USAGE_FOR_NS section of the debug log.
• Add a static analysis rule (Salesforce Code Analyzer/PMD) to the pipeline so new SOQL-in-loop code gets flagged before merge.
• Test the retry path too. When an integration retries a batch that failed on a limit error, it shouldn't create duplicates. Upserting on an external ID helps here; see idempotent ingestion layers.

For the wider set of limits that tend to fail alongside SOQL 101, see the governor limits audit checklist.