How Aggregate Result are count against the Governor Limits?

Question

I'm hitting the Governor limits (Too many query rows: 50001) and I would like to improve my query. However is only an Aggregate Result that only brings 30 rows but it reads a lot because is against the Invoices table. I'm wondering if Aggregate results are count by the amount of rows that the query brings or against the row it reads?

Is just a simple query and I don't see anything wrong on

AggregateResult[] LastYTD_invoicevalue =[Select Sold_To__r.Class_Code__c c,Sum(Invoice_Amount__c)s from Invoice_New__c where (Effective_Date__c >=2015-07-01 and Effective_Date__c <=2016-10-23) Group By Sold_To__r.Class_Code__c Limit 1000];

I do 4 querys (MTD, Last MTD, YTD and Last YTD) like the one above and put them in 4 different MAPS and the read the 4 of them to display the results.

Why is this query is only bringing 30 rows each one am I hitting the limits?

Thanks in advance.

Answer 1

The number of queried records is what is counted against you. Even if you only get one AggregateResult row back, if that row is tallied from 50,000 records, then you'll exceed the governor limit. You'll find documentation that states that in Working with SOQL Aggregate Functions.

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.

Answer 2

The aggregate queries are restricted to governor limits and the total no of rows in a context is 50000 .If you have 4 different queries operation on 20K rows each then you will definitely hit the limit as well .

Alternative approaches would be to run each one of them in async via JSRemoting call or write a batch process to aggregate the result set in a custom objects .

If you are using only to display then you can also make page as readOnly via an attribute on apex:page which will get you 50 million rows limit and platform will use query more for same .

Another alternative I can think of is using bulk REST on visualforce page which will consume your Bulk API but you will not hit the limits and you can use PK Chunking approach to aggregate .

Answer 3

From the documentation:

Queries that include aggregate functions are subject to the limit on total number of query rows i.e 50,000. All aggregate functions other than COUNT() or COUNT(fieldname) include each row used by the aggregation as a query row for the purposes of limit tracking.

For COUNT() or COUNT(fieldname) queries, limits are counted as one query row, unless the query contains a GROUP BY clause, in which case one query row per grouping is consumed.