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

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];

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0