Database Inserts – Primary Data Entity Comparison between dev box and sandbox environments

In the previous article on this topic, I talked about basic data insert performance under several scenarios under different considerations. This is a further expansion on the table by using a data entity for customers, rather than interacting with CustTable directly. We expect data entities to be slower than table buffers but how much slower? Also does the performance degradation get offset by the simplicity of using a data entity? Also in this article I’ll be providing shorter explanations and just calling out observations. Based on previous articles here, here, and here, we have established a pattern of performance by environment type – Azure Dev Vms are the fastest, UDEs are the slowest.

Single Insert

The explanation for this test can be found here. Comparing to this for this workload, data entities are slower and have the same performance characteristics for this type of workload. 

MultiInsert

The explanation for this test can be found here. Comparing to this for this workload, again using a data entity is slower. However, I didn’t expect the performance for this workload to be so poor. Its nearly as bad as the single insert pattern above and I can’t quite determine why. This is still marginally better than a single insert pattern.

RecordInsertListInsert

The explanation for this test can be found here. Reviewing this for this type of workload, a data entity is slower. One item of note is that although this is specifically using a set based operator, it was downgraded into a row based operation so we got the same performance as the multi-insert pattern.

RecordSortedInsertList

The explanation for this test can be found here. Similar to the last test, a data entity is slower. however, this specific workload did appear to have a minor advantage which is likely just due to variance at runtime and not anything specific to the entity or the RecordSortedList construct.

Conclusion

When using data entities, use of set based operations doesn’t appear to matter us much. It does appear to help a little with performance but the not a great deal like when working with table buffers.