Performance Comparison of Singleton, XML, and TVP Inserts

November 13, 2008 by Michelle Ufford
Filed under: Performance & Tuning, SQL 2008, SQL Tips 

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)

TVP

Single Insert Method

TVP

XML Method

TVP

Table-Valued Parameter Method

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.

Tags: , ,

Comments

2 Comments on Performance Comparison of Singleton, XML, and TVP Inserts

  1. SQL Server Blog by Jason Massie on Sun, 16th Nov 2008 7:25 pm

    2. New\Newish SQL Blogs(at least to me)…

    …

  2. SQL Server Blog by Jason Massie on Sun, 7th Jun 2009 12:13 pm

    3. [...] Recent Highlight: Performance Comparison of Singleton, XML, and TVP Inserts [...]

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.