2

I have a trigger which updates the total amount of child record values into parent record fields. It is working fine when I do the single records insertion. I have 50000 child records which are related to 7000 parent records. I have given batch size as 200 from dataloader to update some values of child object and I got issue with my below trigger. The error is showing as System.LimitException: Too many SOQL queries 101.

Apex Trigger:

trigger triggerUpdateAmount on Expense__c (after insert, after update, after delete) {

    List <Id> scratchSheetIds = new List<Id> ();
    List <Scratch_Sheet__c> scratchSheetList = new List<Scratch_Sheet__c>();
    List <AggregateResult> expenses = new List<AggregateResult>();
    List <AggregateResult> expensesFringe = new List<AggregateResult>();
    List <AggregateResult> expensesOther = new List<AggregateResult>();
    List <AggregateResult> expensesOtherTaxable = new List<AggregateResult>();



     if(Trigger.isInsert || Trigger.isUpdate) {
        for(Expense__c req:trigger.new){
            scratchSheetIds.add(req.Scratch_Sheet__c);
        }
     }

     if(Trigger.isDelete) {
        for (Expense__c req : Trigger.old) {
            scratchSheetIds.add(req.Scratch_Sheet__c);
        }
     }

     scratchSheetList = [Select Id, Bill_To_Facility_Total__c,fringe_expense1__c,
                            other_expense1__c,Other_Taxable__c From Scratch_Sheet__c 
                        Where Id In :scratchSheetIds];
      expenses = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
            AND Bill_Client__c = true AND Active__c = true
            AND Total_Amount__c != null
            Group By Scratch_Sheet__c];
    expensesFringe = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
                AND Grouping_Total__c = 'Fringe Expense' AND Active__c = true
                AND Total_Amount__c != null
                Group By Scratch_Sheet__c];

    expensesOther = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
                AND Grouping_Total__c = 'Other Expense' AND Active__c = true
                AND Total_Amount__c != null
                Group By Scratch_Sheet__c];  

    expensesOtherTaxable = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
                AND Grouping_Total__c = 'Other Taxable' AND Active__c = true
                AND Total_Amount__c != null
                Group By Scratch_Sheet__c]; 


    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.Bill_To_Facility_Total__c = 0.00;
        for(AggregateResult ar: expenses){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                scr.Bill_To_Facility_Total__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }
    system.debug('scr....0'+scratchSheetList);


    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.fringe_expense1__c = 0.00;
        for(AggregateResult ar: expensesFringe){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                //system.assert(false,ar.get('expr0'));
                scr.fringe_expense1__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }

     system.debug('scr....2'+scratchSheetList);

    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.other_expense1__c = 0.00;
        for(AggregateResult ar: expensesOther){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                scr.other_expense1__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }

    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.Other_Taxable__c = 0.00;
        for(AggregateResult ar: expensesOtherTaxable){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                scr.Other_Taxable__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    } 
    update scratchSheetList;
}

I am sure that I am not doing I havn't writtten any SOQL queries inside the for loop also

  • This trigger looks OK to me. Have you checked for other triggers or declarative logic that might have the problem? – Keith C Oct 29 '16 at 9:44
  • Yeah. I checked other triggers and I was told by my senior team that I am not handling the bulk logic. I don't know what I am doing wrong. I have done everything here. Also, I am getting SOQL issue only sometimes like, when I run batch of 200 for 10 times continuously, I am getting issue at 7th or 8th time and I am reducing the batch size to 80 and running the update. As you know I have 50 k records, I don't think its best practice to sit do it manually all the time. any changes I can do or any mistakes I have done here ? – KSL Oct 29 '16 at 9:48
  • 1
    Suggest you get your senior team to point to exactly what they think is wrong. – Keith C Oct 29 '16 at 9:52
  • Thanks Keith.. Can you confirm me that I have done no mistake please :)... It is only for my understanding to know If I have code properly or not – KSL Oct 29 '16 at 9:54
  • 1
    While there doesn't look like anything that would cause the 101 SOQL error, you are matching records using doubly nested loops which might cause a CPU limit exception. I'll post an answer about that. – Keith C Oct 29 '16 at 9:56
1

Here as an example of the sort of change I think you should make for each case; won't fix the 101 SOQL error but leaves the code cleaner and will significantly reduce the risk of hitting the CPU limit:

Map<Id, Decimal> expenses = new Map<Id, Decimal>();
for (AggregateResult> ar : [
        Select Scratch_Sheet__c, SUM(Total_Amount__c) total
        From Expense__c
        Where Scratch_Sheet__c IN: scratchSheetIds
        AND Bill_Client__c = true
        AND Active__c = true
        AND Total_Amount__c != null
        Group By Scratch_Sheet__c
        ]) {
    expenses.put((Id) ar.get('Scratch_Sheet__c'), (Decimal) ar.get('total'));
}

...

for (Scratch_Sheet__c scr: scratchSheetList) {

    Decimal e = expenses.get(scr.Id);
    scr.Bill_To_Facility_Total__c = e != null ? e : 0.00;

    Decimal ef = expensesFringe.get(scr.Id);
    scr.fringe_expense1__c = ef != null ? ef : 0.00;

    ...
}

This removes the nested loops by using a map.

| improve this answer | |
  • Sure. I will do that. Also I will check with team and update the issue here.. Thanks again – KSL Oct 29 '16 at 10:25
  • You can skip the first loop, too, if you use Map<Id, AggregateResult> as I demonstrate here, which would reduce CPU time used even further. – sfdcfox Oct 29 '16 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.