0

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)
{ 
}}
2

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.

  • hi Daniel, can you enlighten me how can i add ContactId from the scope in my select statement? this seems to work with my logic – user2074703 Dec 16 '14 at 7:38
  • Thanks Daniel! Will try on your suggestion! But i do not understand what do you mean by this -> If you take the batch size all the way down to one then you could just use COUNT() in some cases. – user2074703 Dec 16 '14 at 8:17
  • @user2074703 If you pass 1 as the second parameter to executeBatch then only a single Contact will appear in each scope (and the number of batches will increase). As you will only be selecting for a single account in each execution the total number of CampaignMember records will be ytd_total_Campaign. There is no need to use a for loop to count the number of matching records. – Daniel Ballinger Dec 16 '14 at 8:39
  • Thanks Daniel! It works so far but i hit another error: 'Apex CPU time limit exceeded'. Is it because i have too many for loop? Any suggestions? – user2074703 Dec 17 '14 at 7:23
  • @user2074703 Have a look at the debug log to see where all the execution time is going. It will almost certainly be the nested for loops driving the execution time up. Again, reducing the batch size will reduce the number of loops required. Better still would be to rework the code to avoid the nested for loops. This typically involves creating a map structure to hold the required data and only looping over each collection once. – Daniel Ballinger Dec 17 '14 at 22:41
0

If it is a necessity to process all data, what can be done is get the count of total records and LIMIT your query to 50,000 every time till you reach the end by incrementing your loop by 50,000 everytime and setting the OFFSET accordingly.

  • 1
    The maximum offset is 2,000 (from the docs), so you won't be able to page past 50,000 records that way. – Daniel Ballinger Dec 16 '14 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.