Conserve DML Statements

Apex provides a robust framework for working with the data inside of Salesforce called DML(Data Manipulation Language) statements. DML statements consist of a few different operations. These operations include the commonly used insert, update, upsert, delete, undelete, and merge.

These DML statements come with certain governor limits:

Description Limit
Total number of DML statements issued1 150
Total number of records processed as a result of DML statements, Approval.process, or database.emptyRecycleBin 10,000

1Calls to the following methods count against the number of DML queries issued in a request:

  • Approval.process
  • Database.convertLead
  • Database.emptyRecycleBin
  • Database.rollback
  • Database.setSavePoint
  • delete and Database.delete
  • insert and Database.insert
  • merge
  • undelete and Database.undelete
  • update and Database.update
  • upsert and Database.upsert
  • System.runAs

As you can see from above, these are relatively robust limits. In a simple example, you an insert several objects separately and not have an issue. Let’s take a look at that in the context of a unit test. Let’s say we have a dataSetup method in a utility class we use for our unit tests. That method looks something like:

public static void dataSetup(){
	Foo__c fooOne = new Foo__c();
	Foo__c fooTwo = new Foo__c();
	Foo__c fooThree = new Foo__c();
	Widget__c widgetOne = new Widget__c();
	Widget__c widgetTwo = new Widget__c();
	Widget__c widgetThree = new Widget__c();
	Stub__c stubOne = new Stub__c();
	Stub__c stubTwo = new Stub__c();
	Stub__c stubThree = new Stub__c();
	
	insert fooOne;
	insert fooTwo;
	insert fooThree
	insert widgetOne;
	insert widgetTwo;
	insert widgetThree;
	insert stubOne;
	insert stubTwo;
	insert stubThree;
}

In this scenario, by utilizing the insert DML statement, we were able to generate 9 different objects, 3 Foo__c, 3 Widget__c, and 3 Stub__c. By inserting them individually, we had to perform 9 separate DML operations. In this simple scenario, it isn’t really a major deal to do this. However, what would you do if you wanted to test some larger data sets? What would you do if you wanted 100 Foo__c, 100 Widget__c, and 100 Stub__c? Knowing that there is a limit of 150 DML statements per transaction, we know this method would never work for that. So, what can we do?

We can use collections, such as Lists or Sets. Apex provides a unique syntax for the DML statements as they also handle collections as a parameter. This would allow us to group sets of similar objects together and perform a single DML statement on them. Let’s take a look back at our example above, but let’s now invoke a List.

public static void dataSetup(){
	List<Foo__c> foos = new List<Foo__c>();
	foos.add(new Foo__c());
	foos.add(new Foo__c());
	foos.add(new Foo__c());
	
	List<Widget__c> widgets = new List<Widget__c>();
	widgets.add(new Widget__c());
	widgets.add(new Widget__c());
	widgets.add(new Widget__c());
	
	List<Stub__c> stubs = new List<Stub__c>();
	stubs.add(new Stub__c());
	stubs.add(new Stub__c());
	stubs.add(new Stub__c());
	
	insert foos;
	insert widgets;
	insert stubs;
}

Look at that! We were able to take 9 objects and insert them into the database using only 3 DML statements. This initially took us 9 different statements. By utilizing this functionality, we would subsequently be able to add the 100 Foo__c, 100 Widget__c, and 100 Stub__c with only 3 DML statements as well! What if this isn’t enough for you? What if you want to take it even further?

Well, you can use collections of sObjects as well. An sObject is the generic, base object in Apex. Every object in Apex is an extension of sObject. This means that every object can be cast to an sObject. This will allow us to create one generic List of sObjects to perform a single DML operation. Let’s take a look at it in relation to our running example of dataSetup:

public static void dataSetup(){
	List<sObject> allObjects = new List<sObject>();
	allObjects.add(new Foo__c());
	allObjects.add(new Foo__c());
	allObjects.add(new Foo__c());
	allObjects.add(new Widget__c());
	allObjects.add(new Widget__c());
	allObjects.add(new Widget__c());
	allObjects.add(new Stub__c());
	allObjects.add(new Stub__c());
	allObjects.add(new Stub__c());
	
	insert allObjects;
}

By utilizing this functionality, we are now able to insert 9 different objects of 3 different object types by utilizing a single DML statement! This type of functionality is incredibly powerful! There are a few things to note about this approach. There are certain objects that can not have DML operations performed on them in this method. There is also a cap of 10 object types being mixed together.

There are a slew of nice features that Apex provides to handle data manipulation. Please take some time to read about those features in more depth.

5 Responses to “Conserve DML Statements”

  1. August 15, 2013 at 2:17 pm #

    This is crazy! I never knew Salesforce could accept a list of different sObjects in DML statements. This will definitely cut down on DML statements in the future.

    • August 15, 2013 at 2:26 pm #

      Thanks for the feedback Ron. I was really surprised when I found out this existed too. In the future, I plan on writing an article about proper unit test structure. This is especially useful in that context as it helps speed up the process of generating your test model.

  2. Peter Knolle
    August 15, 2013 at 4:40 pm #

    The 10 object type cap is very interesting. Great little tidbit, Jesse!

  3. Ketan Benegal
    July 18, 2016 at 2:32 pm #

    How do you do exception handling on the DML in such situation? I do the same sort of DML, but I have a situation, where I have to report the exact object type of the error record.

    Any suggestion?

    Thanks,
    Ketan

Trackbacks/Pingbacks

  1. Proper Unit Test Structure in Apex | Jesse Altman - September 8, 2013

    […] I would like to take this one step even further. By utilizing the generic sObject, we can actually limit our DML statements as […]

Leave a Comment