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

When inserting data in F&O, there are several best practices on how to do this from MSFT. But for this specific type of workload, 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: https://www.atomicax.com/article/database-inserts-and-performance. In this series of tests, we'll be looking at table SalesTable.

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 tests, we're looking at a workload size of 1, 10, 250, and 1000 to see how the workload size changes performance characteristics. You can see as we perform more inserts, the overall throughput increases for the workload size. This is likely due to caching of some sort in SQL related to the transaction and/or query plan.

Multiple Insert

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

Just like the last graph, we can see that overall the workloads have more throughput as the workload size increases. This is different from the last workload in that we're creating only 1 transaction so we're seeing improved performance overall for nearly all instances. Also, its interesting that the workload size of 1000 appears to have worse performance than the workload at a size of 250 records. This is likely due to SQL downgrading the entire transaction from being in memory to being a disk based temporary table. This behavior is expected but something to keep an eye out for if the highest of performance metrics must be met for your workload.

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

This is very similar to the Multiple Insert pattern tested above. A record insert size of more than 1 and less than approximately 500 or so seems to be the sweet spot for this type of insert.

RecordSortedList

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

Similar to the RecordInsertList workload. Single record insert performance is slightly worse but about everything else remains the same.

SysDAInsertObject

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

Conclusion

For this table, SalesTable, and the given workloads, the Multiple Insert pattern appears to be the most performant by a small margin followed by the RecordInsertList pattern then RecordSortedList pattern. The Single Insert pattern is in a distant last place. However, overall the performance metrics are pretty dissapointing.