6

I have a SOQL for loop wherein the query sometimes returns more rows than the limit of 50,000. The code block executes once per record returned by the query, and in that code block is an if statement that breaks the loop if a certain value reaches 20,000. The log shows that the loop was broken during the 10,000th iteration, as I expected, but it also said that the query returned 50,001 rows.

I thought SOQL for loops query in batches of 200. Shouldn't it not have returned the rest of the rows yet? Shouldn't it only count the 10,000 (or 10,200 at the worst) against my limit?

6

Edit: Actually, the "Rows" line will show one more than the remaining limit available for the current transaction, but the actual value counted against you will be 200 more than when you break.

Here's demonstration code:

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

Shuffling through the debug logs, I note that:

10:58:41:200 SOQL_EXECUTE_END [2]|Rows:50001
10:58:45:389 SOQL_EXECUTE_END [9]|Rows:29801

However, when I look at the cumulative usage section, I see that:

10:58:38:000 LIMIT_USAGE_FOR_NS   Number of query rows: 40400 out of 50000 ******* CLOSE TO LIMIT

I actually wasn't aware that this was a feature, so it's good to know.

  • Thanks for the quick, clear answer. Unfortunately, it's not what I was hoping to hear. I wish there were a good way to query huge groups of records at once. – Neo Jan 5 '16 at 17:57
  • @Neo: you can use batch Apex... – mkorman Jan 5 '16 at 17:58
  • @Neo So... Actually, I found out I was wrong :( – sfdcfox Jan 5 '16 at 17:59
  • @Neo Please take a look at the now completely revised answer. – sfdcfox Jan 5 '16 at 18:02
  • 1
    @Neo Yes-- the List method of the for-each SOQL query loop uses chunks of 200 records, while the non-list method tries to suck in all the records at once. – sfdcfox Jan 5 '16 at 19:25

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.