I have the code below:

Integer counter = 0;
for(Account[] records: [SELECT Id FROM Account]) {
    counter += records.size();

While executing this anonymous code, I am getting an error:

Error on line 36, column 1: System.LimitException: Too many query rows: 50001
Class.Database.QueryLocatorChunkIterator.next: line 36, column 1
External entry point
AnonymousBlock: line 11, column 1
23:03:00.757 (3757182588)|SOQL_EXECUTE_END|[11]|Rows:50001

I checked the documentation, and I found that SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API.

There are 9 million records in Account object, so how can I use SOQL for-loop to iterate over all the accounts? Or am I missing something?

And the most important question: is it possible to query more than 50000 records in a synchronous transaction?

  • 1
    The most you can query via any approach in apex code is 50000 records PER TRANSACTION. See developer.salesforce.com/docs/atlas.en-us.apexcode.meta/…
    – Phil W
    Apr 1 at 6:23
  • I was asked a question, and I knew that it's not possible to query more than 50000 records in a synchronous transaction. But he said that using querymore, it's possible. And I was like seriously?
    – snl
    Apr 1 at 6:26
  • Not in a single transaction. You would have to have this split into multiple transactions from an external system (e.g. the browser) or using a sequence of async transactions. On the latter, you can process up to 50000000 (50 million) records in a batch, though that's not doing it via a single transaction of course. Even though COUNT queries changed, this still faces limits - typically CPU - if there are WHERE conditions that must be at least partially handled in memory.
    – Phil W
    Apr 1 at 6:54

The SOQL for loop is focused on avoiding heap space issues, as that page says:

Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

This is important when you are querying a lot of large records.

The SOQL for loop structure does not allow you to circumvent the hard limit of 50000 records queried (across all SOSL/SOQL) in a transaction, as covered in the Apex Governor Limits documentation.

Since all you want to do here is perform a COUNT, you could instead consider using the COUNT query. This was updated back in Summer '18 to reduce the number of query row limits consumed. Even so, you can find that COUNT queries fail with timeouts or because they are not selective enough.

If you have a hard requirement to count the number of Account records in your system, you could hold the count in a separate object and look to maintain this count via logic in the Account trigger (incrementing on insert/undelete, decrementing on delete). The initial calculation could be handled via a Batch, since these are able to process up to 50000000 rows (and your count is less than that limit at the moment).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.