How to query more than 50000 records in start method of batch apex?

Question

Problem: I am using SOQL query to retrieve more than 50000 records inside start method and passing those records to query locator. I am getting this error :Too many query rows: 50001 in batch class.

Batch

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

        String oppQuery = 'SELECT Id FROM Opportunity WHERE StageName NOT in (\'Duplicate\',\'Closed Lost\',\'Cancelled\',\'Prospect\',\'Active - Low Probability\',\'Active - Med. Probability\') ';
        List<Opportunity> Opp = new List<Opportunity>(); 
        System.debug('oppQuery '+oppQuery);     
        Opp = database.query(oppQuery);


        List<Id> oppIds = new List<Id>();
        for(Opportunity o : Opp)
        {
           oppIds.add(o.ID);
        }


        String query = 'SELECT id FROM OpportunityLineItem WHERE (NOT(Product2.Name like \'%ECO%\')) AND  Primary__c = true  AND OpportunityId IN: oppIds';     
        System.debug('query query '+query );    
        return Database.getQueryLocator(query);

    }

Error Line:

String oppQuery = 'SELECT Id FROM Opportunity WHERE StageName NOT in (\'Duplicate\',\'Closed Lost\',\'Cancelled\',\'Prospect\',\'Active - Low Probability\',\'Active - Med. Probability\') ';

How can I fix this error?

Answer 1

You can Optimize your second query to include the condition and using that you don't need 2 query.

Set<String> strSet =new Set<String>()
strSet.add('Duplicate');
strSet.add('Closed Lost');
strSet.add('Cancelled');
strSet.add('Prospect');
strSet.add('Active - Low Probability');
strSet.add('Active - Med. Probability');
String query = 'SELECT id FROM OpportunityLineItem WHERE (NOT(Product2.Name like \'%ECO%\')) AND  Primary__c = true  AND Opportunity.stageName NOT IN: strSet'; 

Now you can query records without any issue. You don't need 2 query anymore.