+ Start a Discussion
hylim1215hylim1215 

Please help! First error: Too many query rows: 50001 in my Batch Job

Hi, please help on my Batch Apex Job, it kept on prompt me error 'First error: Too many query rows: 50001'  and i cant reduce the result return from my statement, i need to process all data, can any1 suggest me a way?
 
public class ContactCampaignCount implements Database.Batchable<sObject>{
    Integer subtotalUpdCst_actual = 0;
    private string ctc_types;
    private string cpgn_types;
 
    public ContactCampaignCount(String ctc_type, string cpgn_type){
        
        this.ctc_types = ctc_type;
        this.cpgn_types = cpgn_type;
    }
    
    public Database.QueryLocator start(Database.BatchableContext BC)
    {
        String query = 'SELECT Id,Name,Test1__c, Test2__c, Test3__c, Test4__c, Test5__c FROM Contact WHERE RecordTypeId = \'' + ctc_types +'\'';
        
        return Database.getQueryLocator(query);
    }
   
    public void execute(Database.BatchableContext BC, List<Contact> scope)
    {
                
        List <Campaign> ytd_total_cmpgn = new List <Campaign>();
        List <Campaign> ytd_email_cmpgn = new List <Campaign>();
        List <Campaign> mth_total_cmpgn = new List <Campaign>();
        List <Campaign> mth_email_cmpgn = new List <Campaign>();
        List <CampaignMember> ytd_total_cpgn_mb = new List<CampaignMember>();
        List <CampaignMember> ytd_email_cpgn_mb = new List<CampaignMember>();
        List <CampaignMember> mth_total_cpgn_mb = new List<CampaignMember>();
        List <CampaignMember> mth_email_cpgn_mb = new List<CampaignMember>();
        List<Contact> updateCtcs = new List<Contact>();
        
        ytd_total_cmpgn = [SELECT ID, Campaign_Effective_Start_Date__c, Year__c, Channel__c FROM Campaign WHERE Year__c =: String.valueOf(System.today().year()) and RecordTypeId =: cpgn_types];
        ytd_email_cmpgn = [SELECT ID, Campaign_Effective_Start_Date__c, Year__c, Channel__c FROM Campaign WHERE Year__c =: String.valueOf(System.today().year()) and Channel__c = 'Email' and RecordTypeId =: cpgn_types];
        mth_total_cmpgn = [SELECT ID, Campaign_Effective_Start_Date__c, Year__c, Channel__c FROM Campaign WHERE Campaign_Effective_Start_Date__c >=: System.today().addMonths(-12) and Campaign_Effective_Start_Date__c <=: System.today() and RecordTypeId =: cpgn_types];
		mth_email_cmpgn = [SELECT ID, Campaign_Effective_Start_Date__c, Year__c, Channel__c FROM Campaign WHERE Campaign_Effective_Start_Date__c >=: System.today().addMonths(-12) and Campaign_Effective_Start_Date__c <=: System.today() and Channel__c = 'Email' and RecordTypeId =: cpgn_types];
        
        ytd_total_cpgn_mb = [SELECT Id, ContactId, CampaignId From CampaignMember where CampaignId in :ytd_total_cmpgn];
        ytd_email_cpgn_mb = [SELECT Id, ContactId, CampaignId From CampaignMember where CampaignId in :ytd_email_cmpgn];
        mth_total_cpgn_mb = [SELECT Id, ContactId, CampaignId From CampaignMember where CampaignId in :mth_total_cmpgn];
        mth_email_cpgn_mb = [SELECT Id, ContactId, CampaignId From CampaignMember where CampaignId in :mth_email_cmpgn];
        
        for(Contact ctc : scope){
            
            Integer ytd_total_Campaign = 0;
            Integer ytd_total_emailCampaign = 0;
            Integer mth_total_Campaign = 0;
            Integer mth_emailCampaign = 0;
            Date last_campaign_date;
            
            for(CampaignMember ytd_total_cmb : ytd_total_cpgn_mb){
                if(ytd_total_cmb.ContactId == ctc.Id){
                   ytd_total_Campaign += 1;
                }
            }
            
            for(CampaignMember ytd_total_eml_cmb : ytd_email_cpgn_mb){
                if(ytd_total_eml_cmb.ContactId == ctc.Id){
                   ytd_total_emailCampaign += 1;
                }
            }
            
            for(CampaignMember mth_total_cmb : mth_total_cpgn_mb){
                if(mth_total_cmb.ContactId == ctc.Id){
                   mth_total_Campaign += 1;
                    
                    for(Campaign cm: mth_total_cmpgn){
                        if(cm.Id == mth_total_cmb.CampaignId){
                            
                            if(last_campaign_date == null || last_campaign_date < cm.Campaign_Effective_Start_Date__c){
                                last_campaign_date = cm.Campaign_Effective_Start_Date__c;
                            }
                        }
                    }
                }                
            }
            
            for(CampaignMember mth_total_eml_cmb : mth_email_cpgn_mb){
                if(mth_total_eml_cmb.ContactId == ctc.Id){
                   mth_emailCampaign += 1;
                }
            }
            
            if(ctc.Test5__c == null || ctc.Test5__c < last_campaign_date){               
               ctc.Test5__c = last_campaign_date;
            }
            
            ctc.Test1__c = ytd_total_Campaign;
            ctc.Test2__c = ytd_total_emailCampaign;
            ctc.Test3__c = mth_total_Campaign;
            ctc.Test4__c = mth_emailCampaign;
       
            updateCtcs.add(ctc);
        }
        statics.enableContactTrigger = false;

        List<Database.SaveResult> r1 = Database.update(updateCtcs, false);
        for( Database.Saveresult r : r1 ){
            if ( r.isSuccess()){
                subtotalUpdCst_actual = subtotalUpdCst_actual + 1;
                System.debug('Database.Saveresult >> ' + r);
            }else{
                for ( Database.Error e : r.getErrors() ){
                    System.debug ( updateCtcs + '\n' + e.getStatusCode() + ': ' + e.getMessage());
                } 
            }
        }
    }   
    public void finish(Database.BatchableContext BC)
    { 
    }
}



thanks
Swati GSwati G
Try reducing batch size.