Ytukyg

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

       ...

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.

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/