2

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];

enter image description here 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.

4

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.

3
  • Thanks @sfdcfox. This solved my question, However I would like to ask you what happened if that aggregate result queries 21000 rows. That's less than the 50000 limit but because I'm doing 4 aggregate results that's why I'm hitting the limits. If I move the Aggregate results to different methods will that help? I know for sure that separate they won't hit the limits.
    – MANUELAN00
    Oct 24 '16 at 0:28
  • @MANUELAN00 The 50,000 row limit applies to all queries in a single transaction, no matter where they're called from. If you need more, consider using @ReadOnly or apex:page's readOnly attribute.
    – sfdcfox
    Oct 24 '16 at 0:32
  • Beautiful, <apex: page readonly="true"> solved my problem. This is just for reporting purposes so I don't need to perform any DML operation. Thanks a lot @sfdcfox...!!!
    – MANUELAN00
    Oct 24 '16 at 0:39
3

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 .

1
  • Thanks a lot for your answer. Certainly make the page as Readonly solved my problem...!!!
    – MANUELAN00
    Oct 24 '16 at 0:42

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.