Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
A Few Tweaks
The test set is pretty untuned, but I'd like to mention a few small tweaks that proved to work well:
Even though you saw that the INSERT throughput for the ordinary GUID case wasn't good, it would have been even worse if I had used clustered primary keys in that case.
Each INSERT of an order is wrapped in an explicit transaction. That is, I wrote 1 row to ordermaster and 10 rows to orderdetail in each transaction. Writing many orders in each transaction would have given a shorter processing time, but in an Online Transaction Processing (OLTP) application, it's often natural to have one transaction per order.
I ran a DBCC DBREINDEX ('tablename', '', 90) before each performance test. This is very important for the GUID case but not that important for the INTEGER and COMB cases. I also did an UPDATE STATISTICS before I executed the second test, and I recompiled the stored procedures.
The orderdetail.ordermaster_id is indexed. Otherwise, Test C will probably run forever.
I did a few other things between tests to get as fair tests as possible. I ran a backup of the database after the massive INSERTs to all the tables before I collected my metrics. That way, I emptied the transaction log. I also stopped SQL Server between each test case. And, of course, I ran each test case several times and skipped extreme values before finding typical values.