4

I looked at this, but clearly there appears to be a big difference between the two. Can someone please explain the behavior and/or suggest a workaround?

I already posted this comment in this thread, but now feel like it deserves its own thread. If not, please advise.

String soql = 'SELECT State__c FROM Custom_Object__c WHERE State__c != \'\' GROUP BY State__c';
    List<AggregateResult> results = new List<AggregateResult>();
    results = Database.query(soql);

For above, LIMIT_USAGE_FOR_NS Number of query rows: 34095 out of 50000 ******* CLOSE TO LIMIT

Set<String> stateSet = new Set<String>();
for( AggregateResult ar : [SELECT State__c FROM Custom_Object__c WHERE State__c != '' GROUP BY State__c]){     
    stateSet.add(String.valueOf(ar.get('State__c')));
}

For above, LIMIT_USAGE_FOR_NS Number of query rows: 114 out of 50000

Why so? Ideally I expect 114 out of 50000 both ways.

  • 1
    my guess would be that The second example that you have provided uses SOQL For Loop and SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API and thus the difference in limits unless there is some other reason. – Rajiv Bhatt Apr 22 '15 at 17:45
  • Yes, it likely has to do with your for loop and the way it batches the query. You're not exactly comparing apples to apples. In your string example you are caching the whole result set. – Adrian Larson Apr 22 '15 at 18:28
  • You should probably now close this question. – Adrian Larson Apr 22 '15 at 20:28
  • How do I 'close' a question? Delete it or answer it myself? – Lightning Evangelist Apr 22 '15 at 20:33
  • @kal That's a good question that might merit asking on the meta site. I would think just delete it but I'm not sure. – Adrian Larson Apr 22 '15 at 23:24
3

You may want to try batching through your dynamic query results as well:

for (AggregateResult result : (List<AggregateResult>)Database.query(soql))
{
    stateSet.add(String.valueOf(result.get('State__c')));
}

I am not 100% sure you have to cast the result, but I'm pretty sure if you don't you will be looping over a List<SObject> instead and will get a compile error. (UPDATE: casting is not necessary.)

As for the cause, this documentation sheds a little light on why. See the section titled "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.

The above seems to suggest that only one "batch" of records is maintained on the heap at any time, and potentially only the last batch would count against the limit you reference in your question. I could use some clarification there myself.

Unfortunately, said document also raises some new questions in the next paragraph:

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.

I'm guessing the number that counts against that is 114, so you're pretty well under the limit, but it could be an issue with your approach if you change your grouping.

  • Thank you, I couldn't mark a comment as an answer so I almost answered the question myself. Now I could mark yours! Appreciate your time. BTW... casting was not necessary. – Lightning Evangelist Apr 22 '15 at 19:43
  • Good that this works around the problem, but can you explain why? Would be good to know what is going on. – Keith C Apr 22 '15 at 19:44
  • @KeithC Added some detail and documentation. – Adrian Larson Apr 22 '15 at 19:53
  • @AdrianLarson Thanks for the extra detail. Agreed that if the limit was heap space the "SOQL for loop" would do much better but why the query row limit is affected puzzles me. – Keith C Apr 22 '15 at 20:54
  • @KeithC I agree it is somewhat puzzling. I think it has something to do with trying to cache the entire set of results instead of batching smaller chunks through memory. – Adrian Larson Apr 22 '15 at 20:58
1

The below test passes in my dev org illustrating that there is no difference in the query row limit consumption between dynamic and static SOQL.

I suggest you run the same test in your org with your query replacing the one I used and see what result you get: your problem may be coming from some other place in your code.

@isTest
private class ATest {

    @isTest(SeeAllData=true)
    static void staticVsDynamic() {

        Integer r1 = Limits.getQueryRows();
        AggregateResult[] dynamicResults = Database.query('select FirstName from Contact group by FirstName');
        Integer r2 = Limits.getQueryRows();
        AggregateResult[] staticResults = [select FirstName from Contact group by FirstName];
        Integer r3 = Limits.getQueryRows();

        Integer dynamicQueryRows = r2 - r1;
        Integer staticQueryRows = r3 - r2;

        System.assertEquals(dynamicResults.size(), staticResults.size());
        System.assertEquals(dynamicQueryRows, staticQueryRows);

        // Check there are more than a few rows
        System.assert(dynamicResults.size() > 20);
        System.assert(dynamicQueryRows > 20);
    }
}

PS

Is this simply a quirk/feature/design choice/anomaly/bug in how the query row governor limit is counted?

This code:

@isTest
private class ATest {

    @isTest(SeeAllData=true)
    static void staticVsDynamic() {

        Integer ungrouped = [select count() from Contact];

        Integer r1 = Limits.getQueryRows();
        AggregateResult[] dynamicResults = Database.query('select FirstName from Contact group by FirstName');
        Integer r2 = Limits.getQueryRows();
        AggregateResult[] staticResults = [select FirstName from Contact group by FirstName];
        Integer r3 = Limits.getQueryRows();
        for (AggregateResult dynamicResult : Database.query('select FirstName from Contact group by FirstName')) {
        }
        Integer r4 = Limits.getQueryRows();
        for (AggregateResult staticResult : [select FirstName from Contact group by FirstName]) {
        }
        Integer r5 = Limits.getQueryRows();

        System.assertEquals('', 'ungrouped ' + ungrouped + ' grouped ' + dynamicResults.size()
                + ' static for ' + (r5 - r4) + ' dynamic for ' + (r4 - r3) + ' static list ' + (r3 - r2) + ' dynamic list ' + (r2 - r1));
    }
}

produces these numbers:

ungrouped 105 grouped 47 static for 47 dynamic for 47 static list 105 dynamic list 105

that confirm that dynamic/static SOQL makes no difference. But assigning directly to a list consumes the query rows I would have expected (all the database rows scanned to produce the aggregation) whereas accessing via the for loop consumes only the query rows returned which I would not have expected.

  • 1
    Keith - nice to see you back in SFSE – cropredy Apr 22 '15 at 18:39
  • @crop1645 - appreciated. – Keith C Apr 22 '15 at 18:45
  • Actually I am trying this in 'Execute Anonymous' window, so there isn't anything else. And, I just tried your code and agree that the query rows returned for dynamic and static ways is the same. Like Rajiv and Adrian pointed out above, this clearly appears to be because of the FOR loop in the second approach in OP (which you don't have in your snippet). BUT in any case there must only be 114 query rows. Is it possible to implement the static option using a FOR loop and a STRING query? What I mean is: for( AggregateResult ar : ['my_string_query']){} – Lightning Evangelist Apr 22 '15 at 18:54
  • I could be wrong, but you should be able to do for(AggregateResult ar : Database.query(q)){}. Might need to cast the type. – frontendloader Apr 22 '15 at 19:29

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.