how to query more than 50000 records in batch apex?

Question

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?

Answer 1

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
}

Answer 2

I recommend you use the query optimizer tool. I see several things that strike me in the code snippet below:

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

It appears to me that part of your problem is that you may have what amounts to a non-selective query. You're asking for NOT IN instead of IN or =: to what are stageName picklist values. By asking for !=, you're forcing the query engine to go through every single record to check to test for that condition. While if you asked for a match =, that would be simpler for it to do. However, I don't believe stageName is an indexed field on Opportunity.

It appears as though you're asking for the entire year's data all at once. So, if you have more than 50k opps that were in the pipeline throughout the year, that were closed-won and in any stages that you've not included in your != query, they'll be returned in your results.

So, depending on your sales, you may need to do this by quarter or in finer increments.

With that having been said, you can further optimize your code by making your initial query return a map to eliminate the for loop to get the Ids. Just do as follows:

map<Id,Opportunity>id2Opp = new map<Id,Opportunity>([SELECT Id ... rest of query ...]);

Now, you can use the key set as

set<Id>oppIds = new set<Id>();
oppIds = id2Opp.keyset();