[SalesForce] How Aggregate Result are count against the Governor Limits

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.

Best Answer

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.

Related Solutions

[SalesForce] How do SOQL date literals behave with fiscal years that differ from one year to the next

Behavior

Here is my interpretation based on the evidence I have gathered (detailed below):

The LAST_N_FISCAL_QUARTERS Date Literal does respect Custom Fiscal Quarter definitions. Quarters that come before your first defined Custom Fiscal Year are each treated as a Standard Fiscal Quarter. Quarters that come after your last defined Custom Fiscal Year, however, are ignored entirely. So if your last defined Fiscal Year is in the past, LAST_N_FISCAL_QUARTERS:1 will encompass the last defined Quarter.

In light of this finding, it would be prudent to make sure your Custom Fiscal Years are always defined into the future.

Data

I created MyObject__c records with the following Date__c values (expected Quarter in parentheses):

2012 (4)
2012-02-29, 2012-06-30, 2012-09-16, 2012-12-04
(2012-Q1)   (2012-Q2)   (2012-Q3)   (2012-Q4)

2013 (6)
2013-01-03, 2013-05-23, 2013-08-06, 2013-10-01, 2013-12-30, 2013-12-30
(2013-T1)   (2013-T2)   (2013-T2)   (2013-T3)   (2013-T3)   (2013-T3)

2014 (6)
2014-03-20, 2014-04-29, 2014-06-01, 2014-08-06, 2014-10-31, 2014-12-11
(2014-T1)   (2014-T1)   (2013-T2)   (2013-T2)   (2013-T3)   (2013-T3)

2015 (3)
2015-02-26, 2015-05-05, 2015-08-02
(2015-Q1)   (2015-Q2)   (2015-Q3)

I would then query for the data as follows:

SELECT count() FROM MyObject__c WHERE Date__c = LAST_N_FISCAL_QUARTERS:12

Preliminary Investigation

First, I set up trimesters just for 2013 and 2014.*

Trimesters setup:
2013-T1 (2013-01-01 through 2013-04-30)
2013-T2 (2013-05-01 through 2013-08-31)
2013-T3 (2013-09-01 through 2013-12-31)
2014-T1 (2014-01-01 through 2014-04-30)
2014-T2 (2014-05-01 through 2014-08-31)
2014-T3 (2014-09-01 through 2014-12-31)

Records for each value of LAST_N_FISCAL_QUARTERS:
12 - 16    11 - 16    10 - 16
09 - 15    08 - 14    07 - 13
06 - 12    05 - 11    04 -  9
03 -  6    02 -  4    01 -  2

Which looks like it matches up with:
2011-Q3 - 0    2011-Q4 - 0
2012-Q1 - 1    2012-Q2 - 1    2012-Q3 - 1    2012-Q4 - 1
2013-T1 - 1    2013-T2 - 2    2013-T3 - 3
2014-T1 - 2    2014-T2 - 2    2014-T3 - 2

Sure seems like LAST_N_FISCAL_QUARTERS:1 matches up with 2014-T3.

Follow Up

After that I added 2015.

Quarters setup:
2015-Q1 (2015-01-01 through 2015-03-31)
2015-Q2 (2015-04-01 through 2015-06-30)
2015-Q3 (2015-07-01 through 2015-09-30)
2015-Q4 (2015-10-01 through 2015-12-31)

Records for each value of LAST_N_FISCAL_QUARTERS:
15 - 19    14 - 19    13 - 18
12 - 17    11 - 16    10 - 15
09 - 14    08 - 12    07 -  9
06 -  7    05 -  5    04 -  3
03 -  2    02 -  1    01 -  0

Which looks like it matches up with:
2011-Q4 - 0
2012-Q1 - 1    2012-Q2 - 1    2012-Q3 - 1    2012-Q4 - 1
2013-T1 - 1    2013-T2 - 2    2013-T3 - 3
2014-T1 - 2    2014-T2 - 2    2014-T3 - 2
2015-Q1 - 1    2015-Q2 - 1    2015-Q3 - 1    2015-Q4 - 0

And finally 2016.

Quarters setup:
2016-Q1 (2016-01-01 through 2016-03-31)
2016-Q2 (2016-04-01 through 2016-06-30)
2016-Q3 (2016-07-01 through 2016-09-30)
2016-Q4 (2016-10-01 through 2016-12-31)

Records for each value of LAST_N_FISCAL_QUARTERS:
18 - 19    17 - 19    16 - 19
15 - 18    14 - 17    13 - 16
12 - 15    11 - 14    10 - 12
09 -  9    08 -  7    07 -  5
06 -  3    05 -  2    04 -  1
03 -  0    02 -  0    01 -  0

Which looks like it matches up with:
2011-Q3 - 0    2011-Q4 - 0
2012-Q1 - 1    2012-Q2 - 1    2012-Q3 - 1    2012-Q4 - 1
2013-T1 - 1    2013-T2 - 2    2013-T3 - 3
2014-T1 - 2    2014-T2 - 2    2014-T3 - 2
2015-Q1 - 1    2015-Q2 - 1    2015-Q3 - 1    2015-Q4 - 0
2015-Q1 - 0    2015-Q2 - 0

Looks like THIS_FISCAL_QUARTER is one quarter after LAST_N_FISCAL_MONTHS:1, in case that wasn't obvious.

* This post prompted me to lay out in detail how to set up trimesters in the first place. How Do I Set Up Fiscal Trimesters, Anyway?

[SalesForce] Avoid hitting SOQL Limits but know that I was about to hit them

The way I see it, you have two options here:

Display your message whenever you end up with 0 remaining query rows available.
Write a batch that will store the number of records that object contains in a Hierarchy Custom Setting.

Either way, it will probably simplify repeated references to add a helper method:

public static Integer getRemainingQueryRows()
{
    return Limits.getLimitQueryRows() - Limits.getQueryRows();
}

For Option 1 your solution would simply look like:

someList = [
    SELECT convertCurrency(Price__c) FROM MyObject__c LIMIT :getRemainingQueryRows()
];
isQueryLimitConsumed = (0 == getRemainingQueryRows());

For Option 2 your current implementation would change to look more like:

isQueryComplete = MySetting__c.MyObjectCount__c > getRemainingQueryRows();
someList = [/*query*/];

Best Answer

Related Solutions

[SalesForce] How do SOQL date literals behave with fiscal years that differ from one year to the next

[SalesForce] Avoid hitting SOQL Limits but know that I was about to hit them

Related Topic