[SalesForce] how to query more than 50000 records in batch apex

Problem: **I am using SOQL query to retrieve more than 50000 records outside start method.
I am getting this error at Line: 11:

System.LimitException: Too many query rows: 50001**

Error line:

global Map<Id,ForceRen_Opportunity_Snapshot__c> Oppsn = new
Map<Id,ForceRen_Opportunity_Snapshot__c>([select
Id,ForceRen_Os_Opportunity_LineItem_Id__c  from
ForceRen_Opportunity_Snapshot__c]);

Batch:

global class ForceREN_OpptySnapshotInsertBatch implements Database.Batchable<sObject>,Database.Stateful {
    global Map<Id,ForceRen_Opportunity_Snapshot__c> Oppsn = new Map<Id,ForceRen_Opportunity_Snapshot__c>([select Id,ForceRen_Os_Opportunity_LineItem_Id__c  from ForceRen_Opportunity_Snapshot__c]);

    global Map<Id,Id> comparesn = new Map<Id,Id>();


        global Database.QueryLocator start(Database.BatchableContext BC) {

            for(ForceRen_Opportunity_Snapshot__c sn : Oppsn.values())
            {
            comparesn.put(sn.ForceRen_Os_Opportunity_LineItem_Id__c,sn.Id);
            }
            System.debug('comparesn start:::::::::'+comparesn);
            String startvalue=System.Label.Start_CloseDate;
            String endvalue=System.Label.End_CloseDate;


            List<Opportunity> Opp = new List<Opportunity>();      
            Opp = [select Id  from Opportunity 
            where StageName NOT in('Duplicate','Closed Lost','Cancelled','Prospect','Active - Low Probability','Active - Med. Probability') 
            AND CloseDate >= 2017-01-01 AND CloseDate <= 2017-12-31  ]; 
            List<Id> oppIds = new List<Id>();
            for(Opportunity o : Opp)
            {
               oppIds.add(o.ID);
            }
            String query = 'SELECT Opportunity.ForceES_Opportunity_Number__c,convertCurrency(Opportunity.ForceES_Pipeline_Amount__c),Opportunity.ForcePW_USDOpportunityAmount__c,Opportunity.CurrencyIsoCode,Opportunity.Tier_3_Install_Region__c,Opportunity.ForceES_PGS_Sub_P_L__c,Opportunity.ForcePW_Pole__c,Opportunity.ForceES_Disposition_Date__c,Opportunity.Name,Opportunity.ForcePW_Sales_Region__c,Opportunity.Account.ForceHQ_Marketing_Name__c,Opportunity.Account.Name,Opportunity.ForceOG_Install_Country__c,Opportunity.CloseDate,Opportunity.ForcePW_Expected_Delivery_Date__c,Opportunity.StageName,Product2.Name,ForcePW_WE_Hub_ht__c,Opportunity.ForcePW_WE_Prd_Scpe__c,Opportunity.TotalOpportunityQuantity,convertCurrency(ForceRll_Curr__c),convertCurrency(Opportunity.ForcePW_NUC_CM__c),Opportunity.ForceES_OPPTY_CM__c FROM OpportunityLineItem WHERE (NOT(Product2.Name like \'%ECO%\')) AND  ForcePW_Primary__c = true  AND OpportunityId IN: oppIds';     
            return Database.getQueryLocator(query);
        }

        global void execute(Database.BatchableContext BC, List<OpportunityLineItem> scope) {
        List<ForceRen_Opportunity_Snapshot__c> OpptySnap = new List<ForceRen_Opportunity_Snapshot__c>{};
             for(OpportunityLineItem a : scope)
             {  

            ForceRen_Opportunity_Snapshot__c newOppty = new ForceRen_Opportunity_Snapshot__c();
            System.debug('comparesn12::::::::'+comparesn);
            if(comparesn.containsKey(a.Id))
            {   
                    newOppty.Id = comparesn.get(a.Id);
                    newOppty.ForceRen_OS_IsRecent__c = false;
            }
            else
            {
                    newOppty.ForceRen_OS_IsRecent__c = true;
            }
            newOppty.ForceRen_OS_Opportunity_Number__c = a.Opportunity.ForceES_Opportunity_Number__c;
            newOppty.ForceRen_OS_Opportunity_Amount_Converted__c = a.Opportunity.ForceES_Pipeline_Amount__c;
            newOppty.ForceRen_OS_Opportunity_Amount_USD__c = a.Opportunity.ForcePW_USDOpportunityAmount__c;
            newOppty.ForceRen_OS_Currency__c = a.Opportunity.CurrencyIsoCode;
            newOppty.ForceRen_OS_Install_Region__c = a.Opportunity.Tier_3_Install_Region__c;
            newOppty.ForceRen_OS_Tier3PL__c = a.Opportunity.ForceES_PGS_Sub_P_L__c;
            newOppty.ForceRen_Os_Opportunity_LineItem_Id__c = a.id;       
            OpptySnap.add(newOppty);        
             }
             upsert OpptySnap ;
             System.debug('Opportunity OpptySnap '+OpptySnap);
        }   

        global void finish(Database.BatchableContext BC) {
        }
    }

How can I fix this error?

Best Answer

Don't calculate them all at once. The whole point of a batch is you break the execution into multiple chunks. So calculate comparesn in your execute method for each individual chunk.

public void execute(Database.BatchableContext context, List<OpportunityLineItem> lineItems)
{
    // you only need to map the child records specific to your lineItems in scope
    Map<Id, Id> myMap = new Map<Id, Id>();
    for (MyChildObject__c child : [
        SELECT LineItem__c FROM MyChildObject__c
        WHERE LineItem__c IN :new Map<Id, SObject>(lineItems).keySet()
    ]){
        myMap.put(child.LineItem__c, child.Id);
    }

    // other logic
}
Related Topic