Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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

share|improve this question
    
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 '13 at 15:51
add comment

3 Answers

up vote 8 down vote accepted

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.

share|improve this answer
    
Can't you use @readonly in vf pages as well? And I thought there was still a row limit of one million. –  Greg Grinberg Jan 19 '13 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/… –  Bob Buzzard Jan 20 '13 at 4:46
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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