Database Inserts - Primary Table

Does the process of inserting data with Code matter for performance by table?

When inserting data in F&O, there are several best practices on how to do this from MSFT. But when inserting data, do those best practices really matter? Can I code up a solution that is less than ideal and still have a workable solution? Let's find out. This will only look at Azure hosted VM performance for the time being. 

Firstly, as a refresher, we'll be reviewing the basic workloads outlined here: We'll be examining the results using a "Primary Table"; CustTable specifically in this instance.

The Results

Single Insert

The basic test used here is available at

This is a chat showing records inserted per second when using a single insert pattern and the various throughputs in records per second for the test sizes of 1, 10, 250, and 1000. In general, as we do more work, its gets more efficient over time to a point. Inserting 1 record is the slowest from a throughput persective while around 250, give or take, is the fastest.

Multiple Insert

The basic test used here is available at

Very similar to the last test but we can see that overall performance is better than the single insert pattern for all workload sizes. This shows us that creating a transaction as part of a loop incurs negative performance charactistics that we likely don't want. This is, in generaly, nearly twice as more performant than a single insert pattern.


The basic test used here is available at This wasn't tested as it didn't seem to fit specifically how one would create customers. let me know if you'd like to find a way to test this.

Query Insert_RecordSet

Similar to the Insert_RecordSet test, this workload wasn't tested either for the same reasons.


The basic test used here is available at

Here we see similar results as the Multiple Insert pattern above. Nothing specific to report here other than RecordInsertLists don't give a specific performance advantage over just working with buffers in a mutliple insert pattern.


The basic test used here is available at

This is very similar to the RecordInsertList pattern. We're seeing a very slight performance decrease for this given taken when using a RecordSortedList. 


This wasn't tested for the same reasons as Insert_RecordSet and the Query based Insert_RecordSet weren't tested.


For this table, CustTable, and the given workloads, there isn't a clear winner in terms of performance. For ease of use, the multiple insert pattern is probably most comfortable and easy to implement for most reading this article. I'm assuming if you're still here you've been with AX since 2009 before we has a RecordInsertList. Other than that, it doesn't specifically matter what you use so long as it isn't the Single Insert pattern. Again, no clear first place, but the Single Insert Pattern is very clearly in last place. *Foreshadowing* we're going to see the single insert pattern being in last place a lot.