For Loop by SOQL (hidden feature?)

Question

After intensively using SOQL in For Loop I have found some interesting behavior which I neither can explain nor understand. Official SF documentation also does not have any notes regarding this.

For the below examples I will use different predefined values.

---

Aggregate Functions

Normally if you run aggregate function for huge amount of data, which simply Counts records, outside of For Loop you get System.LimitException: Too many query rows: 50001, but in For Loop you will get the result

1. 3 Accounts and 103001 Contacts

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (AggregateResult ar : [
     SELECT
       Count(Amount__c) counter
     FROM Contact
   ]) {
     numberOfRecords += Integer.valueOf(ar.get('counter'));
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   05:05:35.679 (5679523466)|USER_DEBUG|[84]|DEBUG|Number Of Records => 103001
   05:05:35.679 (5679570318)|USER_DEBUG|[85]|DEBUG|Query Rows => 1 out of 50000

---

Inner Select in SOQL

If you are using inner select in SOQL outside of For Loop for you also can get this exception System.LimitException: Too many query rows: 50001 , but in For Loop not in all cases.

1. 3 Accounts and 103001 Contacts. 1st Account in iteration has 51000 Contacts, 2nd Account - 53000 Contacts and 3rd Account - 1 Contact

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
           )
       FROM Account
       ORDER BY CreatedDate ASC
   ]) {
       for (Contact cont : acc.Contacts) {
           numberOfRecords++;
       }
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   System.LimitException: Too many query rows: 50001

---

2. 3 Accounts and 103001 Contacts. 1st Account has 1 Contact, 2nd Account - 52000 Contacts, 3rd Account - 51000 Contacts

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
           )
       FROM Account
       ORDER BY CreatedDate DESC
   ]) {
       for (Contact cont : acc.Contacts) {
           numberOfRecords++;
       }
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   05:54:34.902 (3902283001)|USER_DEBUG|[137]|DEBUG|Number Of Records => 103001
   05:54:34.902 (3902359709)|USER_DEBUG|[138]|DEBUG|Query Rows => 3007 out of 50000

---

3. 3 Accounts and 103001 Contacts. 1st Account has 1 Contact, 2nd Account - 51000 Contacts, 3rd Account - 51000 Contacts.

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
               LIMIT 51000
           )
       FROM Account
       ORDER BY CreatedDate DESC
   ]) {
       for (Contact cont : acc.Contacts) {
           numberOfRecords++;
       }
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   06:12:51.609 (11609593541)|USER_DEBUG|[138]|DEBUG|Number Of Records => 102001
   06:12:51.609 (11609660978)|USER_DEBUG|[139]|DEBUG|Query Rows => 2007 out of 50000

---

4. 3 Accounts and 103001 Contacts. 1st Account has 1 Contact, 2nd Account - 50000 Contacts, 3rd Account - 50000 Contacts.

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
               LIMIT 50000
           )
       FROM Account
       ORDER BY CreatedDate DESC
   ]) {
       for (Contact cont : acc.Contacts) {
           numberOfRecords++;
       }
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Results:

   06:09:33.259 (12259882425)|USER_DEBUG|[138]|DEBUG|Number Of Records => 100001 06:09:33.259 (12259944075)|USER_DEBUG|[139]|DEBUG|Query Rows => 7 out of 50000

---

5. 3 Accounts and 103001 Contacts. 1st Account has 1 Contact, 2nd Account - 30000 Contacts, 3rd Account - 30000 Contacts.

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
               LIMIT 30000
           )
       FROM Account
       ORDER BY CreatedDate DESC
   ]) {
       for (Contact cont : acc.Contacts) {
           numberOfRecords++;
       }
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   06:02:29.849 (1849947431)|USER_DEBUG|[138]|DEBUG|Number Of Records => 60001
   06:02:29.849 (1850023376)|USER_DEBUG|[139]|DEBUG|Query Rows => 3 out of 50000

---

6. 3 Accounts and 104000 Contacts. 1st Account has 1000 Contacts, 2nd Account - 40000 Contacts, 3rd Account - 40000 Contacts.

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
               LIMIT 40000
           )
       FROM Account
       ORDER BY CreatedDate DESC
   ]) {
       for (Contact cont : acc.Contacts) {
           numberOfRecords++;
       }
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   06:25:27.430 (2430919804)|USER_DEBUG|[138]|DEBUG|Number Of Records => 81000
   06:25:27.430 (2430990408)|USER_DEBUG|[139]|DEBUG|Query Rows => 804 out of 50000

---

7. 3 Accounts and 104000 Contacts. All Accounts have 200 Contacts. Use size() method to get number of records.

   Integer srartQueryRows = Limits.getQueryRows();
   Integer numberOfRecords = 0;
   for (Account acc : [
       SELECT
           Id,
           (
               SELECT
                   Id
               FROM Contacts
               LIMIT 200
           )
       FROM Account
       ORDER BY CreatedDate DESC
   ]) {
       numberOfRecords += acc.Contacts.size();
   }
   Integer endQueryRows = Limits.getQueryRows();
   System.debug('Number Of Records => ' + numberOfRecords);
   System.debug('Query Rows => ' + (endQueryRows - srartQueryRows) + ' out of ' + Limits.getLimitQueryRows());
   

   Result:

   System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

---

Conclusion

I see a certain pattern here.

1. Aggregate function does not count all query rows, only number of aggregated results.

2. Inner select in SOQL is not included in query rows counter. SF counts only parent records and child records for the 1st parent loop (and only after chunk of 200 records is processed - that's why we see that if 1st iteration has 1000 child records then in result we will have 800 query rows). Also if inner select has more than 50000 all child records are counted after 50000.

Are my conclusions above correct? Is this behavior documented?

Answer 1

SOQL For Loops are themselves a documented feature, but increasing your query row governor limits is not very clearly listed as a benefit.

SOQL For Loops Versus Standard SOQL Queries

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records, SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API. Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

Note that queries including an aggregate function don't support queryMore. A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop.

A couple things to note about the above:

• SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API
  • I guess this difference may be what allows you to retrieve a higher number of results.
• A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop.
  • If you are just aggregating COUNT(Id) it probably won't matter, but be careful trying to use this approach for more complicated counting mechanisms.

I'm not sure why the listed number of query rows consumed is lower. Could be a bug, could be a feature.

Answer 2

Sort of documented here:

Working with Very Large SOQL Queries

Your SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore.

For example, if the results are too large, this syntax causes a runtime exception:

Account[] accts = [SELECT Id FROM Account];

Instead, use a SOQL query for loop as in one of the following examples:

// Use this format if you are not executing DML statements 
// within the for loop
for (Account a : [SELECT Id, Name FROM Account 
                  WHERE Name LIKE 'Acme%']) {
    // Your code without DML statements here
}
// Use this format for efficiency if you are executing DML statements 
// within the for loop
for (List<Account> accts : [SELECT Id, Name FROM Account
                            WHERE Name LIKE 'Acme%']) {
    // Your code here
    update accts;
}