Query Limit on Batchable Class

Question

What is the best practice with governor limits with Batchable classes and queries inside of the Batchable class?

I am running into query limits with a Batchable class because I have too many AggregateResult queries. I have a query that returns 100 results and I need to run 3 SUM() queries on each one of those results. I add each record into a list and do one update at the end. This will be called from a scheduler twice a day so I don't want to have to limit the returned results.

I am not sure how to handle this. Does anyone have any suggestions?

global class PaymentAgreementMonitor implements Database.Batchable<sObject> {

global Database.QueryLocator start(Database.BatchableContext BC){
    //Query for all Payment Agreements where Status = Active
    String status = 'Active';
    String Query = 'SELECT Id, Balance_Paid__c, Total_Expected_Balance_Today__c, Ownership__c,Recovery__c,Start_Date__c,Matter__c,' +
    'Contingency_Payout_Method__c, Balance_Paid_Last_Updated__c FROM Payment_Agreement__c WHERE Status__c =\'' + status + '\'';

    return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List<Payment_Agreement__c> scope){
    system.debug('scope:: ' + scope);
    scheduler(scope);
}

global void finish(Database.BatchableContext BC){
    system.debug('finished batchable PaymentAgreementSchedule');
}

public static void scheduler(List<Payment_Agreement__c> payment_agreements){
    List<Id>paListError = new List<Id>();
    List<Payment_Agreement__c> paList = new List<Payment_Agreement__c>();
    for (Payment_Agreement__c pa : payment_agreements) {
        for(AggregateResult schPay : [SELECT SUM(Amount__c)sumAmt
                FROM Scheduled_Payment__c
                WHERE Payment_Agreement__c = :pa.Id
                AND Scheduled_Date__c <=TODAY AND Amount__c != NULL]){
            Decimal decimalAmount = (Decimal)schPay.get('sumAmt');
            if(decimalAmount !=null) {
                pa.Total_Expected_Balance_Today__c = math.abs(decimalAmount);                }
        }

        Decimal decimalAmount;
        for(AggregateResult schPay : [SELECT SUM(Amount__c)sumAmt
                                        FROM Recovery_Payment__c
                                        WHERE Recovery__c = :pa.Recovery__c
                                        AND Date__c >= :pa.Start_Date__c]) {
            decimalAmount = (Decimal)schPay.get('sumAmt');
        }

        //Calculate and insert the value for current Balance_Paid__c
        if (pa.Ownership__c == 'In House') {
                if(decimalAmount != null) {
                    pa.Balance_Paid_Last_Updated__c = DateTime.now();
                    pa.Balance_Paid__c = math.abs(decimalAmount);
                }
        } else if (pa.Ownership__c == 'Local Counsel' && pa.Contingency_Payout_Method__c == 'Contingency Paid to Local Counsel') {
                if(decimalAmount != null) {
                    pa.Balance_Paid_Last_Updated__c = DateTime.now();
                    pa.Balance_Paid__c = math.abs(decimalAmount);
                    system.debug('Payment Agreement Contigency Paiid to Local Counsel -Balance_Paid__c:: ' + pa.Balance_Paid__c);
                }
        } else if (pa.Ownership__c == 'Local Counsel' && pa.Contingency_Payout_Method__c == 'Net Paid to National Funding') {
            Decimal sumAverageRecoveryPaymentsLocalNF = 0;
            Decimal sumAverageExpenseAmount = 0;
                if(decimalAmount != null) {
                    sumAverageRecoveryPaymentsLocalNF = decimalAmount ; 
                }

            for(AggregateResult schPay : [SELECT SUM(advpm__Expense_Amount__c)sumExp
                    FROM advpm__Expense__c
                    WHERE advpm__Matter__c = :pa.Matter__c
                    AND advpm__Category__c = 'Contingency Fees']){
                Decimal sumExpenseDecimal = (Decimal)schPay.get('sumExp');
                if(decimalAmount != null){
                    sumAverageExpenseAmount = math.abs(sumExpenseDecimal);                          
                }
                }
                Decimal sumRecoveryAndExpenses = sumAverageExpenseAmount + sumAverageRecoveryPaymentsLocalNF;
                if(sumRecoveryAndExpenses != 0){
                    pa.Balance_Paid_Last_Updated__c = DateTime.now();
                    pa.Balance_Paid__c = math.abs(sumRecoveryAndExpenses);
                }
        } else{
            pa.Total_Expected_Balance_Today__c = null;
            pa.Balance_Paid_Last_Updated__c = null;
            paListError.add(pa.Id);
        }
        paList.add(pa);
    }
    if(paListError.size() > 0 ){
        NFLogger.logError('PaymentAgreementMonitor.cls', 'Payment Agreement record(s): ' + paListError + ' have incorrect data in Ownership__c or Contingency_Payout_Method__c fields.');
    }
    update paList;
}
}

UPDATES: Updated Code from Suggestions:

private static void scheduler(List<Payment_Agreement__c> payment_agreements){
    ....
    Map<Id, AggregateResult> scheduledPayments = new Map<Id, AggregateResult>([
            SELECT SUM(Amount__c) amount, Payment_Agreement__c Id, Payment_Agreement__r.Recovery__c rec, Payment_Agreement__r.Start_Date__c stDate, Payment_Agreement__r.Matter__c mttr
            FROM Scheduled_Payment__c
            WHERE Payment_Agreement__c IN :payment_agreements
            AND Scheduled_Date__c <=TODAY AND Amount__c != NULL
            GROUP BY Payment_Agreement__c
    ])

    Map<Id, AggregateResult> recoveryPayments = new Map<Id, AggregateResult>([
            SELECT SUM(Amount__c)sumAmt
            FROM Recovery_Payment__c
            WHERE Recovery__c IN :scheduledPayments.rec
            AND Date__c >= :scheduledPayments.stDate]
    );

    Map<Id, AggregateResult> expenseAmount = new Map<Id, AggregateResult>([
            SELECT SUM(advpm__Expense_Amount__c)sumExp
            FROM advpm__Expense__c
            WHERE advpm__Matter__c = :scheduledPayments.mttr
            AND advpm__Category__c = 'Contingency Fees']
    );

    for (Payment_Agreement__c pa : payment_agreements) {
       ...

Answer 1

You do not need to query these aggregates within your loops. It's not really any different than most other queries, with one notable exception: with aggregate queries you can use field alias functionality to use the built in Map<Id, SObject>(List<SObject>) constructor.

Map<Id, AggregateResult> scheduledPayments = new Map<Id, AggregateResult([
    SELECT SUM(Amount__c) amount, Payment_Agreement__c Id
    FROM Scheduled_Payment__c
    WHERE Payment_Agreement__c IN :payment_agreements
    AND Scheduled_Date__c <=TODAY AND Amount__c != NULL
    GROUP BY Payment_Agreement__c
]);
for (Payment_Agreement__c record : payment_agreements)
{
    Decimal amount;
    AggregateResult scheduledPayment = scheduledPayments.get(record.Id);
    if (scheduledPayment != null)
    {
        amount = (Decimal)scheduledPayment.get('amount');
    }
    if (amount != null)
    {
        // further logic here
    }
}

The process will be quite similar for your other aggregations, with the exception of your Start_Date__c filtering, which will involve a bit more thought. You may want to look at pre-computing that value as a rollup via trigger.

Answer 2

Rather then editing my code, I found that Salesforce recommends creating smaller batch calls when you run a Batchable class that will hit limits.

An optional parameter scope. This parameter specifies the number of records to pass into the execute method. Use this parameter when you have many operations for each record being passed in and are running into governor limits. By limiting the number of records, you are limiting the operations per transaction. This value must be greater than zero. If the start method of the batch class returns a QueryLocator, the optional scope parameter of Database.executeBatch can have a maximum value of 2,000. If set to a higher value, Salesforce chunks the records returned by the QueryLocator into smaller batches of up to 2,000 records. If the start method of the batch class returns an iterable, the scope parameter value has no upper limit. However, if you use a high number, you can run into other limits. https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm

Because I have three queries that need to be nested inside of a for loop, if there are 200 records the result will be a governor limit being hit. To resolve this I need to schedule each batch in increments of 66 records at a time.

This is a really great resource for Aggregate queries: https://blog.jeffdouglas.com/2010/02/22/soql-how-i-query-with-thee-let-me-count-the-ways/