You need to sign in to do that
Don't have an account?
MattMet86
SOQL - Aggregate - 50,000 record limit - need help
Ok, I have the following schedulable class but I am running into the SOQL limit on my query. The query should return back around 60 summaires at most but one of our accounts has 92,000 employees which is where i think I am hitting the limit. Can anyone help me code this to get past the limit?
I put a comment around the soql block that is breaking.
I put a comment around the soql block that is breaking.
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; }
Process data in batch and implement Database.stateful in your class so you can maintain data in case of multiple batches.
Thanks,
Naval
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm
http://www.infallibletechie.com/2013/01/simple-batch-apex-example-in-salesforce.html
https://www.safaribooksonline.com/library/view/development-with-the/9780133511611/ch09lev2sec6.html
http://salesforceapexcodecorner.blogspot.in/2011/08/state-management-in-batch-apex-in.html
Let me know if you need my help.
Here is a way to get over 50k records - add read-only to a VF page. Not sure it completly takes care of your issue - but thought it might spur some new thoughts on it.
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000DDy9
Jeff