System.LimitException: Too many query rows: 50001 - How to solve it with a batch?

Question

I need to create a summaries record for each result generated by the following query, which returns around 12K.

The trouble is the aggregate result has to query 600K records, so I get 'System.LimitException: Too many query rows: 50001'.

What should be the right strategy here? Generate a list of contract and call out smaller queries for ~12K of them?

Your advice will be appreciated.

List<AggregateResult> queryCurrentYear = [select TI_Contract__c cntr, Line__c line, count(id) recscount, sum(number_of_calls__c) totalcalls, sum(total_costs__c) totalcost, max(ti_contract__r.value__c) value from detailed_call_data__c where (date__c >= :dateStart) and (date__c <= :dateEnd) and ti_contract__c !='' GROUP BY TI_Contract__c, Line__c]; 

Answer 1

On the face of it, a Batch on Contract would be appropriate. I would write the batch to iterate the Contracts and have the execute perform the aggregate query and related processing. Your aggregate query would simply change to:

public Database.QueryLocator start(BatchableContext context) {
    // You might like to restrict the contracts examined, e.g. to those
    // active in the current year or whatever. That's logic you can decide on
    return [SELECT Id FROM TI_Contract__c]; 
}

public void execute(BatchableContext context, List<TI_Contract__c> contracts) {
    // Note that dateStart and dateEnd are attributes of the batch
    List<AggregateResult> queryCurrentYearForContracts = [
        SELECT TI_Contract__c cntr, Line__c line, COUNT(Id) recscount,
               SUM(number_of_calls__c) totalcalls, SUM(total_costs__c) totalcost,
               MAX(ti_contract__r.value__c) value
        FROM detailed_call_data__c
        WHERE (date__c >= :dateStart) AND (date__c <= :dateEnd) AND
              ti_contract__c IN :contracts GROUP BY TI_Contract__c, Line__c
    ];

    ...
}

You can then make it such that you adjust the batch execution's scope size to best fit with the data you have - e.g. by making it a custom metadata instance value or a custom setting field. That way the admin can adjust the size if governor limits get exceeded during batch execution.