function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
NatakuNataku 

Batchifying code

Hi Guys,

I'm running into to the governor limit - Too many SOQL query 101
My trigger works normally until I tried to dump 2.5k worth of records in. I thought I was batchifying code properly, but now I'm confused. (btw I tried moving the AggregatedResult out from - for(AggregatedResult ar) - to it's own line and it ran into the same issue. May be I'm looking in the wrong place?

trigger HQDisplayTotalNewAccount on Account (after insert, after undelete)
{
	//variable to keep all HQ IDs for processing
	Set<Id> parentIds = new Set<Id>();
	
	//grab what was being saved first
    for (Account acc : Trigger.new)
    {
    	//process if a child, else change the values back
        if (acc.ParentId != null)
        {
            parentIds.add(acc.ParentId);
        }
    }
    
    if (parentIds.size() > 0)
    {
        List<Account> updates = new List<Account>();
        
        // Leave the job of calculating the sum to the database
        for (AggregateResult ar : 
        		[
                SELECT ParentId p
                		,sum(Couture_Display__c) sumCD
                		,sum(Abode_Display__c) sumAD
                		,sum(KW_Gallery_Display__c) sumKGD
                		,sum(KW_Gallery_Cabinet__c) sumKGC
                		,sum(KW_Tower_Display__c) sumKTD
                		,sum(KW_Wire_Rack__c) sumKWR
                		,sum(KW_Mod_10__c) sumKMT
                		,sum(Evoke_Lam_Gallery_Display__c) sumELGD
                		,sum(Evoke_LV_Gallery_Display__c) sumELVG
                		,sum(Evoke_Gallery_Cabinet__c) sumEGC
                		,sum(Evoke_Tower_Display__c) sumETD
                		,sum(Evoke_Wire_Rack__c) sumEWR
                		,sum(Evoke_Mod_10__c) sumEMT
                		,sum(X2012_Sales__c) sumTW
                		,sum(X2013_Sales__c) sumTR
                		,sum(X2014_Sales__c) sumFO
                FROM Account
                WHERE ParentId in :parentIds
                GROUP BY ParentId
                ])
        {
        	
            // Update without the cost of a preceding query
            updates.add(new Account(Id = (Id) ar.get('p')
            						, Couture_Display__c = (Decimal) ar.get('sumCD')
            						, Abode_Display__c = (Decimal) ar.get('sumAD')
            						, KW_Gallery_Display__c = (Decimal) ar.get('sumKGD')
            						, KW_Gallery_Cabinet__c = (Decimal) ar.get('sumKGC')
            						, KW_Tower_Display__c = (Decimal) ar.get('sumKTD')
            						, KW_Wire_Rack__c = (Decimal) ar.get('sumKWR')
            						, KW_Mod_10__c = (Decimal) ar.get('sumKMT')
            						, Evoke_Lam_Gallery_Display__c = (Decimal) ar.get('sumELGD')
            						, Evoke_LV_Gallery_Display__c = (Decimal) ar.get('sumELVG')
            						, Evoke_Gallery_Cabinet__c = (Decimal) ar.get('sumEGC')
            						, Evoke_Tower_Display__c = (Decimal) ar.get('sumETD')
            						, Evoke_Wire_Rack__c = (Decimal) ar.get('sumEWR')
            						, Evoke_Mod_10__c = (Decimal) ar.get('sumEMT')
            						, X2012_Sales__c = (Decimal) ar.get('sumTW')
            						, X2013_Sales__c = (Decimal) ar.get('sumTR')
            						, X2014_Sales__c = (Decimal) ar.get('sumFO')
            						)
            		   );
        }
        
        
        // This update will cause this trigger to fire again
        update updates;
    }

}
Oh, and this trigger is suppose to add up each column from all the child under a parent and then update that column in the parent with the total.

Thanks,

Best Answer chosen by Nataku
JuFeJuFe
Hi Andy,

You need to build a list like this:

List<List<<Account>> wrapperList = new List<List<Account>>();

List<Account> innerList = new List<Account>();

for( --- your aggregate results --- ar ) {
    if(innerList.size() == 10000) {
        wrapperList.add(innerList);
        innerList = new List<Account>();
    }
    innerList.add(new Account( --- your aggregate object --- ar));
}

After this, you can loop through the wrapperList, getting a full list of accounts and updating those. You can perform the update inside a future method (using the annotation @future), which means the update will be done asynchronously, i.e. whenever your ORG gets the resources to perform the action.

I hope this helps! ;)

All Answers

JuFeJuFe
I would create a List<List<Account>> with a maximum size of less than 10,000 records for the inner list, and a maximum size of 150 for the outer list (so you don't exceed the DML limit). If this doesn't work, you can always use future methods.
James LoghryJames Loghry
I don't see anything glaringly obvious here, but it could potentially be because your aggregate query is operating over such a large result set, that it's issuing queryMore calls to get more than 200 results per aggregation.  (Although, I wonder if it still throws a queryMore exception in that case like it used to).  If that is the case though, then you're probably better off pulling down all results and doing the aggregation yourself rather than using the count / sum SOQL functions. 

Also, is it possible you have additional triggers on Account that could be inserting / cloning accounts?  Perhaps you're running into some trigger recursion that could be calling the query over and over again.  That's the only other thing I can think of.
NatakuNataku
@JuFe

sorry, I'm a bit of a noob at this. what did you mean by future methods? and (still looking this up) how does List<List<Account>> work ? I mean wouldn't the first list just contain one record, which is the list with the actual data?

@James Loghry

I just tried pulling the results and adding up the numbers myself instead of using sum within SOQL, but it's still throwing the same error

I do have another trigger that does some stuff on insert. I'm going to try and remove that trigger to see if thats the cause. (still poking through my list of tiggers to make sure nothing else is doing stuff on insert)

Thanks,
NatakuNataku
hmm, I tried removing all the other ones that were modifying Account on insert with no avail so I tried removing "update updates;" just to see and it inserted the data. I hope the method "update" doesn't do each record on it's own...? if it does, does anyone know of any other way to run these kinds of triggers? 
JuFeJuFe
Hi Andy,

You need to build a list like this:

List<List<<Account>> wrapperList = new List<List<Account>>();

List<Account> innerList = new List<Account>();

for( --- your aggregate results --- ar ) {
    if(innerList.size() == 10000) {
        wrapperList.add(innerList);
        innerList = new List<Account>();
    }
    innerList.add(new Account( --- your aggregate object --- ar));
}

After this, you can loop through the wrapperList, getting a full list of accounts and updating those. You can perform the update inside a future method (using the annotation @future), which means the update will be done asynchronously, i.e. whenever your ORG gets the resources to perform the action.

I hope this helps! ;)
This was selected as the best answer
NatakuNataku
Thanks JuFe, after some toying around with the code it worked!