AX Performance: Insert vs RecordInsertList

by Moritz · 30. November 2018

Today I would like to show you the difference, performance wise, between a plain insert of a big set of data versus an insert via a “RecordInsertList” in AX. My example is based on Dynamics 365 fFO (AX7), but it would work the same way in AX 2012.

Setup the Insert vs RecordInsertList Test

First I created a new table for this test. I just added an integer and a string field, both fields also define an index for this table.

Next, I created a runable class to perform both insert operations against the database.

First the “normal” insert statement:

	public static void insert(int rows)
	{
	BulkInsertTable bulkInsertTable;

	for (var i = 0; i < rows; i++)
	{
	bulkInsertTable.Id = i;
	bulkInsertTable.StringValue = strFmt("Value - %1", i);
	bulkInsertTable.insert();
	}
	}

view raw ax_insert hosted with ❤ by GitHub

Per given row we would insert an id and a string value, nothing special here.

Next the “RecordInsertList“:

	public static void insertWithList(int rows)
	{
	BulkInsertTable bulkInsertTable;
	RecordInsertList insertList = new RecordInsertList(bulkInsertTable.TableId);

	for (var i = 0; i < rows; i++)
	{
	bulkInsertTable.Id = i;
	bulkInsertTable.StringValue = strFmt("Value - %1", i);
	insertList.add(bulkInsertTable);
	}
	insertList.insertDatabase();
	}

view raw ax_recordinsertlist hosted with ❤ by GitHub

In contrast to the “normal” insert statement I defined a new “RecordInsertList” object and provided the table id of the “BulkInsertTable”. Instead of an insert in the for – loop, I just call the add method of the list and provide the table buffer in every iteration.

After the loop is finished, I call the insertDatabase method to get my records inserted to the database.

I added the complete code to the end of this post.

Results

In my test I performed both operations with 10k, 100k and 1mio rows:

As you can see the “RecordInsertList” is significantly faster and should be used for inserting bulk data instead of the insert method. (Clearly overlooked that “timeConsumed” already adds units 😉 )

Full Code

	class BulkInsert
	{
	/// <summary>
	/// Runs the class with the specified arguments.
	/// </summary>
	/// <param name = "_args">The specified arguments.</param>
	public static void main(Args _args)
	{
	int timerStart, timerEnd;
	str timeConsumed;
	int rows;
	BulkInsertTable bulkInsertTable;

	for (rows = 10000; rows <= 1000000; rows = rows * 10)
	{
	// delete all records
	delete_from bulkInsertTable;

	// insert
	timerStart = timeNow();
	ttsbegin;
	BulkInsert::insert(rows);
	ttscommit;
	timerEnd = timeNow();
	timeConsumed = timeConsumed(timerStart, timerEnd);
	info(strFmt("INSERT: Added %1 rows in %2 seconds", rows, timeConsumed));

	// delete all records
	delete_from bulkInsertTable;

	// insert with a list
	timerStart = timeNow();
	ttsbegin;
	BulkInsert::insertWithList(rows);
	ttscommit;
	timerEnd = timeNow();
	timeConsumed = timeConsumed(timerStart, timerEnd);
	info(strFmt("LIST: Added %1 rows in %2 seconds", rows, timeConsumed));
	}
	}

	// good old insert
	public static void insert(int rows)
	{
	BulkInsertTable bulkInsertTable;

	for (var i = 0; i < rows; i++)
	{
	bulkInsertTable.Id = i;
	bulkInsertTable.StringValue = strFmt("Value - %1", i);
	bulkInsertTable.insert();
	}
	}

	// insert a list of records
	public static void insertWithList(int rows)
	{
	BulkInsertTable bulkInsertTable;
	RecordInsertList insertList = new RecordInsertList(bulkInsertTable.TableId);

	for (var i = 0; i < rows; i++)
	{
	bulkInsertTable.Id = i;
	bulkInsertTable.StringValue = strFmt("Value - %1", i);
	insertList.add(bulkInsertTable);
	}
	insertList.insertDatabase();
	}

	}