Vrftsjtryk

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;

}

}

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.