Apex Code Development You need to sign in to do that
Don't have an account?
Shaayaal Code is hitting the 10000 DML limit, need to loop it.
I am receiving an error when the this scheduled APEX task runs. Apex Class = UpsellBatchApex Create Upsell Services batch Apex job processed 2 batches with 1 critical batch failures. Error: First error: Too many DML rows: 10001
The issue seems to occur on line 151 of the Class.
151 List<Database.SaveResult> AcctUpdResults = Database.update(updAcctList,false);
152 for (Integer i = 0; i < AcctUpdResults.size(); i++) {
153 if (!AcctUpdResults[i].isSuccess()) {
I need to figure out how to find out the list size using list.size() method.
Then I need to split the list so the split doesn't cross it to more than 10,000 records. I need to run a loop upto the split list size then store the records in another list and then run the database.update for the split list. I need to store the index of the loop upto where the list is run then run the next loop from that index till next defined split / list.size() and store the records in list again. Does anyone know how I can code the APEX to do this? Any help is greatly appreciated.
Full code is below:
1 global class UpsellBatchApex implements Database.Batchable<sObject>,Database.Stateful
2 {
3 global Integer failedRecordscount {get; set;}
4 global Integer successRecordscount {get;set;}
5 //This query variable is only to hold the query string that is passed over by batch scheduler/EmailServiceHandler class
6 global final String AccountQuery;
7 private final boolean IsTest;
8 global List<Servics_by_Market_Segment__c> MktSegList;
9 private static final Set<String> USER_PROFILE_TO_NOTIFY;
10 private static final Set<String> ACC_RECORDTYPES_DEVNAME_TO_INCLUDE;
11 String failureMsg;
12 integer failedRecCount = 0;
13 integer successRecCount = 0;
14
15 static
16 {
17 USER_PROFILE_TO_NOTIFY = new Set<String>();
18 USER_PROFILE_TO_NOTIFY.add('System Administrator');
19 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE = new Set<String>();
20 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE.add('Contact_Centre_Customer');
21 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE.add('Contact_Centre_Customer_Read_Only');
22 }
23 //Constructor:
24 //global UpsellBatchApex(String Q,String Obj,boolean b)
25 global UpsellBatchApex()
26 {
27 failedRecordscount = 0;
28 successRecordscount = 0;
29 AccountQuery = 'Select Id, Market_Segment__c, RecordType.DeveloperName, Include_for_Upsell__c from Account where Include_for_Upsell__c = true and RecordType.DeveloperName in :ACC_RECORDTYPES_DEVNAME_TO_INCLUDE AND Market_Segment__c != null';
30 MktSegList = new List<Servics_by_Market_Segment__c>([Select Id, Category_Description__c, Category_Number__c, Market_Segment_Name__c, Waste_Stream__c from Servics_by_Market_Segment__c]);
31
32 //IsTest = b;
33 }
34 //Here we could either use querylocator or Query Iterator.Query Iterator enforces governor limits of 50k
35 //retrieved records.Since there are no complex processing, used query locator.
36 global Database.querylocator start(Database.BatchableContext BC)
37 { 38 return Database.getQueryLocator(AccountQuery);
39 }
40
41 global void execute(Database.BatchableContext BC, List <Account> Scope)
42 {
43 List<Upsell_Services__c> DelUpsellList = new List<Upsell_Services__c>();
44 List<Upsell_Services__c> UpsellList = new List<Upsell_Services__c>();
45 List<Account> updAcctList = new List<Account>();
46 Set<Account> updAccountSet = new Set<Account>();
47 //Declare a set to hold the account category number
48 successRecordscount = successRecordscount + Scope.size();
49 Map<Id, Account> updAcctMap = new Map<Id, Account>();
50 updAcctMap.putAll(scope);
51
52 String Separator = '::';
53 String AccValue;
54 String MktSegment;
55 String Category;
56 String DecToString;
57 String NewAccValue;
58
59
60 Set<Id> AccId = new Set<Id>();
61 map<Id,String> AccMap = new map<Id,String>();
62 for (Account a : Scope) {
63 if (a.Market_Segment__c != '' && a.Market_Segment__c != null) {
64 //The separator xxx is added just in case if an account is not having
65 AccValue = a.Market_Segment__c + Separator + 'xxx';
66 AccMap.put(a.Id, AccValue);
67 a.Include_for_Upsell__c = false;
68 }
69 }
70 system.debug('Account Map after Account Loop is' + AccMap);
71 for (CSC_Services__c AccServ : [Select Id, Category_Number__c, Account__c from CSC_Services__c where Account__c IN :AccMap.keySet() and Active__c = true]) {
72 if (AccServ.Category_Number__c != null && AccServ.Category_Number__c > 0) {
73 AccValue = AccMap.get(AccServ.Account__c);
74 AccValue = AccValue + Separator + AccServ.Category_Number__c;
75 AccMap.put(AccServ.Account__c, AccValue);
76 }
77
78
79 }
80 system.debug('Account Map after Services Loop is' + AccMap);
81 //Get the existing upsell list for the account and delete it
82 DelUpsellList = [Select Id from Upsell_Services__c where Account__c IN :AccMap.keySet()];
83
84 //Create the new Upsell
85 for (Id i : AccMap.keySet()) {
86 NewAccValue = AccMap.get(i);
87 system.debug('String Value is' + NewAccValue);
88 MktSegment = NewAccValue.substring(0,NewAccValue.indexOf(Separator));
89 Category = NewAccValue.substring(NewAccValue.indexOf(Separator));
90 system.debug('Category value is' + Category);
91 for (Servics_by_Market_Segment__c MktServ : MktSegList) {
92 if (NewAccValue != '' && NewAccValue != null) {
93 if (MktServ.Market_Segment_Name__c.contains(MktSegment)) {
94 DecToString = MktServ.Category_Number__c.toPlainString();
95 if (!Category.contains(DecToString)) {
96 Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = MktServ.Category_Description__c, Account__c = i,
97 Category_Number__c = MktServ.Category_Number__c, Waste_Stream__c = MktServ.Waste_Stream__c,
98 Category_Description__c = MktServ.Category_Description__c);
99 UpsellList.add(newUpsellRec);
100 }
101 }
102 }
103 }
104 }
105
106
107 /*
108 for(Account Acc : Scope)
109 {
110 DelUpsellList.addAll([Select Id from Upsell_Services__c where Account__c =:Acc.Id]);
111 Set<Decimal> CategorySet = new Set<Decimal>();
112 //Get the Account Services Category Number in a Set.
113 for (CSC_Services__c AccServ : [Select Id, Category_Number__c, Account__c from CSC_Services__c where Account__c = :Acc.Id and Active__c = true]) {
114 if (AccServ.Category_Number__c != null && AccServ.Category_Number__c > 0)
115 CategorySet.add(AccServ.Category_Number__c);
116 }
117
118 for (Servics_by_Market_Segment__c MktServ : [Select Id, Category_Description__c, Category_Number__c, Market_Segment_Name__c, Waste_Stream__c from Servics_by_Market_Segment__c
119 where Market_Segment_Name__c INCLUDES (:Acc.Market_Segment__c) AND Category_Number__c NOT IN :CategorySet ]) {
120 //Create new Upsell Record for this account
121 Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = MktServ.Category_Description__c, Account__c = Acc.Id,
122 Category_Number__c = MktServ.Category_Number__c, Waste_Stream__c = MktServ.Waste_Stream__c,
123 Category_Description__c = MktServ.Category_Description__c);
124 UpsellList.add(newUpsellRec);
125 }
126 //Set the account back
127 Acc.Include_for_Upsell__c = false;
128
129 } */
130
131 if (DelUpsellList.size() > 0) {
132 delete DelUpsellList;
133 }
134
135 List<Database.SaveResult> upsellResults = Database.insert(UpsellList, false);
136 system.debug('Upsell Results is:'+upsellResults);
137 for (Integer i = 0; i < upsellResults.size(); i++) {
138 if (!upsellResults[i].isSuccess()) {
139 failureMsg = failureMsg + 'Error: Needs your attention - Could not create Upsell Record for Account Id: '+ UpsellList[i].Account__c + ' . The error reported is: ' +upsellResults[i].getErrors()[0].getMessage() + '\n';
140
141 }
142 else {
143 //SuccessRecords
144 updAccountSet.add(updAcctMap.get(UpsellList[i].Account__c));
145 }
146 }
147 for(Account a : updAccountSet) {
148 a.Include_for_Upsell__c = false;
149 updAcctList.add(a);
150 }
151 List<Database.SaveResult> AcctUpdResults = Database.update(updAcctList,false);
152 for (Integer i = 0; i < AcctUpdResults.size(); i++) {
153 if (!AcctUpdResults[i].isSuccess()) {
154 failureMsg = failureMsg + 'Error: Needs your attention - Could not create Upsell Record for Account Id: '+ updAcctList[i].Id + ' . The error reported is: ' +AcctUpdResults[i].getErrors()[0].getMessage() + '\n';
155 failedRecCount++;
156
157 }
158 else {
159 //SuccessRecords
160 successRecCount++;
161 }
162 }
163
164 /*
165 if (UpsellList.size() > 0) {
166
167 insert UpsellList;
168 update scope;
169 }*/
170 }
171
172 global void finish(Database.BatchableContext BC)
173 {
174 AsyncApexJob a = [Select Id, Status, NumberOfErrors, JobItemsProcessed,
175 TotalJobItems, CreatedBy.Email from AsyncApexJob where Id = :BC.getJobId()];
176 Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
177 String[] toAddresses = new String[] {a.CreatedBy.Email};
178
179 /*for(User users: [Select email from User where Profile.Name in :USER_PROFILE_TO_NOTIFY and IsActive = true]){
180 toAddresses.add(users.email);
181 } */
182 toAddresses.add('CRMCoordinators@transpac.com.au');
183 mail.setToAddresses(toAddresses);
184 mail.setSubject('UpsellBatchApex Job Processed');
185 String bodyText = 'BATCH\n\n Create Upsell Services batch Apex job processed ' + a.TotalJobItems + ' batches with '+ a.NumberOfErrors + ' critical batch failures.\n\nRECORDS\n\n';
186 bodyText = bodyText + 'Number of successful Upsell Services created: '+successRecCount + '. Number of failed records: '+failedRecCount;
187 if (failedRecCount > 0) {
188 bodyText = bodyText + '\n\n Failure Summary : \n' + failureMsg;
189 }
190 mail.setPlainTextBody (bodyText);
191 Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
192 }
193 }
Hello Shaayaal,
In apex i did not find any means by which we can split the list if the size exceeds 10000. The suggested solution of this issue is to perform mass processing using Batch Apex instead of performing the DML operation in the context of a trigger or future method. Please find additional information on Batch Apex at http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
I believe the reason why we do not have this feature is that if we split lists and make dml operations with large number of records then it might hit the heap size limit.
Please post the idea of having the feature of splitting the list in apex on ideaexchange: https://success.salesforce.com/ideaPost
I would consider some kind of dynamic SOQL statement where the SOQL statement is a string and you add the keyword 'Limit 10000'
but since you have multiple SOQL statements, I would split the 10000 between both of them.
Does the operation require complete knowledge of all the data to work properly or can it be done in parallel?
There are three queries involved in the batch operation.
List a= [Select Id, Market_Segment__c, RecordType.DeveloperName, Include_for_Upsell__c from Account where Include_for_Upsell__c = true and RecordType.DeveloperName in ('Contact_Centre_Customer','Contact_Centre_Customer_Read_Only') AND Market_Segment__c != null ]; //returns 381 records.
Map ma=new Map();
for(Integer i=0;i<a.size();i++)
ma.put(a[i].Id,a[i]);
list ups =[Select Id from Upsell_Services__c where Account__c IN :ma.keySet()]; //However I am passing the record and the Ids here and the number of records this query returns is 9987 records.
system.debug(ups.size()); //9987 records.
Now, To explain this clearly. A simple record insert:
Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = 'TestA',Account__c='001g0000007dGnc');
insert newUpsellRec;
This simple new record is showing 3 DML operations, which is causing the issue when this batch is executed, Because the
Upsell_Services__c is taking 3 DML Rows, which is hitting when run against the governor limit of 10,000. There are dependencies in the org which I tried to find but am unable to narrow down as there are no triggers running on this object and no workflows etc. The same operation is working inside my Dev org which confirms some dependency in the org between "Upsell_Services__c" and Account object.
I am trying to modify the code completely to avoid this error as this seems that , all the queries inside the Batch have to be kept outside the execute method, The last query which is being used to iterate on in the execute method , should be passed in the execute method, This way the scope can be controlled, which is number of records passed in the execute and which we can control to avoid this error.The error is not observed when I limit the number of records I am sending in the execute method.
Thankyou to Shashank at Salesforce Developer Support for providing the solution I provided above. Credit where credit is due.