Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
From the author of

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.

  • + Share This
  • 🔖 Save To Your Account