How to use a recurring Integration Endpoint for importing data
You are here
Database Inserts - Transaction Table Comparison
Database Inserts - Transaction Table Comparison
In this article, we'll go through various performance related tests I ran against the table SalesTable. In the past, here and , we ran tests against a custom code table then also CustTable. Each table had a different usage profile meaning a different number of columns, code behind it, indices, extensions, event handles, and delegates. Using those two articles, we could see as we ask X++ to do more stuff, it slows down. That makes sense. However, as we get closer to a table that changes rapidly, what kind of performance would we expect to see? We can review the results below under lots of different scenarios. I'd half expect poor performance because of all the different scenarios the table may be used for but also expect that because it's such an important and widely used table, it would be performance-tuned such that it performs very well, regardless of how it is used.
Single Insert
The explanation for this test can be found here. Referring to the two articles referenced above, we have the same basic shape of the curves on our data points. However, we have the T2 environment performing worse than a dev or UDE environment. This doesn't make sense to me so there must be something behind the scenes happening that I'm unaware of.
MultiInsert
The explanation for this test can be found here. Here we see what we would expect again referencing the other articles above. Here we see the performance numbers we'd expect; Dev, T2 then UDE. As you can see, under best case scenarios, in a T2 sandbox environment we can its an average of 9.3 Sales Order Headers created per second - or around 33.5K per hour.
InsertRecordsetInsert
The explanation for this test can be found here. This test wasn't conducted as you'd have to have records started somewhere else in order to insert them with an insert_recordSet.
RecordInsertListInsert
The explanation for this test can be found here. This test is interesting as we're using a set based operation in an attempt to get better performance. However, we're not seeing any performance uplift likely because of insert downgrades. So while we intended to get some performance improvements by using a set operator, no such luck. We actually get slightly worse performance using this pattern rather than inserting salesTable records in a loop.
RecordSortedInsertList
The explanation for this test can be found here. This set of tests falls pray to the same issue as a RecortInsertList above. Insert's get a downgrade and we get no performance uplift like we were expecting.
SysDAInsertObject
The explanation for this test can be found here. This wasn't tested for the same reason as the Insert_recordSet test above.
QueryInsertInsertRecordsetInsert
The explanation for this test can be found here. This wasn't tested for the same reason as the Insert_recordSet test above.
Conclusion
Similar to related tests and workloads, we can see that the dev VM is the fastest of the topologies types and using a "single insert" pattern is a a guaranteed way to get the worst performance possible.