2

I have two custom object, Master__c and Detail__c. There is lookup relationship on Detail__c to Master__c ( since some Detail__c record may not have a master ).

To calculate total count field on master I use Aggregate Query.

List<AggregateResult> aggregateResults = [
        SELECT Count(Id), Master__c
        FROM Detail__c
        WHERE Master__c IN :masters
        GROUP BY Master__c
];

Then I log results list and its size in debug statement and iterate over it populating values on Master records and receive

"System.LimitException: Too many query rows: 50001"

if I insert 7500 children records.

However, when I change my code to directly iterate over SOQL query results without having or debugging aggregateResults variable like this

for ( AggregateResult ar: [
        SELECT Count(Id), Master__c
        FROM Detail__c
        WHERE Master__c IN :masters
        GROUP BY Master__c
    ] ) {
}

that error doesn't happen.

Can anyone explain why?

Is this somehow related to Heisenberg's uncertainty principle - that I can't debug the variable ( determine the value of the variable ) and make it work without a limit exception at the same time?

3
  • In the first form the full set of results is added to the list but in the second form only 200 records at a time are cached. I think I have seen (apparently) inconsistent enforcement of governor limits i.e. queries I thought would hit a limit but that did not: exactly where in the processing pipeline the governor limits are applied is not documented. Do you have > 50,000 detail objects in this case?
    – Keith C
    Commented Nov 23, 2015 at 22:50
  • @Keith C: No, I have clearly mentioned that I have 7500 detail object records.
    – Patlatus
    Commented Nov 24, 2015 at 8:28
  • In the first case the same records are counted many times against the query rows limit, in the second case only once
    – Patlatus
    Commented Nov 24, 2015 at 8:29

1 Answer 1

4

Check out the documentation on SOQL For Loops.

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.

Basically it doesn't try to hold on to all of them in the heap at one time and because of that, it is less limits intensive.

5
  • I'm unclear how less heap use could impact the query row use, can you possibly elaborate a bit more? Commented Nov 23, 2015 at 23:39
  • That part is less clear...but it seems like maybe only a single chunk is counted against the limit. @KeithC also touches on it in his comments.
    – Adrian Larson
    Commented Nov 23, 2015 at 23:49
  • I am aware of this statement in the documentation, but it says only about heap size and not about query rows. I mean Limits.getHeapSize() and Limits.getQueryRows() are two different separate methods which show usage of two different Governor Limits. I don't face heap size limit exception but query rows exception. I agree it looks like in the first case each chunk is counted multiple times against the limit while in the second case a single chunk is counted once. Still I have troubles understanding why this happens.
    – Patlatus
    Commented Nov 24, 2015 at 8:33
  • @Patlatus It does not seem like they want to broadcast too loudly what is going on under the hood when they relax limits like this.
    – Adrian Larson
    Commented Nov 24, 2015 at 18:07
  • @Adrian Larson yes, I agree... I hoped there is someone here who understands better what is going on under the hood :)
    – Patlatus
    Commented Nov 24, 2015 at 18:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .