System.LimitException: Too many query rows: 50001

A Wednesday-morning batch job calculates revenue tiers across every customer and updates a custom field on each Account. The org has grown to 65,000 active accounts. The batch fires, the first chunk runs, and then the entire job halts with System.LimitException: Too many query rows: 50001. The Apex code that worked when the org had 20,000 accounts is now over the line. The team needs to retrofit governor-limit discipline into the existing class.

What the platform is checking

Apex enforces a per-transaction limit of 50,000 records returned by SOQL queries. The runtime keeps a running count of every row produced by every query in the current transaction. When a single query would push the total past 50,000, or when a query returns its first row past 50,000, the runtime throws LimitException: Too many query rows: 50001. The number is always 50001 because the limit is "fewer than 50,001 retrieved" and the breach happens at row 50,001.

The limit applies to records the runtime materializes into memory, not to the count of rows the query could theoretically match. A query that uses LIMIT 100 against an object with a million matching rows counts as 100. A query without a LIMIT clause against an object with 60,000 matching rows tries to materialize all 60,000, and the limit fires when the 50,001st row is loaded.

The limit applies across queries. Five queries that each return 12,000 rows produce 60,000 retrieved rows and trip the limit on the fifth query. A trigger that issues a query for each of 200 records can easily exceed 50,000 retrieved rows in aggregate, even when no individual query is large.

The fix is structural. Reduce the number of rows retrieved, or change the unit of work so the rows are processed in smaller chunks across multiple transactions.

The broken example

A batch class that processes accounts and queries related Opportunities per account:

public class RevenueTierBatch implements Database.Batchable<SObject> {
    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('SELECT Id, Name FROM Account');
    }

    public void execute(Database.BatchableContext bc, List<Account> scope) {
        for (Account a : scope) {
            List<Opportunity> opps = [
                SELECT Amount FROM Opportunity
                WHERE AccountId = :a.Id AND IsClosed = true
            ];
            Decimal total = 0;
            for (Opportunity o : opps) {
                total += o.Amount;
            }
            a.Revenue_Tier__c = classifyTier(total);
        }
        update scope;
    }

    public void finish(Database.BatchableContext bc) {}
}

The batch processes 200 accounts per execute call, by default. Each account triggers its own SOQL query against Opportunity. If many accounts have hundreds of closed Opportunities, the cumulative row count in a single execute call can pass 50,000. The query that crosses the line throws.

A second shape, outside a batch: a one-shot script that scans the whole org.

List<Account> allAccounts = [SELECT Id, Name FROM Account]; // 65,000 rows, fails

A third shape: a query joined to a child relationship that returns many child rows per parent.

List<Account> accounts = [
    SELECT Id, Name, (SELECT Id FROM Cases) FROM Account
];

The parent rows are 5,000 but each carries an arbitrary number of Cases. The platform counts both the parent rows and the child rows toward the limit. If the average is twelve Cases per Account, you have already retrieved 65,000 rows.

The fix, three paths

Aggregate at the database, not in Apex. Many calculations can be expressed as SUM, COUNT, or AVG directly in SOQL. The aggregate query returns one row per group, not one row per record.

List<AggregateResult> tiers = [
    SELECT AccountId, SUM(Amount) total
    FROM Opportunity
    WHERE IsClosed = true
    GROUP BY AccountId
    LIMIT 50000
];

The aggregate query returns one row per Account. The 65,000 individual Opportunity rows never enter Apex memory. The total row count is the number of distinct Accounts with closed Opportunities.

Move from a single query to a Database.QueryLocator with batchable scope. Batch Apex is designed for this exact problem. The QueryLocator can return up to 50 million rows, but the rows are streamed to execute methods in chunks of 200 (configurable) so the per-transaction limit never applies to the full set.

The earlier example's batch start is already a QueryLocator. The problem is inside execute, which queries Opportunity per Account. Restructure to query Opportunity at the start as the locator, group by AccountId in execute, and write the result.

public Database.QueryLocator start(Database.BatchableContext bc) {
    return Database.getQueryLocator(
        'SELECT AccountId, Amount FROM Opportunity WHERE IsClosed = true ORDER BY AccountId'
    );
}

public void execute(Database.BatchableContext bc, List<Opportunity> scope) {
    Map<Id, Decimal> totalsByAccount = new Map<Id, Decimal>();
    for (Opportunity o : scope) {
        Decimal current = totalsByAccount.get(o.AccountId);
        totalsByAccount.put(o.AccountId, (current == null ? 0 : current) + o.Amount);
    }
    // Update accounts in this chunk
}

The structure now processes 200 Opportunities per chunk and accumulates the totals. The final write per Account needs careful handling because totals for one Account may span chunks; an alternative is to do the aggregation in SOQL and use the AggregateResult as the locator.

Use selective queries with WHERE clauses that match an indexed field. A WHERE clause that filters on an indexed field with a selective predicate keeps the result set small. Adding LIMIT 50000 on a query you expect to be selective is a safety net that fails predictably instead of unpredictably.

For the broken [SELECT Id, Name FROM Account] script, restrict by date or ownership.

List<Account> recentAccounts = [
    SELECT Id, Name FROM Account
    WHERE LastModifiedDate >= LAST_N_DAYS:30
    LIMIT 50000
];

The fixed example

A revenue-tier batch that respects governor limits by leveraging aggregation:

public class RevenueTierBatch implements Database.Batchable<SObject>, Database.Stateful {
    private Map<Id, Decimal> totalsByAccount = new Map<Id, Decimal>();

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator(
            'SELECT Id, AccountId, Amount FROM Opportunity WHERE IsClosed = true'
        );
    }

    public void execute(Database.BatchableContext bc, List<Opportunity> scope) {
        for (Opportunity o : scope) {
            if (o.AccountId == null) {
                continue;
            }
            Decimal current = totalsByAccount.get(o.AccountId);
            totalsByAccount.put(o.AccountId, (current == null ? 0 : current) + o.Amount);
        }
    }

    public void finish(Database.BatchableContext bc) {
        List<Account> updates = new List<Account>();
        for (Id accountId : totalsByAccount.keySet()) {
            updates.add(new Account(
                Id = accountId,
                Revenue_Tier__c = classifyTier(totalsByAccount.get(accountId))
            ));
        }
        // Update in chunks to avoid DML row limits as well
        Database.update(updates, false);
    }

    private static String classifyTier(Decimal total) {
        if (total == null || total < 10000) return 'Bronze';
        if (total < 100000) return 'Silver';
        if (total < 1000000) return 'Gold';
        return 'Platinum';
    }
}

The batch uses Database.Stateful to retain the totals across execute invocations. The locator streams the Opportunities. Each execute consumes a 200-row chunk and aggregates locally. The finish method writes the Account updates.

The pattern handles million-row Opportunity tables without hitting the query-row limit because the locator is not subject to the 50,000-row cap; only individual transactions are.

Edge case: lookup queries inside a loop

A common anti-pattern is querying inside a for loop.

for (Account a : accounts) {
    Integer count = [SELECT COUNT() FROM Contact WHERE AccountId = :a.Id];
}

This violates two limits at once. The 100-query-per-transaction limit fires first for trigger contexts. In a batch or anonymous-Apex context with a higher SOQL ceiling, the row-count limit fires when the cumulative results exceed 50,000.

The fix is bulkification.

Map<Id, Integer> countsByAccount = new Map<Id, Integer>();
for (AggregateResult ar : [
    SELECT AccountId, COUNT(Id) c
    FROM Contact
    WHERE AccountId IN :accountIds
    GROUP BY AccountId
]) {
    countsByAccount.put((Id) ar.get('AccountId'), (Integer) ar.get('c'));
}

One query, one result row per Account, regardless of how many Contacts each Account has.

Edge case: subqueries and child relationships

A query with a child subquery counts both parent and child rows toward the 50,000-row total.

[SELECT Id, (SELECT Id FROM Cases) FROM Account WHERE Industry = 'Banking']

If the result is 1,000 Accounts and each has an average of 60 Cases, the materialized rows are 61,000 and the query throws. Move the child query out and run it separately with its own selectivity filter, or aggregate the children directly.

Edge case: queueable jobs chained off a near-limit transaction

A queueable job runs in its own transaction with its own 50,000-row budget. Chaining queueables is a useful pattern for splitting large work into transactionally separate chunks. Each link has the full budget.

public class TierWorker implements Queueable {
    private Integer offset;
    public TierWorker(Integer offset) { this.offset = offset; }
    public void execute(QueueableContext qc) {
        List<Account> chunk = [
            SELECT Id FROM Account
            ORDER BY Id
            LIMIT 1000 OFFSET :offset
        ];
        // process
        if (chunk.size() == 1000) {
            System.enqueueJob(new TierWorker(offset + 1000));
        }
    }
}

Production caps queueable chains at 5 by default, so this pattern works for jobs that fit in a handful of chunks. Larger workloads belong in Batch Apex.

Test patterns

A test that proves the batch handles a large data volume:

@IsTest
static void batchProcessesLargeAccountSet() {
    List<Account> accounts = new List<Account>();
    for (Integer i = 0; i < 200; i++) {
        accounts.add(new Account(Name = 'Test ' + i));
    }
    insert accounts;

    List<Opportunity> opps = new List<Opportunity>();
    for (Account a : accounts) {
        for (Integer j = 0; j < 5; j++) {
            opps.add(new Opportunity(
                Name = a.Name + ' Opp ' + j,
                AccountId = a.Id,
                StageName = 'Closed Won',
                CloseDate = Date.today(),
                Amount = 5000
            ));
        }
    }
    insert opps;

    Test.startTest();
    Database.executeBatch(new RevenueTierBatch(), 200);
    Test.stopTest();

    Integer goldCount = [SELECT COUNT() FROM Account WHERE Revenue_Tier__c = 'Silver'];
    System.assertEquals(200, goldCount, 'All test accounts should be Silver tier');
}

A larger test that uses Test.Loader or DataFactory utilities can exercise volumes closer to the actual production data, validating the limit behavior in CI.

Diagnosing in production

When the limit fires:

1. Note the operation that threw. Was it a trigger, a batch, anonymous Apex, a flow?
2. Identify which query in that operation is responsible.
3. Determine the matching row count for that query (run a SELECT COUNT() against the same WHERE clause).
4. Choose a fix: aggregate, batchify, or restrict the WHERE clause.
5. Add a regression test using the volume that originally tripped the limit.

For triggers, the fix often involves moving the work to a batch. For batches, the fix often involves restructuring the locator so the workload is properly chunked. For one-off scripts, adding a date or ownership filter usually suffices.

Defensive habits

Avoid querying inside a loop. Aggregate at the database. Bulkify by collecting ids first and querying once with IN :idSet.

Treat SOQL row counts as a budget. A trigger handler that may run on 200-record bulk loads should plan for at most 250 rows per query, not "however many records happen to relate".

Use the Debug Log governor-limit panel to inspect cumulative SOQL rows after every test execution. Patterns of high cumulative counts indicate code that will fail when data volume grows.

When designing a feature, ask "what happens when this object has a million rows?" up front. Anything that scans the full set without pagination is technical debt that will mature into an incident.

Selectivity and the indexed-field rule

The platform's SOQL optimizer is most effective when the WHERE clause filters on an indexed field with a selective predicate. The rules for selectivity vary by field type.

Standard fields like Id, Name, CreatedDate, LastModifiedDate, OwnerId, and RecordTypeId are indexed automatically. Custom fields marked as External Id, Unique, or part of a composite index are also indexed. Other custom fields require admin action to add an index (a support case in production orgs, a Custom Index in Sandbox).

A query against an indexed field is considered selective when it matches less than 10 percent of the table for standard objects, or less than 5 percent for custom objects. A WHERE CreatedDate >= LAST_N_DAYS:7 filter that matches 50,000 of a 500,000-row table is selective; the optimizer uses the index.

A query against an indexed field that matches more than the threshold is not selective; the optimizer may scan the full table even though an index exists. Adding additional filters narrows the result set and can flip the query back to selective.

When in doubt, use the Query Plan tool in the Developer Console (Query Editor → Query Plan button). The tool shows the cost of the query and whether it uses an index. A high-cost query is a candidate for rewrite.

Skinny tables and big-data patterns

For orgs with hundreds of thousands or millions of records on a single object, standard SOQL is often too slow regardless of selectivity. Salesforce offers two patterns for these cases.

The first is skinny tables, an admin feature that creates a parallel copy of an object's frequently queried fields. Queries against the skinny table run faster than against the main object. Skinny tables are enabled through a Salesforce Support case.

The second is Big Object queries with Async SOQL. Big Objects are designed for billions of rows and are queried asynchronously, returning results to a target object that you query normally. The pattern is useful for archival data and audit history.

For most orgs, neither pattern is needed. The standard SOQL optimizer plus disciplined query design handles the vast majority of workloads up to several million rows per object.

Quick recovery checklist

1. Identify the offending query from the stack trace.
2. Confirm the matched-row count for that query in production.
3. Pick the right fix: aggregate, batch, or selectivity.
4. Add a regression test with realistic volume.
5. Deploy and verify the job completes.

Limit incidents reveal capacity ceilings in the codebase. Each one is an opportunity to harden a class for the org's next year of growth.

Further reading from Salesforce

• Apex Developer Guide: Execution Governors and Limits
• Apex Developer Guide: Using Batch Apex
• Apex Developer Guide: SOQL Query Performance
• Architect: Large Data Volumes
• Trailhead: Asynchronous Apex