Ax 2012 - RecordInsertList - Performance series (Part 2)

Introduction

In my previous post of this series, I highlighted that we can use RecordInsertList in order to execute bulk insert operations and that executing a single insert per iteration is extremelly expensive and should be avoided.

Now, lets take a look at a particular scenario where we try to use the RecordInsertList helper class to improve the performance, but the insert method of the table has been overriden.

Example

In this example I am using a table called MyInvoiceInsertOverriden which has some fields and that has overriden the "insert" method. Just because this method has been overriden, we will not enjoy the full benefits from RecordInsertList helper class. Below, you can see both the AOT node for this table and the insert method that has been implemented.

 

 public void insert()
{
    // Dummy code
    this.InvoiceId += '0';
    
    super();
}

So, lets employ the RecordInsertList approach again. Will the "record by record" insert call from the AOS side be avoided? Will Dynamics AX send a small amount of packages to SQL Server?

 static void insertIntoMyInvOverridenByRecordInsLst(Args _args)
{
    int                             i;
    MyInvoiceInsertOverriden        myInvoiceRecord;
    int                             startTime, endTime;

    // This collection will store the records that must be inserted into the database
    RecordInsertList                invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoiceInsertOverriden));

    startTime = WinAPI::getTickCount();

    for (i = 0; i < 10000; ++i)
    {
        // Initializing the buffer with dummy values
        myInvoiceRecord.InvoiceAmount = (i + 1) * 100;
        myInvoiceRecord.CurrencyCode = 'REA';
        myInvoiceRecord.Qty = (i + 1) * 10;
        myInvoiceRecord.InvoiceId = int2str(i + 1);

        // Instead of inserting the record into the database, we will add
        // it to the RecordInsertList array
        invoicesToBeInserted.add(myInvoiceRecord);
    }

    // After fulfilling the array with the elements to be inserted, we are
    // read to execute the insert operation
    invoicesToBeInserted.insertDatabase();

    endTime = WinAPI::getTickCount();

    info(strFmt('It took %1 miliseconds to insert 10k registers with a RecordInsertList approach with overriden insert',
                endTime - startTime));
}

Now, lets see how many insert SQL statements have been executed through Trace Parser.

 

 Wait!!! What is going on??? 10k insert statements!!!

Unfortunatelly, Ax has executed one call to SQL Server per insert statement. This situation happened because we have overriden the insert method at the table.

The constructor of RecordInsertList allows skipping the insert method call, by passing true to the second parameter, BUT I STRONGLY recommend you to do not try this because the insert method will not be executed. Since there is an insert method, it probably contains important instructions.

Anyway, lets try skipping this method and lets make sure that we will not execute 10k insert statements from AOS side.

 

 static void insertIntoMyInvOverridenSkipInsert(Args _args)
{
    int                             i;
    MyInvoiceInsertOverriden        myInvoiceRecord;
    int                             startTime, endTime;

    // Notice that the second parameter is being passed as true to inform that 
    // the insert method must be skipped
    RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoiceInsertOverriden), true);

    startTime = WinAPI::getTickCount();

    for (i = 0; i < 10000; ++i)
    {
        myInvoiceRecord.InvoiceAmount = (i + 1) * 100;
        myInvoiceRecord.CurrencyCode = 'REA';
        myInvoiceRecord.Qty = (i + 1) * 10;
        myInvoiceRecord.InvoiceId = int2str(i + 1);

        invoicesToBeInserted.add(myInvoiceRecord);
    }

    invoicesToBeInserted.insertDatabase();
}

 This time, if we look at Trace Parser results, just 141 insert statements have been executed from the AOS side. Please, keep in mind that the insert overriden method has not been executed and this skipping may be extremelly dangerous for the consistency of yours data.

 

 Summary

While programming with a performance focus, you should always measure your implementation performance because some assumptions may be wrong.

Although sometimes we have the means to overcome a performance downgrade, we must do the trade off analysis.

In some situations RecordInsertList will not provide all the performance benefits that we would expect. For sure, you could by-pass these situations by writing some code, but the damage can be huge.

The attached file contains the code of this post.

My next post will cover how to use RecordInsertList with temporary tables.

PrivateProject_RecordInsertList.xpo