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? 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: https://www.atomicax.com/article/database-inserts-and-performance. We'll be examining the results using a "Primary Data Entity"; CustCustomerV3Entity specifically in this instance.

The Results

Single Insert

The basic test used here is available at https://www.atomicax.com/article/database-inserts-and-performance#Single...

Similar to past test runs, we have the workloads grouped in a workload size of 1, 10, 250, and 1000. For the single insert pattern, performance is quite underwhelming but peaks at a workload size of around 250.

Multi Insert

The basic test used here is available at https://www.atomicax.com/article/database-inserts-and-performance#MultiI....

Compared to the single insert pattern, performance is overall better but not great by any means. This appears that opening a transaction has some moderately measurable performance impacts but the entity itself could have some performance issues.

Insert_RecordSet

The basic test used here is available at https://www.atomicax.com/article/database-inserts-and-performance#Insert.... 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.

RecordInsertList

The basic test used here is available at https://www.atomicax.com/article/database-inserts-and-performance#Record....

Comparing this to the multiple insert pattern, we can see the numbers are almost exactly the same in terms of throughput for all workloads. I find this quite interesting because set based operations like RecordInsertLists are supposed to be one of the fastest options available. For this workload type, that doesn't appear to be the case.

RecordSortedList

The basic test used here is available at https://www.atomicax.com/article/database-inserts-and-performance#Record....

This workload type is nearly identical to using a RecordInsertList in terms of performance even though it has some variability.

SysDAInsertObject

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

Conclusions

The results on this set of tests was a bit shocking for me. It appears as though the multi insert pattern is fastest overall with the single insert pattern being the slowest. That's not especially surprising but overall the difference between all tests types nearly doesn't matter. Regardless of test type, the numbers are overall pretty low. We know that entities in general perform slower than tables but this specific entity appears to reach max throughput at around 9 records per second under perfect conditions. That's 540 Customers created in every 1 minute. If you had 1 million customers to import, that 30 hours to process that workload.