Table-valued parameters in SQL Server 2008 (Part 3)

Published 22 May 09 07:57 PM | MartinBell

In parts I and II of this series on TVP I wrote about the new table-valued parameter feature in SQL Server 2008 and how easy it is to use them compared to some of the methods you would have used in previous versions of SQL Server. I also mentioned the website created by Erland Sommarskog and how he has investigated the performance of the different methods used to pass and array to a stored procedure at http://www.sommarskog.se/arrays-in-sql-perftest.html. Ease of use is one reason to use TVPs, but as a DBA my focus is often on performance, and I wanted to try and answer the question of how TVPs perform compared to some of the methods you may have used previously and if it would be worthwhile converting legacy applications to use this new feature.

I was particularly interested in comparing TVPs against using XML as this was the easiest way to pass a structure (rather than a homogenous array) to a stored procedure, and I know this is a common solution being used. One scenario for this would be passing a set of tokens (“permission list”) relating to a user’s granted permissions. This “permission list” is often retrieved when a user logs onto the system, then when a stored procedure is called it is included in the parameters as an array.

For my test I wanted to see whether Table-valued parameters performed better than XML with a varying numbers of parameters. I also wanted to compare using integers and GUIDs, as GUIDs would make rows in the TVP wider, and may also be less efficient to compare.

For the integer value test, the setup script is TVPtest_int_setup.sql and for the GUID test use TVPtest_guid_setup.sql. These scripts create a database and populate the data needed to run the test in the table dbo.TestData. Executing the script TVPtest.sql runs the test, storing the results in the table dbo.TestResults. This table holds the durations for each section of the test.

For the table-valued parameter I declare a user-defined type called filtertable.

CREATE TYPE filtertable AS TABLE ( filterid uniqueidentifier  PRIMARY KEY)
GO

 I then created the four test procedures for each method of extracting the parameters

The procedure dbo.GetDetailsWithTVP uses a table-valued parameter. This parameter is simply joined to the dbo.TestData table to return the rows with the given ids.

CREATE PROCEDURE dbo.GetDetailsWithTVP ( @tvp filtertable READONLY )
AS
     SELECT t.id, t.name
     FROM dbo.TestData t
     JOIN @tvp f ON f.filterid = t.id
GO

The procedure dbo.GetDetailsWithXML uses the nodes method of the XML datatype parameter passed to shred the XML and join to the dbo.TestData id column.

CREATE PROCEDURE dbo.GetDetailsWithXML ( @xmlfilter xml )
AS
     SELECT t.id, t.nameFROM dbo.TestData t
    
JOIN @xmlfilter.nodes('/root/filter') AS f(filter) ON f.filter.value('@id', 'int') = t.id

GO

The procedure dbo.GetDetailsWithXMLExists uses the exist method of the XML datatype parameter passed to it to match with the given ids. In Example D for the “value() Method (xml Data Type)” topic in Books online it says “For performance reasons, instead of using the value() method in a predicate to compare with a relational value, use exist() with sql:column().” so I was interested to see if it did perform better.

CREATE PROCEDURE dbo.GetDetailsWithXMLExists ( @xmlfilter xml )
AS
     SELECT t.id, t.name
     FROM dbo.TestData t
     WHERE @xmlfilter.exist('/root/filter[@id = sql:column("t.id")]') = 1
GO

The procedure dbo.GetDetailsWithOpenXML uses OPENXML to shred the XML parameter and match with the given ids. This is a method that could have been used in SQL 2000.

CREATE PROCEDURE dbo.GetDetailsWithOpenXML ( @xmlfilter varchar(max) )
AS
   DECLARE @idoc  int,
           @err   int

   EXEC  @err = sp_xml_preparedocument @idoc OUTPUT, @xmlfilter
   SELECT @err = @@error + coalesce(@err, 4711)
   IF @err <> 0 RETURN @err

     SELECT t.id, t.name
     FROM dbo.TestData t
     JOIN OPENXML(@idoc, '/root/filter', 1)
              WITH (id int) AS s ON t.id = s.id

     EXEC sp_xml_removedocument @idoc
GO

MSXML was improved for SQL Server 2005 so I was interested how well OPENXML would perform.


When the test script is executed, each set of tests will be repeated 10 times, controlled by the value in @testrepeat. Within each set of tests I call each procedure 5 times (@numrepeats) with a given number of filters between 1 and 2000 determined by @runcount. For instance if @runcount is 7 then 25 filters are used, if it is 12 then 1000 are used. The number of filters is held in @num_filters.

The given number of rows is then inserted into the @filters table variable 

     INSERT INTO @filters ( filterid )
     SELECT TOP (@num_filters) id
     FROM dbo.TestData 
     ORDER BY NEWID();

I order by NEWID() to try to vary which GUIDs are returned. The xml to be passed to the procedure is created using the statement

SET @xmlstring = ( SELECT filterid AS [@id] FROM @filters FOR XML PATH('filter'), ROOT( 'root') ) ;

I convert this string to an XML variable so there isn’t an implicit conversions occurring when I call the procedures that take the XML variable as a parameter.

So for GUIDs if I had 3 filters my XML would be something like:

<root><filter id="EB4FB84B-E821-DE11-A008-001B38FE7298"/>
<filter id="6C5BB84B-E821-DE11-A008-001B38FE7298"/>
<filter id="315EB84B-E821-DE11-A008-001B38FE7298"/></root>

It’s then a matter of calling the procedures. I repeat the each procedure call 5 times to make sure that a time is recorded taking a start time and recording the difference into the dbo.TestResults table. I use nanoseconds as the precision of the datatime2(7) column is 100 nanoseconds. Check out my previous article on the new date and time features if you wish to know more about this.

   INSERT INTO dbo.TestResults ( num_Filters, num_repeats, Method, [nanoseconds] )
   SELECT @num_filters, @numrepeats, 'TVP', 
     DATEDIFF(ns,@startdatetime,SYSDATETIME()) ;

You may also notice that I have used other new SQL 2008 features in this script such as initialisation on declaration: 

   DECLARE @runcount int = 1;

and compound operators:

   SET @repeatcount += 1 ;

Results

Each time I ran the tests I got similar results. When I summarized the results, I got something like:

Integer

Average Differences Compared to TVPs

Method

Average

Maximum

Minimum

TVP

XML

OpenXML

TVP

129092307

481000000

0

XML

143292307

507000000

0

10.99988

OpenXML

159823076

797000000

14000000

23.80527

11.5364

XML Exists

534715384

838000000

458000000

314.2117

273.1641

234.5671

GUID

Average Differences Compared to TVPs

Method

Average

Maximum

Minimum

TVP

XML

OpenXML

TVP

184592307

526000000

0

XML

191476923

626000000

0

3.729633

XML Exists

216838461

627000000

49000000

17.46885

13.24522

OpenXML

218915384

865000000

17000000

18.59399

14.3299

0.95782

Int to GUID comparison

Method

TVP

42.992492

XML

33.626799

OpenXML

35.674063

XML Exists

-59.05946

Which shows a significant improvement using TVPs compared to one of the XML methods especially with Integers, although with GUID filters you would need to make your own decision if having < 4% improvement is justifiable over code changes and everything that would involve, but if you are still using OPENXML or have followed the advice in Books Online and used the exist method, it should certainly be worth considering.

If you look at the results in more detail and consider the number of filters you are likely to use, things are not necessary so clear cut, and the summary aggregation hides the fact that TVPs were not always fastest for a given set of parameters. For instance, OPENXML didn’t fair too well overall, although it was the best performer with 25 to 250 integer filters and 5 to 25 GUID filters. The XML method came out top for 500 integer and 50 to 250 GUID filters. What is significant is the performance of using integers compared to GUIDs for all methods apart from using OPENXML.

Looking at the actual query plans revealed that using the TVP and the XML nodes method both allowed a clustered index seek on the dbo.TestData table. This meant there were 6 logical reads on the table instead of 163 which is obviously beneficial to these methods.

Actual Query Plan TVP 
Figure 1 Actual Query Plan TVP

Actual Query Plan XML (nodes)
Figure 2 Actual Query Plan XML (nodes)

Actual Query Plan XML (exist)
Figure 3 Actual Query Plan XML (exist)

Actual Query Plan OPENXML

Figure 4 Actual Query Plan OPENXML

These are of course one set of results on my server. If you have your own scenario that may benefit from TVPs, try out your own tests and see if they will be beneficial.

Comments

# Table-valued parameters in SQL Server 2008 (Part 2) - Martin Bell UK SQL Server MVP said on May 22, 2009 09:02 PM:

Pingback from  Table-valued parameters in SQL Server 2008 (Part 2) - Martin Bell UK SQL Server MVP

# Table-valued parameters in SQL Server 2008 (Part 1) - Martin Bell UK SQL Server MVP said on May 22, 2009 09:28 PM:

Pingback from  Table-valued parameters in SQL Server 2008 (Part 1) - Martin Bell UK SQL Server MVP

# Dew Drop - May 23, 2009 | Alvin Ashcraft's Morning Dew said on May 24, 2009 03:19 AM:

Pingback from  Dew Drop - May 23, 2009 | Alvin Ashcraft's Morning Dew

# Dark Under Eye said on March 10, 2011 08:37 PM:

Pingback from  Dark Under Eye

# Dark Under Eye said on March 10, 2011 08:37 PM:

Pingback from  Dark Under Eye

This Blog

SQL Blogs

Syndication