This is my first post of two (maybe more, we’ll see 🙂 about some of the performance improvements you will experience when upgrading to SQL Server 2008 and Visual Studio 2008 SP1.
In this one we will be taking TVP (Table Valued Parameters) for a test ride.
Let’s compare the time it takes to insert a large number of rows into a table using the following methods:
- SqlDataAdapter (with different values of the UpdateBatchSize property)
- Multiple Inserts (delimited by semicolons)
- Packaging the rows to insert as XML and calling a stored procedure that receives it.
- A stored procedure with a Table Valued Parameter
Using the code:
- You must be using SQL Server 2008 and Visual Studio 2008 SP1.
- Download the code from the bottom of this post.
- Run the Setup.sql against the server.
- Modify the connection string in the project Settings.
- The code should be self-explanatory, but let me know if not.
Here are the results of just one run on my laptop for the insertion of 10,000 rows.
TVP performs better than DataAdapter generated updates, and better than the relatively fast method of sending tables as XML.
The results aren’t entirely consistent each run, but TVP wins every time : )
Comments are welcome