Salesforce External ID Fields Code Simplification Power

The idea of this article is to show that it is possible to simplify and reduce the Apex code and save the total number of database statements in the cost of complexification of auxiliary structures.

In this article I would like to demonstrate an alternative way to handle insert operations integrity using Salesforce native features and to save the total number of issued DML statements Governor Limit.

Salesforce is a multi tenant platform and due to this mutinenancy it enforces a lot of different Governor Limits. One of these limits is the total number of issued DML statements, which is equal to 150 DML operations in a single transaction. Every database operation like insert, update, delete, upsert, undelete and merge, and corresponding methods from Database classes, are counted. Several Apex methods, mainly belonging to a Database class, are also counted into this limit like:

• Approval.process
• Database.convertLead
• Database.emptyRecycleBin
• Database.rollback
• Database.setSavePoint
• EventBus.publish
• System.runAs

Recently I had a chance to read a corporate copy of the Second Edition of Andrew Fawcett’s book “Force.com Enterprise Architecture.” This book offers many interesting templates and architectural designs. I would like to consider one example given in this popular book, which I think inefficiently uses the total number of issued DML statements; i.e., using five statements while it is possible to use only a single DML statement. If there is only one DML statement in a transaction, then code for the integrity of database operations is no longer needed.

The author, Andrew Fawcett, is a technical architect at FinancialForce. I like his book about Salesforce Enterprise Architecture and I share his interest in the Formula One motorsport series. In his book, he describes a FormulaForce application to store Formula One championship data, like Formula 1 Championship Seasons, Formula 1 Drivers, Season Races, and Driver results as contestants in races.

In Chapter 5, called Application Service Layer, the author describes a Unit of Work pattern. This is a pattern to encapsulate the database operations integrity in a separate dedicated class.

For every project and every application there might be a need to upload data, so in the FormulaForce application the importSeasons method is used to upload dummy data of the 2013 Season year, including one Race, one Driver and one Driver’s result as a Contestant in a Race.

As an example of a Unit of Work pattern, the importSeasons method refactoring is shown.

On pages 148-150, a code snippet of the importSeasons method (This method uploads dummy data for the 2013 Season year including one Race, one Driver and one Driver’s result as a Contestant in a Race)  is provided which doesn’t use a Unit of Work pattern (as a pattern example) and which consists of 54 lines of code in total. Notice that four insert operations are used in this piece of code and also Database. The setSavePoint method is used which is also counted in the total number of database statements. These lines of code are highlighted in bold, and the highlight is mine.

public static void importSeasons(String jsonData) {
 System.Savepoint serviceSavePoint = Database.setSavePoint();
 try{
  // Parse JSON data
  SeasonService.SeasonsData seasonsData = (SeasonService.SeasonsData) JSON.deserializeStrict(jsonData, SeasonService.SeasonsData.class);
 // Insert Drivers
 Map<String, Driver__c> driversById = new Map<String, Driver__c>();
 for(SeasonService.DriverData driverData : seasonsData.drivers)
  driversById.put(driverData.driverId, new Driver__c( Name = driverData.name,
    DriverId__c = driverData.driverId,
    Nationality__c = driverData.nationality,
    TwitterHandle__c = driverData.twitterHandle)
  );
  insert driversById.values();
  // Insert Seasons
 Map<String, Season__c> seasonsByYear = new Map<String, Season__c>();
 for(SeasonService.SeasonData seasonData : seasonsData.seasons) 
  seasonsByYear.put(seasonData.year, new Season__c(
  Name = seasonData.year, Year__c = seasonData.year)); 
 insert seasonsByYear.values();
 // Insert Races
Map<String, Race__c>racesByYearAndRound = new Map<String, Race__c>();
for(SeasonService.SeasonData seasonData : seasonsData.seasons)
for(SeasonService.RaceData raceData : seasonData.races)
racesByYearAndRound.put(seasonData.Year + raceData.round,
new Race__c(
Season__c = seasonsByYear.get(seasonData.year).Id, Name = raceData.name));
insert racesByYearAndRound.values();
// Insert Contestants
List<Contestant__c> contestants = new List<Contestant__c>();
for(SeasonService.SeasonData seasonData : seasonsData.seasons)
    for(SeasonService.RaceData raceData : seasonData.races)
for(SeasonService.ContestantData contestantData: raceData.contestants) 
    contestants.add(
new Contestant__c(
Race__c = racesByYearAndRound.get(seasonData.Year + raceData.round).Id,
Driver__c = driversById.get( contestantData.driverId).Id,
ChampionshipPoints__c = contestantData.championshipPoints,
DNF__c = contestantData.dnf, Qualification1LapTime__c =
contestantData.qualification1LapTime, Qualification2LapTime__c =
contestantData.qualification2LapTime, Qualification3LapTime__c =
contestantData.qualification3LapTime
));
insert contestants;
} catch (Exception e) {
// Rollback any data written before the exception
Database.rollback(serviceSavePoint); // Pass the exception on
throw e;
}

The version of he importSeasons methodt, which uses a Unit of Work pattern, contains 48 lines of code.

public static void importSeasons(String jsonData) {
 System.Savepoint serviceSavePoint = Database.setSavePoint();
 try{
  // Parse JSON data
  SeasonService.SeasonsData seasonsData = (SeasonService.SeasonsData) JSON.deserializeStrict(jsonData, SeasonService.SeasonsData.class);
 // Insert Drivers
 Map<String, Driver__c> driversById = new Map<String, Driver__c>();
 for(SeasonService.DriverData driverData : seasonsData.drivers)
  driversById.put(driverData.driverId, new Driver__c( Name = driverData.name,
    DriverId__c = driverData.driverId,
    Nationality__c = driverData.nationality,
    TwitterHandle__c = driverData.twitterHandle)
  );
  insert driversById.values();
  // Insert Seasons
 Map<String, Season__c> seasonsByYear = new Map<String, Season__c>();
 for(SeasonService.SeasonData seasonData : seasonsData.seasons) 
  seasonsByYear.put(seasonData.year, new Season__c(
  Name = seasonData.year, Year__c = seasonData.year)); 
 insert seasonsByYear.values();
 // Insert Races
Map<String, Race__c>racesByYearAndRound = new Map<String, Race__c>();
for(SeasonService.SeasonData seasonData : seasonsData.seasons)
for(SeasonService.RaceData raceData : seasonData.races)
racesByYearAndRound.put(seasonData.Year + raceData.round,
new Race__c(
Season__c = seasonsByYear.get(seasonData.year).Id, Name = raceData.name));
insert racesByYearAndRound.values();
// Insert Contestants
List<Contestant__c> contestants = new List<Contestant__c>();
for(SeasonService.SeasonData seasonData : seasonsData.seasons)
    for(SeasonService.RaceData raceData : seasonData.races)
for(SeasonService.ContestantData contestantData: raceData.contestants) 
    contestants.add(
new Contestant__c(
Race__c = racesByYearAndRound.get(seasonData.Year + raceData.round).Id,
Driver__c = driversById.get( contestantData.driverId).Id,
ChampionshipPoints__c = contestantData.championshipPoints,
DNF__c = contestantData.dnf, Qualification1LapTime__c =
contestantData.qualification1LapTime, Qualification2LapTime__c =
contestantData.qualification2LapTime, Qualification3LapTime__c =
contestantData.qualification3LapTime
));
insert contestants;
} catch (Exception e) {
// Rollback any data written before the exception
Database.rollback(serviceSavePoint); // Pass the exception on
throw e;
}

Let’s consider a default Unit of Work implementation to see if it is still used underneath the same four insert operations and Database. The setSavePoint method, which still yields a total of five database statements.

public class SimpleDML implements IDML{
        public void dmlInsert(List<SObject> objList){
            insert objList;
        }
        ….
}
 
public void commitWork() {
        // notify we're starting the commit work
        onCommitWorkStarting();
 
        // Wrap the work in its own transaction 
        Savepoint sp = Database.setSavePoint();
        Boolean wasSuccessful = false;  
        try
        {       
            // notify we're starting the DML operations
            onDMLStarting();
            // Insert by type
            for(Schema.SObjectType sObjectType : m_sObjectTypes)
            {
                m_relationships.get(sObjectType.getDescribe().getName()).resolve();
                m_dml.dmlInsert(m_newListByType.get(sObjectType.getDescribe().getName()));
            }                   
            ….      }
        catch (Exception e)
        {
            // Rollback
            Database.rollback(sp);
            // Throw exception on to caller
            throw e;
        }
        finally
        {           
            // notify we're done with commit work
            onCommitWorkFinished(wasSuccessful);
        }
    }

I agree with the author, in that it might be beneficial in some cases to follow a Unit of Work pattern and to handle database transactions in a governed way, but sometimes if we look at the task through a different angle, we can drastically simplify implementation. For the example provided in the book, I would recommend that slight modifications be made to the database model and to simplify the code drastically.

First of all to test this solution we need to download the code and customization provided by example code downloads.

After we download the code sample, we need to deploy it to some organization.

The classic way is to use ANT but now we can use SFDX instead and execute some commands like this:

sfdx force:mdapi:deploy -u ffcpt5 -d ../src

to deploy source code to some organization authenticated in or created previously.

Let’s take the JSON example data provided in the book and upload it to some Static Resource and let’s call it “json”.

{"drivers":[
  { "name": "Lewis Hamilton",
    "nationality": "British", 
    "driverId": "44",
    "twitterHandle": "lewistwitter"
  }], 
  "seasons": [
  { "year": "2013",
    "races": [
    {
      "round": 1,
      "name": "Spain", 
      "contestants": [
      {
        "driverId": "44", 
        "championshipPoints": 44, 
        "dnf": false,
        "qualification1LapTime": 123, 
        "qualification2LapTime": 124, 
        "qualification3LapTime": 125
      }]
    }]
  }]
}

Also let’s create a Apex Class ImportService and copy the previous version of the importSeasons method into it.

Now let’s execute the following snippet of code to determine the number of DML statements used.

StaticResource x = [ SELECT Name, Body FROM StaticResource WHERE Name = 'json'];
        System.debug(LoggingLevel.ERROR, '@@@ Limits.getDmlStatements(): ' + Limits.getDmlStatements() );
        ImportService.importSeasons(x.Body.toString()); 
        System.debug(LoggingLevel.ERROR, '@@@ Limits.getDmlStatements(): ' + Limits.getDmlStatements() );
Reading the debug logs gives us the number of DML statements used, it is 5.
@@@ Limits.getDmlStatements(): 5

Reading the debug logs gives us the number of DML statements used, it is 5.

@@@ Limits.getDmlStatements(): 5

→ Get 100% Code Coverage for Salesforce Custom Metadata Based Decisions

Now we need to delete inserted data and after that let’s try another version.

After cleaning up, let’s execute this code:

StaticResource x = [ SELECT Name, Body FROM StaticResource WHERE Name = 'json'];
        SeasonService.importSeasons(x.Body.toString()); 
        System.debug(LoggingLevel.ERROR, '@@@ Limits.getDmlStatements(): ' + Limits.getDmlStatements() );

Again, the amount of DML statements is 5.

@@@ Limits.getDmlStatements(): 5

The amount of DML statements is one of the Governor Limits. In Salesforce, Apex execution is allowed to execute 150 DML Statements in a single transaction.

If we want to see the number of governor limits used in a transaction we don’t have to add System.debug statements, we can just scroll down the debug log to the end and find the section about limit usage.

LIMIT_USAGE_FOR_NS   Number of DML statements: 5 out of 150

In the highlighted line it is shown that the number of DML statements used is 5 and the limit is 150.

In the snippet code, we perform a set of database save points and four insert operations of four different objects. Assume that we have 150 different objects to insert, in such a case this approach would exceed governor limit.

Is there a way to refactor this into a more efficient way to achieve desired business logic?

Actually the book hints on  the solution, mentioning that external field references might help to simplify the manual data import process, but this topic is not expanded on further.

In fact, external field references might help to simplify the code as well. In the provided data model, two of three parent objects already have an external id unique field. Let’s introduce another one on the Race object. Let’s call it Unique Key. It will be a Text field with External Id and Unique attributes.

→  Logging exceptions in Salesforce

Let’s reformat the JSON data so that it can be automatically consumed by Salesforce Apex without the need of redundant Data Transfer Objects. The new JSON Data will look like following:

[
  {
    "attributes": {
      "type": "Driver__c"
    },
    "Name": "Lewis Hamilton",
    "DriverId__c": "44",
    "Nationality__c": "British",
    "TwitterHandle__c": "lewistwitter"
  },
  {
    "attributes": {
      "type": "Season__c"
    },
    "Name": "2013",
    "Year__c": "2013"
  },
  {
    "attributes": {
      "type": "Race__c"
    },
    "Name": "Spain",
    "Unique_Key__c": "2013-1",
    "Season__r": {
      "Year__c": "2013"
    }
  },
  {
    "attributes": {
      "type": "Contestant__c"
    },
    "Race__r": {
      "Unique_Key__c": "2013-1"
    },
    "Driver__r": {
      "TwitterHandle__c": "lewistwitter"
    },
    "RacePosition__c": null,
    "ChampionshipPoints__c": 44,
    "DNF__c": false,
    "Qualification1LapTime__c": 123,
    "Qualification2LapTime__c": 124,
    "Qualification3LapTime__c": 125
  }
]

Let’s save it for convenience in another Static Resource and call it “optimizedJSON”.

Now let’s execute the following code:

StaticResource x = [ SELECT Name, Body FROM StaticResource WHERE Name = 'optimizedJSON'];
List<SObject> records = (List<SObject>) JSON.deserialize(x.Body.toString(), List<SObject>

We can see that in this approach, only one DML statement is used.

@@@ Limits.getDmlStatements(): 1

→ Migrate from Aura to Lightning Web Components to Increase Performance

While the same business requirements are met and implemented, the same amount of data is imported, including Season, Race, Driver and Contestant record.

Also, the code is more concise and simpler to understand and maintain. We don’t have here transaction savepoint management since we have only one DML statement. The optimized version of code has two lines of code compared to 54 lines in the without unit of work pattern implemented, or compared to 48 lines in the code snippet having a unit of work pattern implemented including 350 lines of code in flib_Application class, 402 lines of code in fflib_SObjectUnitOfWork class and 54 lines of code in Application class.

We can summarize our results in the following table:

So we have shown here that it is possible to simplify and reduce the code and save total number of issued DML statements in the cost of complexification of auxiliary structures like an additional field in the data model and more complex JSON data format.