Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (1)   |   Related Tips: More > Table Valued Parameters

Attend these FREE MSSQLTips webcasts >> click to register


Problem

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.

Solution

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
)
GO

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

      INSERT INTO test VALUES (@vint,@vint,@vvarchar,@vvarchar,@vvarchar,@vfloat,@vfloat)
  
      COMMIT TRAN  
   END TRY  
   BEGIN CATCH
      ROLLBACK TRAN
   END CATCH 
END 
GO

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

      INSERT INTO test ( col1,col2,col3,col4,col5,col6,col7 ) 
      SELECT col1,col1,col3,col3,col3,col6,col6  FROM @test
  
      COMMIT TRAN  
   END TRY  
   BEGIN CATCH
      ROLLBACK TRAN
   END CATCH 
END 
GO

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
go

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

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


Last Update:


signup button

next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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.

 


Learn more about SQL Server tools