Apex Code Development You need to sign in to do that
Don't have an account?
Dodi System.LimitException: Too many query rows: 50001 when querying into a List??
Dear Gurus,
I have a batch class which works properly for the most part. One small issue is that I am getting System.LimitException: Too many query rows: 50001
Per the docs this should not happen because I am querying everything into a list and processing off the list. Can someone explain a work around or whats wrong with the code. The error occurs on the query line.
Any help is appreciated
/*This class rolls up invoices for all accounts and calculates a monthly average based on the invoice amounts/months and years queried.
global class UpdateInvoiceForecastAverages implements Database.Batchable<sObject> {
//private final string strQuery;
global final string strQuery;
global UpdateInvoiceForecastAverages(String query) {
this.strQuery = query;
}
//Execute the query.
global database.querylocator start(Database.BatchableContext BC) {
return Database.getQueryLocator(strQuery);
}
global void execute(Database.BatchableContext BC, List<sObject> scope){
//get custom setting params
Account_Forecast_Date_Parameter_Config__c paramConfig = Account_Forecast_Date_Parameter_Config__c.getValues('UpdateInvoiceForecastAverages');
Integer startYear = Integer.valueOf(paramConfig.Start_Year__c);
Integer endYear = Integer.valueOf(paramConfig.End_Year__c);
Integer yearDiff;
//calculate year range from custom setting params
if (endYear == startYear) {
yearDiff = 1;
}
else {
yearDiff = (endyear - startyear) + 1;
}
//Maps to store Account Id and Related Invoices
Map<Id, List<ESDInvoice__c>> mapInvoiceByAccount = new Map<Id, List<ESDInvoice__c>>();
List<Account> lstAccountToUpdate = new List<Account>();
set<Id> setAccountId = new set<Id>();
//Collect Account Id's
for(sObject sobjAccount : scope){
Account objAccount = (Account)sobjAccount;
setAccountId.add(objAccount.Id);
}
//Retrieve Invoices
for (List<ESDInvoice__c> lstInvoices : [SELECT Account__c, PostPeriod__c, InvoiceTotal2__c From ESDInvoice__c e WHERE Account__c IN :setAccountId
AND PostPeriod__c != NULL AND InvoiceTotal2__c != NULL
AND InvoiceTotal2__c != 0 AND CALENDAR_YEAR(PostPeriod__c) >= :startYear AND CALENDAR_YEAR(PostPeriod__c) <= :endYear]){
for(ESDInvoice__c objInvoice : lstInvoices){
if(NULL != objInvoice.Account__c){
//if(NULL != objInvoice.Account__c){
if(!mapInvoiceByAccount.containsKey(objInvoice.Account__c))
mapInvoiceByAccount.put(objInvoice.Account__c, new List<ESDInvoice__c>());
mapInvoiceByAccount.get(objInvoice.Account__c).add(objInvoice);
}
}
}
if(mapInvoiceByAccount.size() > 0){
//Iterate through map and collect each months total
for(Id idKey : mapInvoiceByAccount.keySet()){
//Initialize variables
Decimal dJanTotal = 0; Integer iJanRecordCount = 0;
Decimal dFebTotal = 0; Integer iFebRecordCount = 0;
Decimal dMarTotal = 0; Integer iMarRecordCount = 0;
Decimal dAprTotal = 0; Integer iAprRecordCount = 0;
Decimal dMayTotal = 0; Integer iMayRecordCount = 0;
Decimal dJunTotal = 0; Integer iJunRecordCount = 0;
Decimal dJulTotal = 0; Integer iJulRecordCount = 0;
Decimal dAugTotal = 0; Integer iAugRecordCount = 0;
Decimal dSeptTotal = 0;Integer iSeptRecordCount = 0;
Decimal dOctTotal = 0; Integer iOctRecordCount = 0;
Decimal dNovTotal = 0; Integer iNovRecordCount = 0;
Decimal dDecTotal = 0; Integer iDecRecordCount = 0;
List<ESDInvoice__c> lstInvoices = mapInvoiceByAccount.get(idKey);
if(lstInvoices.size() > 0){
for(ESDInvoice__c objInvoice : lstInvoices){
//If Jan
if(objInvoice.PostPeriod__c.month() == 1){
dJanTotal = dJanTotal + objInvoice.InvoiceTotal2__c;
iJanRecordCount++;
}
//If Feb
if(objInvoice.PostPeriod__c.month() == 2){
dFebTotal = dFebTotal + objInvoice.InvoiceTotal2__c;
iFebRecordCount++;
}
//If Mar
if(objInvoice.PostPeriod__c.month() == 3){
dMarTotal = dMarTotal + objInvoice.InvoiceTotal2__c;
iMarRecordCount++;
}
//If Apr
if(objInvoice.PostPeriod__c.month() == 4){
dAprTotal = dAprTotal + objInvoice.InvoiceTotal2__c;
iAprRecordCount++;
}
//If May
if(objInvoice.PostPeriod__c.month() == 5){
dMayTotal = dMayTotal + objInvoice.InvoiceTotal2__c;
iMayRecordCount++;
}
//If June
if(objInvoice.PostPeriod__c.month() == 6){
dJunTotal = dJunTotal + objInvoice.InvoiceTotal2__c;
iJunRecordCount++;
}
//If July
if(objInvoice.PostPeriod__c.month() == 7){
dJulTotal = dJulTotal + objInvoice.InvoiceTotal2__c;
iJulRecordCount++;
}
//If Aug
if(objInvoice.PostPeriod__c.month() == 8){
dAugTotal = dAugTotal + objInvoice.InvoiceTotal2__c;
iAugRecordCount++;
}
//If Sept
if(objInvoice.PostPeriod__c.month() == 9){
dSeptTotal = dSeptTotal + objInvoice.InvoiceTotal2__c;
iSeptRecordCount++;
}
//If Oct
if(objInvoice.PostPeriod__c.month() == 10){
dOctTotal = dOctTotal + objInvoice.InvoiceTotal2__c;
iOctRecordCount++;
}
//If Nov
if(objInvoice.PostPeriod__c.month() == 11){
dNovTotal = dNovTotal + objInvoice.InvoiceTotal2__c;
iNovRecordCount++;
}
//If Dec
if(objInvoice.PostPeriod__c.month() == 12){
dDecTotal = dDecTotal + objInvoice.InvoiceTotal2__c;
iDecRecordCount++;
}
}
}
/* For testing yeardiff for multiple year calc */
Decimal dFinalJanAvg = iJanRecordCount == 0 ? 0 : dJanTotal/yearDiff;
Decimal dFinalFebAvg = iFebRecordCount == 0 ? 0 : dFebTotal/yearDiff;
Decimal dFinalMarAvg = iMarRecordCount == 0 ? 0 : dMarTotal/yearDiff;
Decimal dFinalAprAvg = iAprRecordCount == 0 ? 0 : dAprTotal/yearDiff;
Decimal dFinalMayAvg = iMayRecordCount == 0 ? 0 : dMayTotal/yearDiff;
Decimal dFinalJunAvg = iJunRecordCount == 0 ? 0 : dJunTotal/yearDiff;
Decimal dFinalJulAvg = iJulRecordCount == 0 ? 0 : dJulTotal/yearDiff;
Decimal dFinalAugAvg = iAugRecordCount == 0 ? 0 : dAugTotal/yearDiff;
Decimal dFinalSeptAvg = iSeptRecordCount == 0 ? 0 : dSeptTotal/yearDiff;
Decimal dFinalOctAvg = iOctRecordCount == 0 ? 0 : dOctTotal/yearDiff;
Decimal dFinalNovAvg = iNovRecordCount == 0 ? 0 : dNovTotal/yearDiff;
Decimal dFinalDecAvg = iDecRecordCount == 0 ? 0 : dDecTotal/yearDiff;
lstAccountToUpdate.add(new Account(Id = idKey, Invoice_Line_Item_Avg_Jan__c = dFinalJanAvg, Invoice_Line_Item_Avg_Feb__c = dFinalFebAvg, Invoice_Line_Item_Avg_Mar__c = dFinalMarAvg, Invoice_Line_Item_Avg_Apr__c = dFinalAprAvg, Invoice_Line_Item_Avg_May__c = dFinalMayAvg, Invoice_Line_Item_Avg_Jun__c = dFinalJunAvg, Invoice_Line_Item_Avg_Jul__c = dFinalJulAvg, Invoice_Line_Item_Avg_Aug__c = dFinalAugAvg, Invoice_Line_Item_Avg_Sept__c = dFinalSeptAvg, Invoice_Line_Item_Avg_Oct__c = dFinalOctAvg, Invoice_Line_Item_Avg_Nov__c = dFinalNovAvg, Invoice_Line_Item_Avg_Dec__c = dFinalDecAvg));
}
}
try{
if(lstAccountToUpdate.size() > 0)
update lstAccountToUpdate;
}
catch(exception ex){
system.debug('Exception Message :: ' + ex.getMessage());
}
}
global void finish(Database.BatchableContext BC) {
system.debug('Batch Complete');
}
}
SOQL for loops don't address the 50,000 record limit. They only reduce the heap usage.
It looks like you have a lot of invoices. Your best bet is probably to reduce the batch size in the Database.ExecuteBatch call. The default is 200 (you're processing 200 accounts in each call to "execute".
Dan
Thanks for the feedback. Yes, we have cases where accounts can have more than 50k invoices. I have already tried reducing the batch size to just 1 account at a time. It fails for accounts that have over 50k invoices in the query, regardless of the batch size.
Also, I thought there is no record count limit in a collection(in this case List).
Ah, in that case your best bet is probably to redesign your code to loop over invoices instead of accounts. You may need a two step process - first iterate over invoices and accumulate data in an intermediate object, then a second batch to update from their to the account objects. Or you might be able to accumulate data directly onto the account objects - I haven't looked closely at the rest of the code.
Dan