Is there any way to bulkify an insert/update operation performed from anonymous apex?
For instance, if I have a script that builds a list of 15,000 records that need to be updated, is there any way to avoid hitting the "Too many DML rows: 10001" error when trying to insert/update?
The DML rows are counted against one transaction not per DML statement for example below code will work as myList.add( new Account(name='xyz') ); is commented. if you uncomment the statement then it will hit Too many DML rows limit. Somewhere in your code there is more than 4 DML exists apart from the code shown which makes a total more than 10000 to make the code fail
List<Account> myList = new List<Account>();
// myList.add( new Account(name='xyz') );
// myList.add( new Account(name='xyz') );
// myList.add( new Account(name='xyz') );
// myList.add( new Account(name='xyz') );
// myList.add( new Account(name='xyz') );
for ( Integer i = 0; i < 9996; i++) {
myList.add( new Account(name='xyz') );
}
System.debug( 'myList.size() : ' + myList.size() ); // => here I see in debug myList.size() : 9996
insert myList;
You cannot update more than 10000 records in a single transaction.
It looks like you may be doing this in a VF page so you will have to write your VF page to check the status of a batch every so often and then do what you require when it is completed. You can use <apex:actionPoller> in your page and run a method to query the AsyncApexJob object to see if the the batch has been completed or not.
Please note the examples may need some tweaking as I modified existing code to use your example. I may have missed something....
EXAMPLE PAGE
<apex:page controller="YOUR CONTROLLER" showHeader="true" sidebar="true" cache="false">
<apex:outPutPanel layout="block" id="main_panel" rendered="{!!fatal_error}">
<apex:outPutPanel layout="block" id="job_start" >
<apex:outPutPanel layout="block" id="job_start_processing" rendered="{!AND(start_polling,NOT(job_is_done))}" styleClass="ctr">
<div class="ctr">
<h1>In Progress - Please Wait</h1>
<br /><br />
</div>
<apex:image url="{!URLFOR($Resource.processing_small)}"/>
</apex:outPutPanel>
<apex:outPutPanel layout="block" id="job_start_button" rendered="{!!start_polling}" styleClass="ctr">
<h1>Click 'Start Process' to begin</h1>
<br /><br />
<apex:commandButton action="{!run_process}" value="Start Process" reRender="job_start,msgs"/>
</apex:outPutPanel>
<apex:actionPoller action="{!poll_for_completion}" reRender="job_start,job_stop,msgs" interval="5"/>
</apex:outPutPanel>
<apex:outPutPanel layout="block" id="job_stop" styleClass="ctr">
<apex:outPutPanel layout="block" id="job_done" rendered="{!AND(job_is_done,ISBLANK(job_error_message))}">
<div class="ctr">
<h1>Complete</h1>
<br /><br />
</div>
<apex:commandButton action="{!restart_page}" value="Reset Page" rerender="msgs"/>
</apex:outPutPanel>
<apex:outPutPanel layout="block" id="job_error" rendered="{!AND(job_is_done,NOT(ISBLANK(job_error_message)))}">
<div class="ctr">
<h1>Error</h1>
<br />
<p>Message: {!job_error_message}</p>
<br />
</div>
<apex:commandButton action="{!restart_page}" value="Reset Page" rerender="msgs"/>
</apex:outPutPanel>
</apex:outPutPanel>
</apex:outPutPanel>
</apex:page>
EXAMPLE CONTROLLER
public with sharing class YOUR CONTROLLER NAME {
private AsyncApexJob[] jobs;
@TestVisible private ApexClass[] apex_class;
@TestVisible private Map<ID,AsyncApexJob> previous_jobs;
public boolean job_is_done {get;set;}
public boolean start_polling {get;set;}
public boolean fatal_error{get;set;}
public string job_error_message {get;set;}
public YOUR CONTROLLER NAME(){
job_is_done = false;
apex_class = [Select ID, Name From ApexClass Where Name = 'CLASS NAME'];
if(apex_class.isEmpty()) return;
//get previous jobs - so we can kinda tell if ours has ran
previous_jobs = New Map<ID,AsyncApexJob>([Select ApexClassID, JobType, MethodName, Status
From AsyncApexJob where JobType = 'BatchApex'
AND ApexClassID = :apex_class[0].id
AND CreatedByID = :userInfo.getUserID()
AND CreatedDate = LAST_N_DAYS:1]);
}
public void run_process(){
try{
example_Batch b = New example_Batch();
database.executeBatch(b);
}catch(exception e){
job_error_message = e.getMessage();
}
//start_polling = true;
job_is_done = true;
}
public void poll_for_completion(){
if(job_is_done || start_polling == false) return;
AsyncApexJob[] tmp = [Select ID, ApexClassID, JobType, MethodName, Status, ExtendedStatus
From AsyncApexJob
where JobType = 'BatchApex'
AND ApexClassID = :apex_class[0].id
AND CreatedByID = :userInfo.getUserID()
AND (ID NOT IN :previous_jobs.keySet())
AND CreatedDate = LAST_N_DAYS:1
AND (Status = 'Completed' OR Status = 'Failed')];
if(!tmp.isEmpty() || test.isRunningTest()){
job_is_done = true;
if(!test.isRunningTest() && tmp[0].Status == 'Failed'){
job_error_message = tmp[0].ExtendedStatus;
}
}
}
public pagereference restart_page(){
pageReference pr = New PageReference('/apex/YOUR PAGE');
pr.setRedirect(true);
return pr;
}
}
In order to do what you are wanting to do you will need to write a batch class to perform the updates. The code would look something like:
global class example_Batch implements Database.Batchable<sObject>{
global Database.QueryLocator start(Database.BatchableContext BC) {
String query = 'SELECT id FROM NRProducts__c WHERE Active__c = true';
if(test.isRunningTest()){
query += ' LIMIT 200';
}
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext BC, List<sObject> scope) {
Decimal Debug = products.size();
for(NRProducts__c p : (NRPProducts__c[])scope){
p.Active__c=false;
}
database.update(scope,false);
}
global void finish(Database.BatchableContext BC) {
}
}
Then to execute it you would use
example_Batch b = New example_Batch();
database.executeBatch(b);
Best Answer
For a quick and dirty approach if I have more than 10,000 rows to update through anonymous apex I will sometimes do something like this
Then run the script twice or whatever. This relies on the change you're making being something that you can filter in the where condition. Also this will get dull if there are many tens of thousands of records to update and you have to kick off the script again and again.