Compare SQL Server Stored Procedure Performance for Table Valued Parameters vs Multiple Variables

By:   |   Comments (2)   |   Related: > Table Valued Parameters


Introduced with the release of SQL Server 2008 was a new feature called Table-valued parameters (TVP) which allowed the programmer to pass multiple rows and columns of data to a stored procedure with a single call. This eliminates the need for many calls to the same stored procedure when we are loading large amounts of data. In this tip we will compare the performance of this feature against the old way of loading the data into individual variables and making multiple stored procedure calls.


In case you are not familiar with this feature you can get a good overview from the following tips which demonstrate, with examples, how you can create and load a table variable and then use this as a parameter for the stored procedure.

Sample Table Setup

In order to perform this test with as few variables as possible let's create a simple test table that we will leave as a heap with no indexes. This will allow us to load data with no overhead other than the variables we are trying to test. We'll also need to create a user defined table type that we can use as a parameter to one of our stored procedures. The other stored procedure will just use regular variables. Below is the T-SQL to create this test table and all the associated objects mentioned above.

-- Create test table
CREATE TABLE test (col1 int, 
                   col2 int, 
                   col3 varchar(10),
                   col4 varchar(10), 
                   col5 varchar(10), 
                   col6 float, 
                   col7 float)

-- Create user defined table type
CREATE TYPE [dbo].[test_1] 
 AS TABLE (col1 int, 
           col3 varchar(10), 
           col6 float

-- Create stored procedure that uses regular variables
CREATE PROCEDURE [dbo].[spInsertTest_regularvariable]  @vint int,@vvarchar varchar(10),@vfloat float 

      INSERT INTO test VALUES (@vint,@vint,@vvarchar,@vvarchar,@vvarchar,@vfloat,@vfloat)
   END TRY  

-- Create stored procedure that uses table variable
CREATE PROCEDURE [dbo].[spInsertTest_tablevariable]  @test test_1 READONLY 

      INSERT INTO test ( col1,col2,col3,col4,col5,col6,col7 ) 
      SELECT col1,col1,col3,col3,col3,col6,col6  FROM @test
   END TRY  

SQL Server Table-Valued Parameters Performance test

For this test we are going to look at loading a few different sized data sets so we can see if there is any difference in performance based on this factor. Even though we expect to see the performance improvement when loading multiple records, let's first get a comparison while just inserting just a single row. Following that we will compare loading 10 and then 100 rows.

Here is the T-SQL for the single record insert. As mentioned, the first step loads the data into either a TVP or a set of regular variables and the subsequent step executes the stored procedure.

-- regular variable single insert
declare @vint int,@vvarchar varchar(10),@vfloat float
select @vint=23,@vvarchar='abcdefg',@vfloat=3.34334
exec spInsertTest_regularvariable @vint,@vvarchar,@vfloat

-- table variable single insert
declare @vtesttable test_1
insert into @vtesttable values (23,'abcdefg',3.34334)
exec spInsertTest_tablevariable @vtesttable

In the interest of space I'll provide the T-SQL for the second two scenarios in a script. They follow the exact same format as the above example with one small difference I wanted to point out. In the case where we use regular variables as parameters we load the variables before each call to the stored procedure which results in many calls to the stored procedure to perform the inserts. Notice that in the TVP example we simply load all the data into the TVP and make one call to the stored procedure. Here are the scripts for the second two scenarios.

SQL Server Performance Analysis

Now let's execute all of the above scripts and use SQL Profiler to capture the performance metrics. For this test we will look at the usual performance metrics: CPU, Reads, Writes and Duration. Here are the results.

Single Row Scenario
Test Scenario CPU (ms) Reads Writes Duration (ms)
Regular Parameters 0 29 0 1
Table-valued Parameter 0 254 2 4

10 Row Scenario
Test Scenario CPU (ms) Reads Writes Duration (ms)
Regular Parameters 0 43 0 56
Table-valued Parameter 0 292 2 8

100 Row Scenario
Test Scenario CPU (ms) Reads Writes Duration (ms)
Regular Parameters 15 224 0 432
Table-valued Parameter 16 743 2 214

Looking at the results above there are some interesting things we've proven with this test. The first thing we showed was that as expected as we loaded more data, the TVP scenario started to run faster than the regular variable scenario. Interestingly for the single record scenario I would have thought they would be almost identical, but in this case the regular variable scenario outperformed the TVP scenario. The other interesting thing I took from this test was that although the TVP scenario ran faster when loading multiple records it did have to perform more reads than the stored procedure using regular variables. As always it's best to test in your environment with your own data to verify which approach is best suited for your application.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, February 6, 2018 - 1:26:41 PM - Jason Back To Top (75115)

Great article.  Company software architect and I have been discussing this, only we had an additional twist which was to make use of an OUTPUT clause to capture the change and record it.  Fascinating results.  One thing I wasn't clear on was what version of SQL Server you used for this.  The article only mentions it was a new feature in 2008.


Tuesday, May 17, 2016 - 9:16:26 AM - Paul Back To Top (41496)

This is a good apples to apples test and perhaps we can expand on the testing to include a strength of table variables, which is to insert the entire 10 or 100 records at one time. i.e. insert the 100 records into the table variable and then call the insert proc one time.


get free sql tips
agree to terms