Ax 2012 - RecordInsertList - Performance series (Part 4)

Introduction

So far, during my previous posts (post 1, post 2, post 3), we realized how to use RecordInsertList with regular and temporary tables on Ax. Before covering a new theme on performance, I would just like to highlight the behavior of the add and insertDatabase methods.

I suppose that you would not think that some calls to the add method do execute insert transactions on the database. The call to insertDatabase assures that all the buffers added to the list will be inserted, so, it will probably execute a bunch of other insert statements in order to have all the buffers inside SQL Server, as promissed by the API.

Depending on the feature you are implementing, you may want to use ttsBegin and ttsCommit to avoid that in case of exceptions or any unexpected problem, that some records will be inserted while others not.

Behind the scenes, the add method execute the bulk insert as soon as the number of records being hold by the list achieves a specific value. The math to calculate this value will depend on the server buffer size that has been configured at Dynamics Ax Server Configuration and on the size of the buffer records themselves.

Example 

Before you call me a liar, lets see a practical example using ours MyInvoice table (sorry for not choosing Foo table). In the below code, you can see that I have added 10k buffers to the list, but insertDatabase method has not been called. Then, a select count is executed against the table and the result is printed at infolog. Surprisingly, infolog shows that 9940 records exist in the database. These records have been successfully inserted by the add method.

 static void callingAddManyTimesWillInsertSomeRecords(Args _args)
{
    int              i;
    MyInvoice        myInvoiceRecord, insertedRecords;

    RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoice));

    delete_from insertedRecords;
    
    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);
    }

    select count(RecId) from insertedRecords;
    
    info(strFmt('Although insertDatabase method has not been called, there are %1 registers in db', 
                insertedRecords.RecId));
}

Is add method inserting the register at the database for every call? If you have read my previous posts, you know the answer for sure. Anyway, lets run a small job that will reveal the answers for those who haven t.

The below job is really straightforward, it instantiates the list of RecordInsertList, adds a single buffer to the list and prints at infolog the number of records that have been inserted into the database.

As you can see, no record has been inserted by a single call to add.

 static void callingAddJustOnce(Args _args)
{
    MyInvoice        myInvoiceRecord, insertedRecords;

    RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoice));

    delete_from insertedRecords;

    myInvoiceRecord.InvoiceAmount = 100;
    myInvoiceRecord.CurrencyCode = 'REA';
    myInvoiceRecord.Qty = 10;
    myInvoiceRecord.InvoiceId = '1';

    invoicesToBeInserted.add(myInvoiceRecord);

    select count(RecId) from insertedRecords;

    info('Probably, no record has been inserted by add since it has just been called once');
    info(strFmt('Number of records inserted is %1', insertedRecords.RecId));
}

Summary

 This post finishes my navigation through RecordInsertList class with a performance perspective for now. I tried to explain that add method will insert the records in to the database, so, for some features you may want to use ttsBegin and ttsCommit to avoid partial insertions of the set of buffers that must be persisted. My first example called add method many times without a call to insertDatabase and it showed that some records were indeed inserted. My next example called the add method just once it demonstrated that the record was not into db, since add method does not use a record by record insertion approach. This kind of approach would not provide a fast performance.

Please, feel free to download and test the project that is attached to this post.

On my next post, I will explore the UnitOfWork pattern and how to use it in Ax. Ax 2012 does support this pattern.

PrivateProject_RecordInsertList_AddMethod.xpo