Ax 2012 - RecordInsertList - Performance series (Part 3)
Introduction
In my previous post we analyzed the behavior of RecordInsertList when the insert method has been overriden. Now, lets play with RecordInsertList against a TempDb table. Is that possible?
I want to show that there are performance benefits of using RecordInsertList to make bulk insertions at a TempDb table, although it is not so fast as regular tables. You must pay attention to the fact that we can not use the RecordInsertList with a TempDb on the cliente tier. It is really dangerous to do that because the code is going to compile, but users will get a really ugly and deceiving runtime exception
I will let temporary tables explanation for a future post, so, right now I will just assume that you have some basic understanding on this theme.
Example
Lets see some X++ code. In the below example, I am trying to instantiate a collection of MyInvoiceTmp. This code compiles without problems, but, unfortunatelly, if we try to execute it, a runtime exception will be thrown because TmpDbs cannot be used with RecordInsertList on the client tier. The infolog message says that we cannot just use them together, but, in fact we can and there are performance benefits if we do so.
So, first of all, I have created a table called MyInvoiceTmp with 4 fields and I set its TableType as TempDb on AOT.
Then, I wrote some code to use it. Notice that I had to pass the TempDb buffer as parameter to the constructor of the RecordInsertList. Since that buffer is not the first constructor parameter, I also had to pass some boolean parameters before the buffer.
static void testAddInsertsRecordsIntoDbOnTheClient(Args _args)
{
MyInvoiceTmp tempRecord;
// This code compiles, but it generates a runtime
// exception with a misleading message saying
// that tempDb can not be used together with
// RecordInsertList
RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoiceTmp), // table id
false, // skip insert
false, // skip database log
false, // skip events
false, // skip aos validation
false, // skip RLS validation
tempRecord); // buffer where records will be inserted
}
And when we try to run this code, we get an error.
Although the message says we can t use temporary tables for such operations, indeed we can. We just need to be careful because the code must be executed on the server.
The below implementation demonstrates how to use RecordInsertList together with TempDb tables. I would like to highlight the "server" mark on the method declaration, which enforces that the method must be executed on the server tie. So, I have created a job which calls a static method.
Here is the job:
static void insertRecordSetListTempTbOnServer(Args _args)
{
MyInvoiceTmp::testInsertOnServerForTempTable();
}
And here is the static method:
public static server void testInsertOnServerForTempTable()
{
int i;
MyInvoiceTmp myInvoiceRecord;
int startTime, endTime;
RecordInsertList invoicesToBeInserted = new RecordInsertList(tableNum(MyInvoiceTmp), // table id
false, // skip insert
false, // skip database log
false, // skip events
false, // skip aos validation
false, // skip RLS validation
myInvoiceRecord); // buffer where records will be inserted
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);
}
invoicesToBeInserted.insertDatabase();
endTime = WinAPI::getTickCount();
info(strFmt('It took %1 miliseconds to insert 10k registers with a RecordInsertList approach at a temptbl',
endTime - startTime));
}
Instead of getting an ugly infolog error message, we can see that the method was executed in about 2.4 seconds. This is better than running over a loop of insertions record by record, but it is not as good as the result for a regular table.
So far, we have seen that there are performance benefits for using RecordInsertList, even with a TempDb. But looking at this number at infolog can be tricky, because the number depends on what processes are running right now on my machine.
We must have a second opinion What is happening behind the scenes? What does Trace Parser say about this?
Accordingly to Trace Parser, we have done 10k inserts. How can this be better than just doing a record-by-record insert through a loop? Why has the time at infolog been better?
If we play a bit more with the results at Trace Parser, we can see that everytime we called the add method, RecordInsertList added the buffer at a list. And when this list achieves a specific size, the RecordInsertList inserts all the records that are in this list at SQL Server.
The below picture shows that when the add method is called around 71 times, it inserts all the records being hold by its collection at sql server. So, the records are not inserted just when we call the insertDatabase method. Better than that, they are not inserted using a record by record approach.
Due to this fact, you are probably going to reach better results using the RecordInsertList with a TempDb than calling the insert method inside a loop.
Summary
Throughout this post we saw an example about how to use TempDb and RecordInsertList together. We have realized that, currently, such mix can just be made on the server tier and that there are advantages from performance side.
The attached xpo contains a project that will allow you to play with the example shown in this post.
My next post on the performance series will continue to analyze the RecordInsertList class. I will show what is happening everytime we call the add method and what happens when we call the insertDatabase method.
PrivateProject_RecordInsertListWithTempDB.xpo
Comments
Anonymous
December 16, 2013
Great article thanks very much. For a long time (due to me not trying the two pre-conditions - adding the buffer to the new() call and running it server-side - at the same time) I thought this couldn't be done. This opens up the world of TempDB tables a lot as this, combined with an update_recordset joined to our TempDB table, offers a great and very fast way to do mass-updating of selected records in other tables.Anonymous
January 28, 2014
Thank you very much for your comment AJ. I am glad it helped. I have just written a new post about Unit Of Work. I hope it is usefull as well.Anonymous
November 22, 2015
Hi, I tried using this approach to insert like 4K records on temp table and is working properly but my issue is that it is not populating the records on the form when using the temp table as datasource. Any idea how can I achieve it? Looking forward to hear from you positively!