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