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 "Transaction Data Entity"; "Sales order headers V2" / SalesOrderHeaderV2Entity 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 other charts, from left to right we have a workload size of 1, 10, 250, and 1000. For a single insert pattern workload, we can see that in general performance is lackluster but improves overall as the workload size grows with some fall off near the top, between 250 and 1000. 

Mutli Insert

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

Similar to the last chart, performance is lackluster but improved compared to the single insert pattern.

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....

Using a RecordInsertList for different workload sizes gives us some surprising results. For workload sizes of 1 and 10, we can see that throughput in terms of records records per second is fairly high, compared to other options in the article but also compared to a workload size of 250 or 1000. I'm partially guessing on this but I think this suggests that in SQL Server, small set sizes operation in memory then commit to disk when the transaction commits. However, at some point, the workload gets too large for SQL to handle it in memory and it "drops down" to being disk based inside SQL Server, which is slower. For SQL people reading this, I think this is the difference between an @ temp table and a # temp table. It appears that the primary take away here is that if you are using a data entity to insert data in x++, use a RecordInsertList.

RecordSortedList

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

Using a RecordSortedList also gave some interesting results. This is nearly identical to the multiInsert pattern above but also, this is a set operator just like RecordInsertList but we're getting very different results as compared to that. I'd expect to see similar throughput gains for a workload size of 1 and 10 but we're not seeing them here. I think this supports my theory about the difference between in-memory temp tables as compared to disk-backed temp tables. RecordSortedList have an implicit sort as apart of their usage.

SysDAInsertObject

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

Conclusion

Overall, creating Sales Order Headers is slow, regardless of how it is done. However, the usage of a RecordInsertList for set sizes of 10 or smaller appear to have a special sweet spot for this type of workload. I plan on confirming this further and confirming that all methods, data sources, and related code and data points execute or get created. It does seem odd that 1 specific workload type is much faster than all other so I'm wondering if this is a bug or something like that. Other than that, never use a single Insert pattern as that is the slowest. In most instances, a multi insert pattern will be just fine with using a RecordInsertList being the most performant for a specific size of workload.