2

My query hit the 50,000 record limit. Can someone help me rework the query so it operates within the limits.

For reference, we have one account that has 92,000 active employees. The aggregate function should return back between 50 and 60 summaries. (should only be 51 but sometimes people mistype state abbreviations)

Here is my code. I put a comment around the code that is breaking due to limit.

   global class BCI_Account_State_Count_Rollup implements Schedulable {
/*
Created by MM - 1/25/2016
Purpose - count the state field for each active employee on 
an account and update the corresponding state field count
on the account page. 
*/

//Used for aggregrateresults
Public Summary[] Summaries { get; set; }

//Make class schedulable    
global void execute(SchedulableContext ctx) {        
    CronTrigger ct = [SELECT Id, CronExpression, TimesTriggered, NextFireTime
                      FROM CronTrigger WHERE Id = :ctx.getTriggerId()];


    //Create Set with fields from Account page.
    Set<String> statesAcct = new Set<string>{
        'AL','AK','AZ','AR','CA','CO',
            'CT','DE','FL','GA','HI','ID',
            'IL','IN','IA','KS','KY','LA',
            'ME','MD','MA','MI','MN','MS',
            'MO','MT','NE','NV','NH','NJ',
            'NM','NY','NC','ND','OH','OK',
            'OR','PA','RI','SC','SD','TN',
            'TX','UT','VT','VA','WA','WV',
            'WI','WY',
            //Commonwealth/Territories
            'DC'};


                //Create List of active accounts
                list<account> myAccounts = new list<account>();
    myAccounts = [Select ID, Name from Account WHERE Type = 'Client'];

    //Create list to bulkify update command at end of loop
    list<account> accountsToUpdate = new list<account>();

    //Get active employee counts for every account.
    //Getting all at once to limit SOQL queries. 
    Summaries = new List<Summary>();

//CODE THAT IS BREAKING DUE TO SOQL LIMIT
        AggregateResult[] groupedResults = [SELECT COUNT(ID) ct, State__c st, Account__c acct FROM Employees__c WHERE Type__c = 'Client' AND Inactive__c != 'X' GROUP BY Account__c,State__c];
//END BROKEN CODE

    for (AggregateResult ar : groupedResults) {
        Summaries.add(new Summary(ar));
    }


    //Loop MyAccounts
    for ( Account a : myAccounts ){

        //Create map of specific account aggregrate results per state. 
        Map<String, integer> aggregateAcct = new Map<String, integer>();


        //Loop - extract aggregrateResult data just for current account. 
        for(Summary s : Summaries ){
            //system.debug('Summary' + s.acctid);
            //system.debug('Account' + a.Id);

            if(s.acctId == a.id){
                aggregateAcct.put(s.stateName,s.stateCount);
                //System.debug('aggregateAcct - ' + s.stateName + '-' + s.stateCount); 
            } 
        }
        //End Loop - Summaries

        //Create State Name to State count field Map
        Map<String, integer> stateCount = new Map<String, integer>();

        //Link the state count to the field name that is used on the Account object 
        //This way we only get values for states that we have fields for on Account object.
        for(String st : statesAcct){
            StateCount.Put(st, aggregateAcct.get(st));  
        }

        //Now update each state field using our stored values. 
        //Values in StateCount map are now StateAbbreviation and Count

        a.AK__c = stateCount.get('AK');
        a.AL__c = stateCount.get('AL');
        a.AR__c = stateCount.get('AR');
        a.AZ__c = stateCount.get('AZ');
        a.CA__c = stateCount.get('CA');
        a.CO__c = stateCount.get('CO');
        a.CT__c = stateCount.get('CT');
        a.DE__c = stateCount.get('DE');
        a.FL__c = stateCount.get('FL');
        a.GA__c = stateCount.get('GA');
        a.HI__c = stateCount.get('HI');
        a.IA__c = stateCount.get('IA');
        a.ID__c = stateCount.get('ID');
        a.IL__c = stateCount.get('IL');
        a.IN__c = stateCount.get('IN');
        a.KS__c = stateCount.get('KS');
        a.KY__c = stateCount.get('KY');
        a.LA__c = stateCount.get('LA');
        a.MA__c = stateCount.get('MA');
        a.MD__c = stateCount.get('MD');
        a.ME__c = stateCount.get('ME');
        a.MI__c = stateCount.get('MI');
        a.MN__c = stateCount.get('MN');
        a.MO__c = stateCount.get('MO');
        a.MS__c = stateCount.get('MS');
        a.MT__c = stateCount.get('MT');
        a.NC__c = stateCount.get('NC');
        a.ND__c = stateCount.get('ND');
        a.NE__c = stateCount.get('NE');
        a.NH__c = stateCount.get('NH');
        a.NJ__c = stateCount.get('NJ');
        a.NM__c = stateCount.get('NM');
        a.NV__c = stateCount.get('NV');
        a.NY__c = stateCount.get('NY');
        a.OH__c = stateCount.get('OH');
        a.OK__c = stateCount.get('OK');
        a.OR__c = stateCount.get('OR');
        a.PA__c = stateCount.get('PA');
        a.RI__c = stateCount.get('RI');
        a.SC__c = stateCount.get('SC');
        a.SD__c = stateCount.get('SD');
        a.TN__c = stateCount.get('TN');
        a.TX__c = stateCount.get('TX');
        a.UT__c = stateCount.get('UT');
        a.VA__c = stateCount.get('VA');
        a.VT__c = stateCount.get('VT');
        a.WA__c = stateCount.get('WA');
        a.WI__c = stateCount.get('WI');
        a.WV__c = stateCount.get('WV');
        a.WY__c = stateCount.get('WY');
        a.DC__c = stateCount.get('DC');


        accountsToUpdate.add(a);

    }
    //End Loop - Account

    update accountsToUpdate;

}

public class Summary {

    //Boilerplate code needed to fix aggregateresult labels so 
    //that the field names aren't "unknown column(x)"

    public Integer stateCount { get; private set; }
    public String stateName { get; private set; }
    public String acctId { get; private set; }

    public Summary(AggregateResult ar) {
        stateCount = (Integer) ar.get('ct');
        stateName = (String) ar.get('st');
        acctId = (String) ar.get('acct');
    }
}

}

  • You can do what @sfdcJameson did with his for loop, that isn't considered "Inside a loop". Are you attempting to create a batch job? It doesn't appear your following the correct pattern if your trying to do that – EricSSH Apr 1 '16 at 21:39
  • there are some ideas here: salesforce.stackexchange.com/q/7439/2602 -- the analytic snapshot idea is kind of clever if you can live with daily updates – cropredy Apr 1 '16 at 22:37
  • I have updated the original post with the complete code. This way you guys can see what I am trying to do. – Matt M Apr 4 '16 at 16:04
0

Try this

//Get active employee counts for every account.
            //Getting all at once to prevent executing SOQL queries in loop 
            Summaries = new List<Summary>();
            for (AggregateResult ar : [SELECT COUNT(ID) ct, State__c st, Account__c acct FROM Employees__c WHERE Account__r.Type = 'Client' AND Inactive__c != 'X' GROUP BY Account__c,State__c]) {
                Summaries.add(new Summary(ar));
            }
| improve this answer | |
  • 3
    You'd still hit the 50,000 row limit, unfortunately. – sfdcfox Apr 1 '16 at 21:30
  • Also note that queries including an aggregate function don't support queryMore. A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop. developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… – javanoob Apr 1 '16 at 21:31
  • When you put a query in a for loop like that, it still completes the query and stores the results in memory, it doesn't do the query each and every time it goes through the loop – EricSSH Apr 1 '16 at 21:35
  • I have updated the original post with the complete code. This way you guys can see what I am trying to do. – Matt M Apr 4 '16 at 16:04

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.