As promised, today I took a look at the performance of bulk inserts using XML and Table-Valued Parameters. I also compared it against singleton inserts to show the value in the bulk-insert approach.
My tests were pretty simple: insert 100 records using each method. Each test was executed 10 times to ensure consistency. The duration was recorded in microseconds.
The goal was to compare the performance of the inserts. Because I was executing this entire test within SQL Server, I had to isolate only the actual insert transactions and ignore everything else, such as the loading of the data; that work would normally be performed by the calling application.
So without further ado… screenshots of the Profiler traces: (click to enlarge)
Summary
Method | Avg CPU | Avg Reads | Avg Writes | Avg Duration (micro) |
Singleton Method | 3 | 202 | 0 | 13378 |
XML Method | 0 | 222 | 0 | 3124 |
TVP Method | 1 | 207 | 0 | 780 |
As expected, both the XML and the TVP method performed significantly better than the single-insert method. As hoped, the table-valued parameter arguably performed the best of all 3.
Pingback: SQL Server Blog by Jason Massie
Pingback: SQL Server Blog by Jason Massie
Pingback: Metadata for Table Valued Parameters « SQL Fool