+ Start a Discussion
George Laird 29George Laird 29 

Please Help With Batch APEX, Need To Query 500k Opptys

Hello, I'm a new developer in general, so I really appreciate the help in advance. 

I wrote a batch job that works great in sandbox, but fails in production becuase I have over 500k Opportunitites.  So I get the old too many queries error.  

I was told that I have to put my query in the START method, so that it wil batch the query itself.  Right now, my EXECUTE method calls the class below to do the queries.  What I need help with is that currently I'm usuing two queries and I'm unsure how to put them both into the start method.  

Here is my class that is called from EXECUTE and fails:

public class ProjectionsBatchCalculations {
    
    static date startDate;
    static integer thisYear;
    
    public static void processCalculations(List<Projections__c> proj){
        
        List<Opportunity> apiSubs = new List<Opportunity>();
        List<Projections__c> toUpdate = new List<Projections__c>();
        List<Opportunity> apisubs2018 = new List<Opportunity>();
        List<Opportunity> apiSubs2019 = new List<Opportunity>();
        
        
        for(Projections__c ps : proj){
            
            integer month = ps.Projection_Term_Start_Date__c.month();
            startDate = ps.Projection_Term_Start_Date__c;
            
            for(Opportunity o : [SELECT id,Date_Received__c FROM Opportunity WHERE API_Submission__c = true 
                                 AND Date_Received__c >= 2018-01-01
                                 AND Account.id =: ps.Related_Account__c]){
                                     
              integer year = o.Date_Received__c.year();
                integer oMonth = o.Date_Received__c.month();
                                                         
                if(oMonth==month){
                                         
                  if(year == 2019) {
                    
                        apiSubs2019.add(o);
                    
                    }else if(year == 2018){
                    
                        apiSubs2018.add(o);
                        }  
                                         
                                         
                    }
                                  

            }
            
            
            system.debug('The apiSubs2018 are' +apiSubs2018);
            system.debug('The apiSubs2019 are' +apiSubs2019);
            ps.X2019_API_Submissions__c = apiSubs2019.size();
            ps.X2018_API_Submissions__c = apiSubs2018.size();
            toUpdate.add(ps);
            apiSubs2019.clear();
            apiSubs2018.clear();
        }
        
        system.debug('The list toUpdate is:' +toUpdate);
        update toUpdate;
        
    }
    
}

As you can see, I have a custom object called Projections__c, of which there arent' many records.  That object has a lookup to Account.  I only want to get the Opptys that match Accounts with Projections__c records.  

Please help!!  

I tried to do something like this:

global class ProjectionsBatchClass implements Database.Batchable<sObject>, Database.Stateful{
   
    global List<Opportunity> releventOpptys = new List<Opportunity>();
    global static List<Projections__c> proj = [SELECT id, Related_Account__c, Projection_Term_Start_Date__c FROM Projections__c];
    global final string query;
    
    
    global ProjectionsBatchClass(){
    
        query = 'SELECT id,Date_Received__c FROM Opportunity WHERE API_Submission__c = true AND Date_Received__c >= 2018-01-01 AND Account.id In: proj.Related_Account__c';
        
        
    } 
    

    
    global Database.QueryLocator start(Database.BatchableContext bc){
      
   
      return Database.getQueryLocator(query);


    } 
    
    global void execute(Database.BatchableContext bc, List<Opportunity> proj){
         
          releventOpptys.addAll(proj);  
          ProjectionsBatchCalculations.processCalculations(releventOpptys);          
    }    


But this fails becuase "proj.Realted_Account__c" is not found.  It's not in scope of the Start method.  

I'm stuck!

 
lnallurilnalluri
@George, What is the batch size you were using? Reduce the batch size to a minimum and give it a try.
 
George Laird 29George Laird 29
@Inalluri, I reduced batch size to 10 and still same error.  I think the problem is that the query has to be in the start method, but I'm unsure how to do that in this case since i need to query two objects.