Ax 2012 - UnitOfWork - Performance series (Part 5)

  • Article

Introduction

 

The past posts (post 1, post 2, post 3, post 4) about performance focused on RecordInsertList class. This class is very usefull to boost performance, but there are some situations when records have relationships, where this class may not fit very well. For example, imagine that you have two tables: ParentFoo and ChildFoo. Suppose that ChildFoo has a field called ParentFooRecId (I guess I don t need to explain what this field is for :p ). Notice that in this scenario, you will be unable to use the RecordInsertList, because this RecordInsertList can just hold elements of one type. Besides in order to insert the ChildFoo records, you must have the ParentFoo record id.

 

Unit of Work

This pattern maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems. (Martin Fowler blog) A simple definition of Work means performing some task. From a software application perspective Work is nothing but inserting, updating, and deleting data. For instance let’s say you have an application which maintains customer data into a database. So when you add, update, or delete a customer record on the database it’s one unit. In simple words the equation is. 1 customer CRUD = 1 unit of work (Article from codeproject)

While using applications, we are doing a lot of database operations behind the scenes: inserting new elements, deleting and updating existing ones. It may be expensive to perform a datatabase call for each of the changes required. In order to reduce this overhead, we can use Unit of Work pattern. The pattern is quite simple, somehow it will keep track of the changes that are being made and at some point, all the changes will be made at once at the database.

Accordingly to MSDN magazine, the Unit of Work responsabilities are:

  • Manage transactions.
  • Order the database inserts, deletes, and updates.
  • Prevent duplicate updates. Inside a single usage of a Unit of Work object, different parts of the code may mark the same Invoice object as changed, but the Unit of Work class will only issue a single UPDATE command to the database.

Back to dynamics AX world, there are some points that you must keep in mind

  • Server tier is required
  • Requires optimistic concurrency
  • Database transaction is implicit and automatic
  • Does not bypass record level security
  • Does not support temporary tables

You can find another post about employing UnitOfWork in Ax at daxmusings blog

 

Example

Lets create a table called FooParent which just has three fields: ParentId, Name and Description. Then, lets create another table called FooChild that contains four fields: ChildId, Name, Description and FooParent. Please, see the below picture.

 

 Notice that a relation has been created at the FooChild table. This relation is mandatory in order to use the Unit of Work pattern. Now it is time to test two different approaches: calling many insert statements through a loop versus using Unit Of Work pattern to control the insertion

So, lets write a method at a class that will insert 10k record of FooParent and 10 records of FooChild though a naive loop.

 

 public class FooController
{
}

public static server void insertThroughManyInsertCalls()
{
    FooChild  child;
    FooParent parent;
    int i;
    
    for (i = 0; i < 10000; ++i)
    {
        parent.ParentId = int2str(i);
        parent.Name = 'Any name for parent' + int2str(i);
        parent.Description = 'Any description for parent' + int2str(i);
        parent.insert();
        
        child.ChildId = int2str(i);
        child.Name = 'Any name for child' + int2str(i);
        child.Description = 'Any description for child' + int2str(i);
        child.FooParent = parent.RecId;
        child.insert();
    }
}

And lets write a job that call this method.

 

 static void ManyInsertCalls(Args _args)
{
    FooController::insertThroughManyInsertCalls();
}

 And what is going on if we look at trace parser???

  Well, 20k database calls and almost 5 seconds to execute them seems like a big waste. Lets rewrite the code using Unit of work pattern.

 

 public static server void insertWithUnitOfWork()
{
    FooChild   child;
    FooParent  parent;

    // Instantiating the UnitOfWork.
    // Notice that this method runs on server
    UnitofWork unitOfWork = new unitOfWork();
    int i;

    for (i = 0; i < 10000; ++i)
    {
        parent.ParentId = int2str(i);
        parent.Name = 'Any name for parent' + int2str(i);
        parent.Description = 'Any description for parent' + int2str(i);

        child.ChildId = int2str(i);
        child.Name = 'Any name for child' + int2str(i);
        child.Description = 'Any description for child' + int2str(i);

        // You will just be able to call this method if there is a relation
        // called FooParent at FooChild table
        child.FooParent(parent);

        // Marking these buffers to be inserted by UnitOfWork
        // The buffers are not being inserted at the database right now.
        unitOfWork.insertonSaveChanges(parent);
        unitOfWork.insertonSaveChanges(child);
    }

    // Finally, asking UnitOfWork to insert the records at the database.
    unitOfWork.saveChanges();
}

 I don t believe this piece of code has worked, what is trace parser saying?

  

Indeed the magic happened!!!

Although the number of insert calls remain 20k, the time fall down from 5 seconds to 1.5 seconds. Now we are more than 3 times faster than the naïve approach that calls the insert statement 20k times through a loop.

If we look at trace parser, we can see that UnitOfWork insert statements are faster than calling the insert many times.

 

Summary

This post has briefly explained a very simple usage of UnitOfWork pattern in Ax 2012. This pattern may be very useful and safe to be employed in many situations. We have a meaningful performance improvement without hurting good principles.

Feel free to download and test the project that is attached to this post.

Finally, remember that:

  1. UnitOfWork must run on the server tie
  2. The child must have a relation explicity defined in AOT to the parent
  3. No record will be inserted into the database until the saveChanges method is called.
  4. You can enjoy the methods: insertOnSaveChanges, deleteOnSaveChanges, updateOnSaveChanges, saveChanges

 

UnitOfWork.xpo

Comments

  • Anonymous
    January 29, 2014
    Your MS Paint skills are glorious. Thanks for taking the time to share. This'll come in handy.

  • Anonymous
    January 30, 2014
    Thank you Brent. I am glad it is being useful.

  • Anonymous
    July 07, 2015
    \\\\\\\\\\\\

  • Anonymous
    July 07, 2015