[SalesForce] Batch Apex: Too many query rows: 50001 error

I have a batch program that is using database stateful to do some rollups across all activities associated to contacts. The total number of batches is 6,771 and the batch size is 200. The program runs successfully till I hit the the 5,486 batch. Then I start getting Too many query rows: 50001 error.

Here is my code:

global class TaskRollupBatch implements Database.Batchable<sObject>, Database.Stateful  {

global final String query;
global integer totalHQ;
global integer totalCallReport; 
global integer totalHQYTD;
global integer totalCallReportYTD;
global Date lastHQDate;
global Date lastCallReportDate;
global Map<Id, Integer> mapHQTotal = new Map<Id, Integer>();
global Map<Id, Integer> mapHQTotalYTD = new Map<Id, Integer>(); 
global Map<Id, Date> mapLastHQDate = new Map<Id, Date>();
global Map<Id, Integer> mapCallReportTotal = new Map<Id, Integer>();
global Map<Id, Integer> mapCallReportTotalYTD = new Map<Id, Integer>();
global Map<Id, Date> mapCallReportDate = new Map<Id, Date>();

global TaskRollupBatch() {
    query = 'select Id, WhoId, HQ_Activity__c, Call_Report__c, ActivityDate from Task ALL ROWS';
    totalHQ = 0;
    totalCallReport = 0;
    totalHQYTD = 0;
    totalCallReportYTD = 0;
    lastHQDate = null;
    lastCallReportDate = null;
}

global database.querylocator start(Database.BatchableContext BC)
{
    return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<sObject> scope)
{   
    for (Task t : (List<Task>)scope) {
        if(t.HQ_Activity__c && t.WhoId != null && String.valueOf(t.WhoId).substring(0,3) == '003') {
            totalHQ = mapHQTotal.get(t.WhoId);
            totalHQYTD = mapHQTotalYTD.get(t.WhoId);
            lastHQDate = mapLastHQDate.get(t.WhoId);
            if(totalHQ == null) {
                totalHQ = 1;
            }
            else {
                totalHQ += 1;
            }
            if(t.ActivityDate != null) {    
                if(t.ActivityDate.year() == system.today().year()) {
                    if(totalHQYTD == null) {
                        totalHQYTD = 1;
                    }
                    else {
                        totalHQYTD += 1;
                    }
                }
            }
            if(lastHQDate == null) {
                lastHQDate = t.ActivityDate;        
            }
            else if(lastHQDate < t.ActivityDate) {
                lastHQDate = t.ActivityDate;
            }
            mapHQTotal.put(t.WhoId, totalHQ);
            mapHQTotalYTD.put(t.WhoId, totalHQYTD); 
            mapLastHQDate.put(t.WhoId, lastHQDate);
        }
        if(t.Call_Report__c && t.WhoId != null && String.valueOf(t.WhoId).substring(0,3) == '003') {
            totalCallReport = mapCallReportTotal.get(t.WhoId);
            totalCallReportYTD = mapCallReportTotalYTD.get(t.WhoId);
            lastCallReportDate = mapCallReportDate.get(t.WhoId);
            if(totalCallReport == null) {
                totalCallReport = 1;
            }
            else {
                totalCallReport += 1;
            }
            if(t.ActivityDate != null) {
                if(t.ActivityDate.year() == system.today().year()) {
                    if(totalCallReportYTD == null) {
                        totalCallReportYTD = 1;
                    }
                    else {
                        totalCallReportYTD += 1;
                    }
                }
            }
            if(lastCallReportDate == null) {
                lastCallReportDate = t.ActivityDate;
            }
            else if(lastCallReportDate < t.ActivityDate) {
                lastCallReportDate = t.ActivityDate;
            }
            mapCallReportTotal.put(t.WhoId, totalCallReport);
            mapCallReportTotalYTD.put(t.WhoId, totalCallReportYTD);
            mapCallReportDate.put(t.WhoId, lastCallReportDate);
        }   
    }

    Map<Id, Contact> contactsToUpdate = new Map<Id, Contact>();
    Map<Id, Contact> contactMap = getContacts(mapHQTotal);
    for (Task t : (List<Task>)scope) {
        Contact c = contactMap.get(t.WhoId);
        if(c != null) {
            if(mapHQTotal.get(t.WhoId) != null) c.HQ_Total__c = mapHQTotal.get(t.WhoId);
            if(mapHQTotalYTD.get(t.WhoId) != null) c.HQ_Total_YTD__c = mapHQTotalYTD.get(t.WhoId);
            if(mapLastHQDate.get(t.WhoId) != null) c.Last_HQ_Date__c = mapLastHQDate.get(t.WhoId);
            if(mapCallReportTotal.get(t.WhoId) != null) c.Task_Call_Report_Total__c = mapCallReportTotal.get(t.WhoId);
            if(mapCallReportTotalYTD.get(t.WhoId) != null) c.Task_Call_Report_Total_YTD__c = mapCallReportTotalYTD.get(t.WhoId);
            if(mapCallReportDate.get(t.WhoId) != null) c.Task_Last_Call_Report_Date__c = mapCallReportDate.get(t.WhoId);
            contactsToUpdate.put(c.Id, c);
        }
    }

    if(!contactsToUpdate.isEmpty() && contactsToUpdate.size() > 0) {
        update contactsToUpdate.values();
    }
}

global void finish(Database.BatchableContext BC) {

}

private Map<Id, Contact> getContacts(Map<Id, Integer> idToIntMap) {
    Map<Id, Contact> retval = new Map<Id, Contact>();
    for(Contact c : [select Id, HQ_Total__c, HQ_Total_YTD__c, Last_HQ_Date__c, Task_Call_Report_Total__c, Task_Call_Report_Total_YTD__c, Task_Last_Call_Report_Date__c from Contact where Id in: idToIntMap.keySet()]) {
        retval.put(c.Id, c);
    }
    return retval;
}
}

I think the error is getting caused by this method:

private Map<Id, Contact> getContacts(Map<Id, Integer> idToIntMap) {
    Map<Id, Contact> retval = new Map<Id, Contact>();
    for(Contact c : [select Id, HQ_Total__c, HQ_Total_YTD__c, Last_HQ_Date__c, Task_Call_Report_Total__c, Task_Call_Report_Total_YTD__c, Task_Last_Call_Report_Date__c from Contact where Id in: idToIntMap.keySet()]) {
        retval.put(c.Id, c);
    }
    return retval;
}

Is there a way to get around this issue?

Any help would be greatly appreciated.
Thanks.

Best Answer

With the class marked using Database.Stateful, members such as mapHQTotal are being preserved across batches. So batch by batch the number of Contacts queried is growing.

You need to change your logic so you only query the new Contacts in each execute method, probably by just having a local Set<Id> variable within the execute that you add the t.WhoId ID values into.

Related Topic