13

I'm trying to use aggregate queries to save work and script statements. The object has a field for each month representing energy usage for that month for a building.

In situations where a building has no usage history (we don't have it, or it's brand new), I want to take the average of all the buildings of that class in that city and use that as a default assumption.

It works great, except for the very large cities where I'm hitting a SOQL limit.

SELECT average(january__c), ... FROM annual_usage__c WHERE ... GROUP BY city__c LIMIT 50000 

Doesn't seem to limit it in any way...it'll still come back with more than 50000.

Is there a safe way to deal with the 50000 row limit on aggregate queries? I haven't even been able to catch this error successfully

1
  • As other answers have stated - this won't work the way you want ;) Put something more selective in the query (WHERE January__c != null AND City__c = :city?) or consider preparing data upfront... Some batch class that would aggregate these results in a helper object. Or even often overlooked - Analytic Snapshot?
    – eyescream
    Jan 19, 2013 at 15:51

5 Answers 5

16

I think that the issue here is that an aggregate query is considered to have accessed every record included in the aggregation. Thus the 50000 row limit isn't the number of records returned by the query, its the number of records 'touched'. As you have a clause of 'LIMIT 50000', you are asking for a maximum of 50000 aggregates to be returned by the query and if any of these "touches" more than one record, you will exceed the limit.

The only exception to this is if your class is annotated @readonly, in which case there are no record limits on queries (although the others still apply, (e.g. heap space). However, this can only be used in scheduled classes or webservices, so may not be useful in your situation.

If you can't use @readonly, you'll need to set the value of the LIMIT clause to one which is low enough that all of the records considered in the aggregations is less than 50000. If you have cities that have more than 50000, then there won't be a low enough limit.

If the relationship between building and city is master detail, you could use a roll up summary field to calculate the averages, although you are limited to 10 of these fields per record.

3
  • 1
    Can't you use @readonly in vf pages as well? And I thought there was still a row limit of one million. Jan 19, 2013 at 22:28
  • 1
    There are readonly visualforce pages and the limit there is one million. The @readonly annotation applies to apex and doesn't have that restriction : salesforce.com/us/developer/docs/apexcode/Content/… Jan 20, 2013 at 4:46
  • 2
    updated because the limits changed in Summer18. Oct 18, 2018 at 19:44
4

Limit Clause will not take effect in the Aggregate Functions, you have to use the Where Clause to limit the data size in the query.

See the Note in the SOQL documentation:

Queries that include aggregate functions are subject to the same governor limits as other SOQL queries for the total number of records returned. This limit includes any records included in the aggregation, not just the number of rows returned by the query. If you encounter this limit, you should add a condition to the WHERE clause to reduce the amount of records processed by the query.

4

SOQL count query now counts as 1. So you can count all the things.

https://releasenotes.docs.salesforce.com/en-us/summer18/release-notes/rn_apex_soql_count_limits.htm

2

You can't use a LIMIT clause in a query that uses an aggregate function Ref: http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_agg_functions.htm

0

I recently encountered this issue!

Issue: In my case, Object xyz was used to group by over field xyz_1 which gave around 100 records. One day data was loaded in Object xyz exceeding its count more than 50000. Code started hitting the governor limit of 50000 rows while the query was returning just 100 records. As mentioned by Bob Buzzard in the previous answers, the aggregate query is touching 50000 records to group by which is the reason this error occurs.

Solution: I know I am replying very late. I did not find this solution anywhere and stumbled upon it while checking the salesforce docs hence answering the question.

Old query handling:

List<AggregateResult> results = [select xyz_1 from xyz group by xyz_1];

New query handling:

for (List<AggregateResult> temp: [select xyz_1 from xyz group by xyz_1]) {
    results = temp;
}

This works because for loop processes multiple batch records using internal calls to query and querymore. Explained here: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm

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.