Database Inserts – Primary Table Comparison between dev box and Sandbox Environment

In a previous article, I wrote about the fastest ways in insert data but only looked at it from a development VM ( In Azure ) perspective. I thought it would be worth while 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. The name for the UDE/UNO environment changed int he middle of me collecting and reporting on this data so that graph still refer to the UDE environments as UNO environments. However, the core findings should remain valid. This set of tests compares the differences between creating a CustTable record across the different environment types using different methods of inserting data. If you read this article, you’ll see the same sort of trends in the data – just at different scales. The main comparision we’ll be making is a CustTable record with 50+ fields and multiple indices to a code table with 2 fields and only 1 index. As you would expect, doing more takes more time than doing less. However, this isn’t a direct comparison as CustTable has some extensions, CoC methods, and event handlers for data events so there are some additional things happening with a CustTable buffer that wouldn’t with a simple code table. However, this is a good review of what kind of performance we can extend with CustTable / Customers and concepts like it such as vendors, released products, etc.

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 a 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. This is exactly what we see with a code table. Most of this paragraph is copied from the article referenced earlier – but we can also note that CustTable inserts are slower – much slower. A code table insert in an T2 Sandbox can sustain an average of 100.8 records inserts per second while the same T2 can only handle 14.0 average record inserts per second. CustTable doing more work on more stuff ( indices, columns, etc ) and the result taking more time makes sense. However, I didn’t expect such a large difference. 14 CustTable records inserted per second is quite bad.

MultiInsert

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.

InsertRecordsetInsert

The explanation for this test can be found here. This test wasn’t conducted as you typically would just insert CustTable records by themselves using a set operator pulling from a table with values already pre-populated. 

RecordInsertListInsert

The explanation for this test can be found here. The outcome of this test is nearly identical as the Multi-Insert set of tests from earlier. This essentially tells us that opening a transaction, doing data manipulation, then closing the transaction is faster than open transaction, insert, close transaction then loop. 

RecordSortedInsertList

The explanation for this test can be found here. This outcome is very similar to the previous test but because of an implicit sort on the data construct, it is a little bit slower – but still not the slowest.

SysDAInsertObject

The explanation for this test can be found here. This test wasn’t conducted for the same reason InsertRecordSet / Insert_RecordSet test above.

QueryInsertInsertRecordsetInsert

The explanation for this test can be found here. This test wasn’t conducted for the same reason InsertRecordSet / Insert_RecordSet test above.

Conclusion

Inserting CustTable records are slower than code table records – and that makes sense because CustTable is doing a lot more. As a common theme amongst all testing, opening and closing transactions in a loop is always slow and there is always a faster way to insert a set of records larger than 1.