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.

3 Answers 3

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
    Commented Oct 24, 2016 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
    Commented Oct 24, 2016 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
    Commented Oct 24, 2016 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
    Commented Oct 24, 2016 at 0:42
0

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.

3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .