Ax 2012 - RecordInsertList - Performance series (Part 1)
Introduction
From MSDN:
This class allows you to insert more than one record into the database at a time, which reduces communication between the application and the database.
Records are inserted only when the kernel finds the time appropriate, but they are inserted no later than the call to the insertDatabase. add and insertDatabase methods. The RecordInsertList.add and RecordInsertList.insertDatabase methods return the accumulated number of records that are currently inserted, which allows you to keep track of when the records are actually inserted.
The array insert operation automatically falls back to classic record-by-record inserts when non-SQL based tables are used (for example, temporary tables), or when the insert method on the table is overridden (unless it is explicitly discarded).
RecordInsertList is similar to RecordSortedList, but it has built-in client/server support (it automatically packs data from one tier to another when needed), and it lacks the sort order features that are available in RecordSortedList.
In my words:
The RecordInsertList class supports inserting many records of a given type into the database at once, avoiding the overhead that could happen if we have a loop that inserts a single record per iteration.
When the insertDatabase method is called, Dynamics Ax Runtime packs the buffers to be inserted and send them to SQL Server. At SQL Server, the package is extracted and SQL Server will insert them record by record. Since many buffers are being packed together, we save a lot of round trips from AOS to SQL Server. The number of packages will be calculated accordingly to the size and quantity of the records to be inserted and to Ax server buffer size. If the insert or the aosValidateInsert methods are overriden at the table, then, insertDatabase will execute the "record by record" insert. The package approach will also not be employed if the table has a container field or a memo field. Nevertheless, during the instantiation of the RecordInsertList, either the insert and the aosValidateInsert can be skipped. Please, be very careful when skipping these methods.
The usage is pretty straight forward. First of all, you need to instantiate the a RecordInsertList specifying the table id of the table where the records will be inserted. Then, add the buffers at the list using the add method. Finally, call the insertDatabase method in order to insert all the records at once.
Example
Suppose that we have a table called MyInvoice with 4 fields: CurrencyCode, InvoiceAmount, Qty and InvoiceId. Given to a requirement, we need to insert 10k registers into the database. Inserting thousands of registers into the database during the daily activities of ours customers may be painfull since they need to share server resources with tens, may be hundreds, of other users.
Slow mode
We could add 10k records into this table through the below loop. The insert statement is being called at the end of each iteration. This is really expensive!!!
static void insertIntoMyInvoiceThroughLoop(Args _args)
{
int i;
MyInvoice myInvoiceRecord;
int startTime, endTime;
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);
// Record by record insert operation
myInvoiceRecord.insert();
}
endTime = WinAPI::getTickCount();
info(strFmt('It took %1 miliseconds to insert 10k registers with a record-per-record insert approach',
endTime - startTime));
}
After running this job on my test machine for a couple of times, the time spent was around 5 seconds, as you can see on the infolog window below.
We should also take a look at Trace Parser in order to realize what is going on behind the scenes. Notice that the insert statement has been executed 10k times and the job spent almost 3 seconds just with these statements. Trace Parser is a powerful tool that makes very easy for developers to assess performance and to find bottlenecks. For the sake of simplicity, I will not give any details about Trace Parser in this post.
Fast mode
If you are not comfortable with such amount of time, we could enjoy RecordInsertList. At the end of each iteration, the buffers are just added to the collection. Finally, when all the buffers have been added to the collection, the insertDatabase method is called. The below job shows the usage.
static void insertIntoMyInvoiceByRecordInsertList(Args _args)
{
int i;
MyInvoice myInvoiceRecord;
int startTime, endTime;
// This collection will store the records that must be inserted into the database
RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoice));
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',
endTime - startTime));
}
Now, infolog presents a pretty better value.
Again, it worths analyzing Trace Parser results. The insert statement was executed just 141 times and the job spent 297 miliseconds to insert the 10k registers into the database.
What happens when the insertDatabase inserts a register that causes a failure?
If some of the insert statements fail, we have no clue about which insert statements were correctly executed.
For example, I have inserted a register with the invoiceId equals to '100'. So, one of the insert statements must fail because it will also try to insert a record with the '100' value for this field.
An infolog error message will popup to inform about the problem and if query the db, some of the records were indeed inserted.
static void insertByRecordInsertListWithFailures(Args _args)
{
int i;
MyInvoice myInvoiceRecord;
// This collection will store the records that must be inserted into the database
RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoice));
// this record will cause a failure due to a dupplicated invoiceid. This field must be unique
myInvoiceRecord.InvoiceId = '100';
myInvoiceRecord.InvoiceAmount = 0;
myInvoiceRecord.Qty = 0;
myInvoiceRecord.CurrencyCode = 'REA';
myInvoiceRecord.doInsert();
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);
}
invoicesToBeInserted.insertDatabase();
}
Sql server shows that 71 registers have been inserted by the insertDatabase method, plus the one insert that we did at the beginning of the method
Summary
This post demonstrated how to boost bulk insert operations through RecordInsertList class. Avoid doing adopting an insert per iteration approach. Besides improving the performance, the usage of this helper class will not scratch good OO design. Be aware about the behavior when a failure occurs.
The attached xpo Project contains the code of this post.
My next post of the Ax Performance series will highlight the scenario where the insert method has been overriden and we try to use the RecordInsertList.