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. 

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 are looking at a workload size of 1, 10, 250, and 1000. We can see that as the workload size goes up from 1 to 250 the throughput in records per second goes up. At a workload size of 1000, we can see a drop in throughput. This is different than using the underlying table for this entity in that we're using an entity that contains only one data source - SalesPool. So when comparing this to the direct interactions with the table, the table is much faster. Interacting with the table directly is much faster.

Multiple Insert

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

Similar to the last test, we have a workload size of 1, 10, 250, and 1000 and we see a similar throughput pattern. The peak performance in records per second is around a workload size of 250 records. This pattern performs better than then single insert pattern when looking at data entity performance but as seen in this post, direct table interactions are much faster. 

Insert_RecordSet

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

Just like the last test, we have the same workload sizes. However, here we can see how record created per second by workload size starts to skyrocket when using a set based operator. This suggests that there is a fixed amount of time to create a transaction in SQL but once that is taken, whether we're creating 1 to 1000 records, the creation of the record isn't taking much time, by comparison. This looks to scale very well for large data insert workloads. 

Query Insert_RecordSet

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

With the Query based InsertRecordset pattern, we see another instance where we can see some scaling in favor of performance. This scales for even better performance than a standard Insert_RecordSet command does by a small but noticable margin at the 250 and 1000 workload size points.

RecordInsertList

This threw an error stating that the view for the data entity was read only. It appears using a RecordInsertList for a view with a single table isn't supported for some reason.

RecordSortedInsertList

Similar to the RecordInsertList, this threw the same error.

SysDAInsertObject

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

This is another set based operator that shows another benefit to scaling especially with large workload size - but any workload size sees a benefit. This is the most performant way to insert any data using a data entity, period.

Conclusions

This was a bit surprising for me. I expected Insert_RecordSet to be the most performant but that is not the case. Using the SysDA API for Inserts was by a fair margin the fastest way to insert data when using a data entity. Using Insert_Recordset or the Query based equivalent of that are also fine but SysDA is the clear winner. Using a single or multiple insert pattern are the slowest by a significant margin.