+ Start a Discussion
DodiDodi 

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');
    
        
    }
}

kibitzerkibitzer

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

 

DodiDodi

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.

DodiDodi

Also, I thought there is no record count limit in a collection(in this case List).

kibitzerkibitzer

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

 

Jerun JoseJerun Jose
A side note, you are correct that a list has no limit on the size of its contents (heap limit still applies though). The limit you are running into is with the SOQL that is populating that list.