Apex Code Development You need to sign in to do that
Don't have an account?
DaveSt Creating a batch Apex process that uses data across multiple objects
Hi,
I created a piece of schedulable Apex code but encountered the "scheduled apex class getting Too many DML rows: 10001 error" as one of the queries exceeded the row limit. I am relatively new to Force and was aware of the governere limits but this has caught me out but after a bit of reading understand why.
I think my solution is to adapt my code to use the batch interface and then create a new schedulable piece of code to then call this. My problem is re-cutting my code into a batchable form - would anyone be able to suggest how I can re-structure this?
The original code is:
1 - I split the clear out of the custom object into a separate batch;
2 - I then 'tried' to create a batchable piece of Apex code to do the main processing but cannot get my head around how I transfer my query results into my list and then update my custom object.
3 - The idea was then to call both of these from the scheduble code i.e
Thanks
Dave
I created a piece of schedulable Apex code but encountered the "scheduled apex class getting Too many DML rows: 10001 error" as one of the queries exceeded the row limit. I am relatively new to Force and was aware of the governere limits but this has caught me out but after a bit of reading understand why.
I think my solution is to adapt my code to use the batch interface and then create a new schedulable piece of code to then call this. My problem is re-cutting my code into a batchable form - would anyone be able to suggest how I can re-structure this?
The original code is:
Global class RefreshScheduleMirror implements Schedulable {
global void execute(SchedulableContext SC) {
RefreshSchedule();
}
public void RefreshSchedule() {
/// Delete the old mirror records ///
ScheduleMirror__c[] doomedMirrors = [SELECT Name FROM ScheduleMirror__c];
try {
delete doomedMirrors;
} catch (DmlException e) {
// Process exception here
}
/// Create the current schedulve list and query and query to populate it
OpportunityLineItemSchedule[] allSchedules = [ SELECT CurrencyIsoCode, OpportunityLineItemId, ScheduleDate, Revenue, Quantity, Type, Description FROM OpportunityLineItemSchedule];
/// Create list for the product instance records and query to populate it
OpportunityLineItem[] AlllineItems = [ SELECT ID, Name, OpportunityId, Product2Id, ProductCode FROM OpportunityLineItem];
// Create new list to store the records ( based on the structure defined by the custom object Schedule Mirror )
List<ScheduleMirror__c> newmirrors = new List<ScheduleMirror__c>();
/// Loop around each line item schedule record - create variable 'myschedule' for each iteration through array 'allSchedules'
for (OpportunityLineItemSchedule myschedule : allSchedules) {
// inner loop around the Opportunity Product
for (OpportunityLineItem mylineitem : AlllineItems) {
//
if (mylineitem.ID == myschedule.OpportunityLineItemId) {
// create variable 'mymirror' to hold the record values
ScheduleMirror__c mymirror = new ScheduleMirror__c();
mymirror.CurrencyIsoCode = myschedule.CurrencyIsoCode;
mymirror.Name = myschedule.ScheduleDate.year() + '-' + myschedule.ScheduleDate.month() + '-' + mylineitem.Name;
mymirror.LineItemID__c = myschedule.OpportunityLineItemId;
mymirror.Opportunity__c = mylineitem.OpportunityId;
mymirror.Schedule_Amount__c = myschedule.Revenue;
mymirror.Schedule_Date__c = myschedule.ScheduleDate;
mymirror.Schedule_Description__c = myschedule.Description;
mymirror.Schedule_Quantity__c = myschedule.Quantity;
mymirror.Schedule_Type__c = myschedule.Type;
mymirror.ProductID__c = mylineitem.Product2Id;
mymirror.Product_Code__c = mylineitem.ProductCode;
// Update the list with all the records retrieved in the for loop
newmirrors.add(mymirror);
// update database
upsert newmirrors;
}
}
}
}
}
What I have so far....1 - I split the clear out of the custom object into a separate batch;
Global class PurgeOldMirrorsBatch implements Database.Batchable<sObject>
{
global Database.QueryLocator start(Database.BatchableContext BC)
{
String query ='SELECT Name FROM ScheduleMirror__c';
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext BC, List<ScheduleMirror__c> scope){
delete scope;
}
global void finish(Database.BatchableContext BC){
}
}
2 - I then 'tried' to create a batchable piece of Apex code to do the main processing but cannot get my head around how I transfer my query results into my list and then update my custom object.
Global class RefreshScheduleMirrorBatch implements Database.Batchable<sObject>
{
global Database.QueryLocator start(Database.BatchableContext BC)
/// Grab the data upfront
{
String query ='SELECT CurrencyIsoCode, OpportunityLineItemId, ScheduleDate, Revenue, Quantity, Type, Description, OpportunityLineItem.ID, OpportunityLineItem.Name, OpportunityLineItem.OpportunityId, OpportunityLineItem.Product2Id, OpportunityLineItem.ProductCode FROM OpportunityLineItemSchedule';
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext BC, List<sObject> scope) {
// Create new list to store the records ( based on the structure defined by the custom object Schedule Mirror )
List<ScheduleMirror__c> newmirrors = new List<ScheduleMirror__c>();
// This is the section I am having issues getting my head around - how to build up the records to populate my list from my query results.
for( ScheduleMirror__c myschedule : scope)
{
if (myschedule.ID == myschedule.OpportunityLineItemId) {
// create variable 'mymirror' to hold the record values
ScheduleMirror__c mymirror = new ScheduleMirror__c();
mymirror.CurrencyIsoCode = myschedule.CurrencyIsoCode;
mymirror.Name = myschedule.ScheduleDate.year() + '-' + myschedule.ScheduleDate.month() + '-' + myschedule.Name;
mymirror.LineItemID__c = myschedule.OpportunityLineItemId;
mymirror.Opportunity__c = myschedule.OpportunityId;
mymirror.Schedule_Amount__c = myschedule.Revenue;
mymirror.Schedule_Date__c = myschedule.ScheduleDate;
mymirror.Schedule_Description__c = myschedule.Description;
mymirror.Schedule_Quantity__c = myschedule.Quantity;
mymirror.Schedule_Type__c = myschedule.Type;
mymirror.ProductID__c = myschedule.Product2Id;
mymirror.Product_Code__c = myschedule.ProductCode;
// Update the list with all the records retrieved in the for loop
newmirrors.add(mymirror);
// update database
upsert newmirrors;
}
}
}
}
3 - The idea was then to call both of these from the scheduble code i.e
Global class RefreshScheduleMirrorExec implements Schedulable
{
global void execute(SchedulableContext SC)
{
PurgeOldMirrorsBatch Purge = new PurgeOldMirrorsBatch();
database.executeBatch(Purge);
RefreshScheduleMirrorBatch MirrorBatch = new RefreshScheduleMirrorBatch();
database.executeBatch(MirrorBatch);
}
}
Am I way off the mark in my approach? Any help would be appreciated.Thanks
Dave
You have to move your upsert Statement out of the loop.
===================================
Global class RefreshScheduleMirrorBatch implements Database.Batchable<sObject>
{
global Database.QueryLocator start(Database.BatchableContext BC)
/// Grab the data upfront
{
String query ='SELECT CurrencyIsoCode, OpportunityLineItemId, ScheduleDate, Revenue, Quantity, Type, Description, OpportunityLineItem.ID, OpportunityLineItem.Name, OpportunityLineItem.OpportunityId, OpportunityLineItem.Product2Id, OpportunityLineItem.ProductCode FROM OpportunityLineItemSchedule';
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext BC, List<sObject> scope) {
// Create new list to store the records ( based on the structure defined by the custom object Schedule Mirror )
List<ScheduleMirror__c> newmirrors = new List<ScheduleMirror__c>();
// This is the section I am having issues getting my head around - how to build up the records to populate my list from my query results.
for( ScheduleMirror__c myschedule : scope)
{
if (myschedule.ID == myschedule.OpportunityLineItemId) {
// create variable 'mymirror' to hold the record values
ScheduleMirror__c mymirror = new ScheduleMirror__c();
mymirror.CurrencyIsoCode = myschedule.CurrencyIsoCode;
mymirror.Name = myschedule.ScheduleDate.year() + '-' + myschedule.ScheduleDate.month() + '-' + myschedule.Name;
mymirror.LineItemID__c = myschedule.OpportunityLineItemId;
mymirror.Opportunity__c = myschedule.OpportunityId;
mymirror.Schedule_Amount__c = myschedule.Revenue;
mymirror.Schedule_Date__c = myschedule.ScheduleDate;
mymirror.Schedule_Description__c = myschedule.Description;
mymirror.Schedule_Quantity__c = myschedule.Quantity;
mymirror.Schedule_Type__c = myschedule.Type;
mymirror.ProductID__c = myschedule.Product2Id;
mymirror.Product_Code__c = myschedule.ProductCode;
// Update the list with all the records retrieved in the for loop
newmirrors.add(mymirror);
// update database
//upsert newmirrors;
}
}
upsert newmirrors;
}
}
===================================
See
Best Practice #2: Avoid SOQL Queries or DML statements inside FOR Loops
from the best practices here
https://developer.salesforce.com/page/Apex_Code_Best_Practices
Thanks :)
In the scheduler class, try to reduce the batch size.
By default it takes 200 records. So, let us try with 50.
=================================
Global class RefreshScheduleMirrorExec implements Schedulable
{
global void execute(SchedulableContext SC)
{
PurgeOldMirrorsBatch Purge = new PurgeOldMirrorsBatch();
//database.executeBatch(Purge);
database.executeBatch(Purge, 50);
RefreshScheduleMirrorBatch MirrorBatch = new RefreshScheduleMirrorBatch();
//database.executeBatch(MirrorBatch);
database.executeBatch(MirrorBatch, 50);
}
}
==================================
Hope this resolves your issue.
Thanks :)