[SalesForce] Please help! First error: Too many query rows: 50001 in the Batch Job

In my Batch Apex Job, i keep getting the 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 anyone 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)
{ 
}}

Best Answer

A few quick ideas.

  1. Reduce the batch size. The will reduce the number of Contacts that appear in the execute scope.. See Database.executeBatch(Object, integer)
  2. Modify your SOQL queries to be more selective and to only bring back the records required for the Contacts in the scope. For example, you could modify the query for ytd_total_cpgn_mb to only select CampaignMember records where the ContactId is present for one of the Contacts in the scope. The typical approach here is to create a Set of ContactIds to use in the SOQL where clauses.
  3. Merge the ytd_total_cmpgn queries into the subsequent queries if they are only used for the in clause.

Rough example of 2 and 3.

Set<id> contactIds = new Set<id>();

for(Contact c : scope) {
    contactIds.add(c.Id);
}

List <CampaignMember> ytd_total_cpgn_mb = [SELECT Id, ContactId, CampaignId  
    From CampaignMember 
    where 
        ContactId in contactIds and 
        CampaignId in (
            SELECT ID 
            FROM Campaign 
            WHERE Year__c = :String.valueOf(System.today().year()) and
                RecordTypeId =: cpgn_types)];

If you take the batch size all the way down to one then you could just use COUNT() in some cases.

Related Topic