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

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.

• Using Table-valued parameters (TVP) in SQL Server 2008
• Table-valued parameters (TVP) SQL Server 2008 and Net C

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.

• Scenario 2 – 10 rows
• Scenario 3 – 100 rows

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.

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

• Extend this test to other DML operations – SELECT, UPDATE, DELETE
• Read more information on Table-valued parameters
• Learn more about SQL Server Stored Procedures

Ben Snaidero

Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.

• MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017