How to use a recurring Integration Endpoint for importing data
You are here
Database Inserts - Code Table Comparison
Database Inserts - Primary Table Comparison between dev box and Sandbox Environment
In a previous article, I wrote about the fastest ways to insert data but only looked at it from a development VM ( In Azure ) perspective. I thought it would be worthwhile to review this from a Microsoft-hosted Sandbox environment as well a (new) PPAC-hosted Unified Development Environment, or UDE, which used to be known as a UNO environment - Unified Operations. In the charts below, The "DEV' label is a standard Dev VM deployed in Azure with the defaults taken from LCS ( most importantly just 3 disks ). The "TST" label is for a standard Microsoft hosted Sandbox as a UAT environment available as part of the standard license offering. Lastly, the "UNO" label is for a new PPAC based development environment.
Single Insert
The explanation for this test can be found here. Here we have the sets of workloads on 3 different deployment topologies. Each topology was given the same workload using the code pattern linked earlier. Each topology was tested with a workload size of 1, 10, 250, and 1000. You can see the results below where larger number are considered better. The "sweet spot" for this workload was at around 250. We can also see a large difference between the dev environment and the sandbox environment which supports the assertion that if something is "fine" in dev, it may be slow in a sandbox environment. We can also see the new UDE / UNO environment is slower than a sandbox environment which supports the assertion that if something is "fine" in a UDE environment, it will be "fine" in a sandbox and production by extension.
Multi Insert
The explanation for this test can be found here. We have the same workloads and presentation as the Single Insert test.The "sweet" spot for this workload appears to be at above 250 and less than 1000 records. We can see the dev vm is much faster than the sandbox environment supporting the same assertion as above: Dev performance may be acceptable but Sandbox performance may not be. We can see that the UDE / UNO environment is again slower than the sandbox supporting the assertion that in performance is acceptable in a UDE / UNO environment, it will be acceptable in sandbox and production by extension.
Insert Record Set
The explanation for this test can be found here. The outcome of this testing is identical to the Multi Insert test above with nearly no significant throughput differences but the Multi Insert pattern is marginally faster in our synthethic workload.
RecordInsertList
The explanation for this test can be found here. The outcome of this testing is identical to the Multi Insert test above with nearly no significant throughput differences
RecordSortedList
The explanation for this test can be found here. The outcome of this testing is identical to the Record Insert List test above with nearly no significant throughput differences. The only call out is the RecordSortedList construct is expected to be marginally slower than a RecordInsertList for nearly all workloads because it has an implicit sort as part of its operations.
SysDAInsertObject
The explanation for this test can be found here. The outcome of this test is identical to the Multi Insert test above. I expected this to score overall higher than it did but it could have to do with our synthetic workload and fields used.
Query Insert InsertRecordset
The explanation for this test can be found here. The outcome of this test is identical to the Multi Insert test above.
General Reccomendations
In general, for this particular type of table, we're using CustGroup (Customer Groups) in our example, so long as you don't use the Single Insert pattern, linked above, you'll be fine. If you need the best overall performance, in general the Multi Insert pattern will work. The other options are also strong contenders to work with whatever you like best. I should call out these results are likely influenced by the type of table used and the very limited number of columns on the table. Additionally, Azure Dev VMs will likely give you false positives for performance metrics being achieved so either test in sandbox to ensure service levels will be able met or triple/quadruple performance metrics for a dev VM to hit to ensure expected performance in other environment types. Achieving acceptable results in a UDE is also a valid path forward.