Database Inserts and Performance

Submitted by Nathan Clouse on 11/11/23.

Last modified:

01/15/24

Tags:

Preamble

There are several different ways to create records using X++. First, the most common will be to use a record buffer. By far and away the most common but we're in how we're inserting data in X++. I'd list the ways of inserting data in X++ as follows:

Single record insert - Most common use case, happens all the time. you open a transaction, set values on a buffer, tell it to insert(), close the transaction.
Multi record insert - This is inserting multiple records at a time in a single transaction.
Insert_RecordSet - This is using bypassing using a specific buffer and instead attempting to insert data more directly into the database. You can read more at https://learn.microsoft.com/en-us/dynamicsax-2012/developer/insert-recor...
Query based Insert_RecordSet - This is something I've never seen in the wild specifically. I only know about it from various posts on the subject. I thought it would be interesting to profile this method as well. You can read more at https://learn.microsoft.com/en-us/dotnet/api/dynamics.ax.application.que...
RecordInsertList - This is a collection class that allows for buffers to be added to a collection then inserted later. You can read more at https://learn.microsoft.com/en-us/dotnet/api/dynamics.ax.application.rec...
RecordSortedList - This is a collection class that is typically used to store data rather than insert it but it can be used to insert. You can read more at https://learn.microsoft.com/en-us/dynamicsax-2012/developer/insert-recor...
SysDAInsertObject - This SysDA set of APIs is a new construct. I haven't seen this used specifically but it offers another way to insert data. You can read more at https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev...

The Tables

We'll be looking at several different scenarios of how to insert data for 3 basic tables:

SalesPool: This is a code table with 2 fields, no insert() method, with no extensions. This is meant to represent the simple test case for a table to see maximum performance potential without any other factors altering performance.
CustTable: This is a main table with a fair amount of fields, a insert() method, plus extensions but no extensions on the insert() method. This is meant to be a represent a more standard use case where the table itself has lots of different code behind it plus extensions that compound overall performance.
SalesTable: This is a worksheet header table with a fair amount of fields, an insert() method, plus extensions with at least one extension on the insert() method. This is meant to be a "worse case", but not worst case, compared to CustTable. Just more to consider and handle with this table and how it is extended or used by other areas of the system.

We'll also be using out of the box data entities for the tables above. None of the following entities have any modifications:

SalesOrderPoolEntity: This has no code to it at all so its simply DMF mapping from source to target.
CustCustomerV3Entity: This has a fair amount of code behind it to handle all of the additional fields related to creating a customer rather than using just table CustTable. This is not a set-based entity.
SalesOrderHeaderV2Entity: This also has a far amount of code behind it to handle additional fields for related data sources rather than just table SalesTable. This is not a set-based entity.

The Tests

We'll be testing every possible way of inserting data using X++ against each table and data entity listed above to see "which is fastest". Additionally, we'll consider the side of the amount of data we're inserting such "is inserting 10 records with a RecordInsertList fast than insert just 1 record using a standard table buffer?". We will be testing all ways to insert data ( 7 options ) against 6 different data constructs with different characteristics ( 6 options ), and also with insert counts of 1, 10, 250, and 1000 in a row (4 options ) to see what performs best under what circumstances. That totals 168 test cases. But why stop there? We'll also consider the environment type, too! We'll examine insert performance in a DEV environment, MSFT Hosted Sandbox as a UAT, and also the new PPAC provisioned environments to see if they are any different. That's a total of 504 different test cases we'll be examining. We'll be finding what type of insert works the fastest for a given workload and how the different environment types may provide some negative feedback relative to what you should be doing for performance tuning. My assumption when I started this experiment was the dev environments are different enough that when doing some analysis there, that environment type may give some feedback indicating you need to do X when you really need to do Y to fix a given problem in a MSFT hosted environments.

Insert Types

Single Insert Pattern

This is an example of a single insert pattern in a loop; open tran, set values, call insert(), close tran, loop

for(int i = 0; i <= this.parmRecordCount(); i++)
{
ttsbegin;
SalesPool.SalesPoolId = int2Str(i);
SalesPool.Name = int2Str(i);
SalesPool.insert();
ttscommit;
}

Multiple Insert Pattern

This is an example of a multi insert pattern with a loop; open tran, set values, call insert(), loop, close tran

ttsbegin;
for(int i = 0; i <= this.parmRecordCount(); i++)
{
SalesPool.SalesPoolId = int2Str(i);
SalesPool.Name = int2Str(i);
SalesPool.insert();
}

ttscommit;

Insert_RecordSet Pattern

This in an example of a Insert_RecordSet pattern; insert into target from select from source

ttsbegin;
insert_recordset SalesPool (SalesPoolId, Name)
select SalesPoolId, Name from tmpSalesPool;

ttscommit;

Query Insert_RecordSet Pattern

This is an example of a query based Insert_RecordSet pattern; create query, map fields, insert into target from select from source. This is a variation of the previous pattern is mostly here for completeness.

query = new Query();
qbds_tmpSalesPool = query.addDataSource(tableNum(tmpSalesPool));

fldList_tmpSalesPool = qbds_tmpSalesPool.fields();
fldList_tmpSalesPool.addField(fieldNum(tmpSalesPool, SalesPoolId));
fldList_tmpSalesPool.addField(fieldNum(tmpSalesPool, Name));

fieldMapping = new Map(Types::String, Types::Container);
fieldMapping.insert(fieldStr(SalesPool, SalesPoolId), [qbds_tmpSalesPool.uniqueId(), fieldStr(tmpSalesPool, SalesPoolId)]);
fieldMapping.insert(fieldStr(SalesPool, Name), [qbds_tmpSalesPool.uniqueId(), fieldStr(tmpSalesPool, Name)]);

ttsbegin;
query::insert_recordset(SalesPool, fieldMapping, query);
ttscommit;

RecordInsertList Pattern

This is an example of using a RecordInsertList object to insert records; declare, instantiate, open tran, set values, add, insert collection, close tran

RecordInsertList SalesPoolList;
SalesPoolList = new RecordInsertList(tableNum(SalesOrderPoolEntity));
SalesOrderPoolEntity SalesOrderPoolEntity;

ttsbegin;
for(int i = 0; i <= this.parmRecordCount(); i++)
{
SalesOrderPoolEntity.clear();
SalesOrderPoolEntity.initValue();
SalesOrderPoolEntity.PoolId = int2Str(i);
SalesOrderPoolEntity.PoolName = int2Str(i);

SalesPoolList.add(SalesOrderPoolEntity);

}

SalesPoolList.insertDatabase();
ttscommit;

RecordSortedList Pattern

This is an example of using a RecordSortedList object to insert records; declare, instantiate, open tran, set values, ins, insert collection, close tran

RecordSortedList SalesPoolList;
SalesPoolList = new RecordSortedList(tableNum(SalesPool));
SalesPoolList.sortOrder(fieldName2Id(tableNum(SalesPool), 'SalesPoolId'));

SalesPool SalesPool;
ttsbegin;
for(int i = 0; i <= this.parmRecordCount(); i++)
{
SalesPool.SalesPoolId = int2Str(i);
SalesPool.Name = int2Str(i);
SalesPoolList.ins(SalesPool);
}
SalesPoolList.insertDatabase(con);
ttscommit;

SysDAInsertObject Pattern

This is an example of using a ysDAInsertObject object to insert records; declare, instantiate, add fields, add projection, add query, open tran, exec statement, close trans

var insertObject = new SysDaInsertObject(SalesPool);
insertObject.fields()
.add(fieldStr(SalesPool, SalesPoolId))
.add(fieldStr(SalesPool, Name));

tmpSalesPool source;
var qe = new SysDaQueryObject(source);

var s1 = qe.projection()
.Add(fieldStr(tmpSalesPool, SalesPoolId))
.Add(fieldStr(tmpSalesPool, Name));

insertObject.query(qe);

var insertStmt = new SysDaInsertStatement();
this.startTimer();
ttsbegin;
insertStmt.executeQuery(insertObject);
ttscommit;

Test Insert Sizes

We'll also be looking at various sizes of data inserts to determine if they scale and how they scale. My assumption is that we'll see that large insert sets perform better in some circumstances while smaller insert sets perform better under different circumstances. The insert batch sizes we'll be testing with are 1, 10, 250, 1000. I expect that some types of insert have some pre and post overhead so the different insert batch sizes should tease out if that overhead is there and if so, how much overhead is there.

Environment Types

As mentioned earlier, we'll be testing against different deployment types: Developer ( Tier 1 / CHE ), LCS Sandbox ( Tier 2 / MHE ), PPAC Sandbox (Preview). My assumption is that the different deployment options and how it is deployed will give some workloads an advantage in a Microsoft-based environment and not in a developer environment. This will also help us narrow down operational differences between the environment types that may not be apparent or declared.

What I expect to find

We know from various sources over the years that set operations are faster than row operations. However, with all of the subtleties and nuances to tables and data entities, plus extensions, I'd like to know more about how slow or fast something is to use and weigh that against the utility value of a table vs a data entity. I expect to find that tables are faster than data entities but when using sets, its possible that data entities aren't as specifically slow as when performing row based operations. I also expect to see that a table by itself with no extensions is the fastest option available. Lastly, I expect that MHE environments perform much faster than CHE environments. In the comings chapters that will be linked below. we'll find out if i'm correct or not.