8

This question already has an answer here:

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


  1. 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


  1. 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


  1. 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


  1. 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


  1. 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


  1. 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?

marked as duplicate by Santanu Boral, Martin Lezer, Himanshu, Boris Bachovski, Raul Jul 10 '17 at 6:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • It could be if I didn't create many cases. I still don't understand some of them and for me it seems like SF bug than a feature. – Tehn-r Jul 8 '17 at 15:41
  • @adrian-larson Why is this question marked as duplicate? If even in mentioned salesforce.stackexchange.com/questions/22631/… topic not enough information to get it as truth. Also, I created specific cases which were not explained either way. – Tehn-r Jul 10 '17 at 8:15
  • I'm not sure why you think I would know. I am not one of those who voted to close. I agree with you that it's not a duplicate. – Adrian Larson Jul 10 '17 at 14:33
  • @adrian-larson, oh... I see now. Sorry, I was thinking that you, as moderator, closed it. – Tehn-r Jul 10 '17 at 15:05
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.

  • Yup, I did a test with aggregate function using GROUP BY statement that should return 2000+ groups. Got this run-time exception . I am concerned about Inner Select in SOQL cases and all their strange results - seems more like a SF bug – Tehn-r Jul 8 '17 at 15:58
  • 1
    I don't see any total size limitations in the SOAP API Limits page, I wonder if the API is able to get all the records without hitting a limit and for these queries the query row count is based on calls to query and queryMore, not the actual number of rows retrieved. – IllusiveBrian Jul 8 '17 at 16:33
1

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;
}
  • As I wrote, I read all found documentation and even this quote does not reply to some cases. All your examples will fail with standard System.LimitException: Too many query rows: 50001, but in some cases (which I described here so anyone can copy-paste and check) queries pass this limit. – Tehn-r Jul 8 '17 at 15:47
  • @Tehn-r - "My" Examples are from the docs lol. Just posting for possible information. Too much for a comment – Eric Jul 8 '17 at 15:48
  • Sorry, I didn't mean to be rude. I know that these examples from the documentation. Just have used wrong word. – Tehn-r Jul 8 '17 at 15:52
  • @Tehn-r - Not offense taken. Just making sure you knew they were from the docs and not me. You question is interesting and looking forward to some solid answers if there are any – Eric Jul 8 '17 at 15:53

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