When a price calculation is done for items with multi-level BOM's the batch job creates batch tasks where there is a constraint for every task of the previous BOM level. This creates many to many associations and multiplicative growth in batch constraint records.

Real world example from our system: a price calculation is run for a large set of items.

The batch job generated tasks for 7 BOM levels (0 - 6).

Each BOM level generated 1,162 individual tasks.

A total of 8,134 batch tasks were created in this job.

The BOM Level 6 tasks start immediately as they have no dependencies.

The rest of the tasks (BOM levels 0-5) have a constraint structure as follows:

BOM level 5 has 1162 tasks - each task has 1162 constraints (one for each BOM level 6 task)

That means at BOM level 5 there were 1162 x 1162 constraints created - 1,350,244 batch constraint records (1.3 million).

The same number of constraints exist for each task for BOM levels 4, 3, 2, 1, and 0.

6 x 1162 x 1162 = 8,101,464 batch constraint records.

Over 8 million records were written to the database to create the batch constraints for the price calculation.

Additionally, when the batch job is complete - these are all runtime tasks and therefore need to be deleted.

So, all 8 million batch constraint records have to be created in the history table and deleted from the constraints table.

This amount of data can cause SQL to put a table lock on the constraints table - which means all batch processing is halted until the 8 million records are created / deleted. During times where many price changes happen, this can cause a significant batch processing bottleneck and leads to delays in batch process, along with a large amount of churn in the database.

In AX2012 we were able to change the way the price calculation batch job structures its constraints to vastly improve this process, without changing the price calculation itself.

1) Created a class with an empty run method to be used as a placeholder step.

2) Modified BomCalcJobItemTaskBatchManager::createBatchTasks as follows:

Interrupt the batch task/constraint creation process to put a dummy step between each BOM level

This Dummy step waits for all of the previous BOM level tasks to complete (it has 1 x 1162 constraints).

Since it's run method is empty, it does nothing and just goes to ended.

The next BOM level has 1 constraint on each of its tasks (1162 x 1) - all waiting for the dummy task to complete.

This changes the 1162 x 1162 to instead be (1 x 1162) + (1162 x 1).

We have effectively reduced the constrains per BOM level from 1,350,244 to 2,324... a reduction of 99.8%!

After this change, we observed a nearly 80% reduction in large multilevel BOM price calculations due to the time spent writing and deleting these constraint records. We have not changed anything with the way price calculation is done - we have only changed the way the batch job is structured to massively reduce the constraint record count. This resulted in a significant reduction in database writes, and batch execution time.

Below is the updated BomCalcJobItemTaskBatchManager::createBatchTasks method.

The NullBatchTask class also needs to be created as it is the placeholder for the constraints.

///

/// Creates batch tasks for an instance of the BOMCalcJob_All class.

///

///

/// An instance of the BOMCalcJob_All class

///

public void createBatchTasks(BOMCalcJob_All _bomCalcJob_All)

{

  int               numberOfBatchSessions;

  int               i;

  List              dependentTaskList;

  List              currentTaskList;

  RunBaseBatch          runBaseBatchTask;

  BomCalcItemTask         bomCalcItemTask;

  NullBatchTask          lvlCompleteTask;

  List              lvlCompleteList;

  numberOfBatchSessions = this.numberOfBatchSessions();

  //do bomcalculation from bottom up

  while select bomLevel from bomCalcItemTask

  group by bomLevel

  order by bomLevel desc

    where bomCalcItemTask.SessionGuid == _bomCalcJob_All.parmSessionGuid()

  {

    dependentTaskList = new List(Types::Class);

    //each level of tasks is dependent on the level before, so the last levels current tasks is the dependent tasks for the new loop

    if (currentTaskList)

    {

//      dependentTaskList.appendList(currentTaskList);

      dependentTaskList.appendList(lvlCompleteList);

    }

    currentTaskList = new List(Types::Class);

    //create batch tasks that will do the actual calculation

    for (i=1; i<= numberOfBatchSessions; i++)

    {

      runBaseBatchTask = this.createCalculationTask(bomCalcItemTask,_bomCalcJob_All);

      this.addTask(dependentTaskList, runBaseBatchTask);

      currentTaskList.addEnd(runBaseBatchTask);

    }

    //Add a new task between each BOM level to reduce batch constraint tasks

    lvlCompleteList = new List(Types::Class);

    lvlCompleteTask = NullBatchTask::construct();

    lvlCompleteTask.batchInfo().parmCaption(strFmt("BOM Level %1 - Complete",bomCalcItemTask.BOMLevel));

    this.addTask(currentTaskList, lvlCompleteTask);

    lvlCompleteList.addStart(lvlCompleteTask);

  }

  //nothing was added so there is nothing to run

  if (!currentTaskList)

  {

    return;

  }

  //add clean up task as the last one

  runBaseBatchTask = this.createCleanUpTask(_bomCalcJob_All);

//  this.addTask(currentTaskList,runBaseBatchTask);

  this.addTask(lvlCompleteList,runBaseBatchTask);

  batchHeader.save();

}