More on Table-valued Parameters (TVP’s)

While finalising some slides for my presentation at the Canberra .Net User Group on Using XML and TVP’s to Transport Relational Data, I stumbled across a couple of posts by Bob Beauchemin relating to how TVP’s cause plan recompiles when called from ADO.NET.  Given that I published a post back in May 2011 about using TVP’s with ADO.NET, I thought I’d provide a brief overview of what Bob is referring to (you can find Bob’s original content links below).


Let’s assume we have a Table Type called udtt_Passenger which contains 1 column of type BIGINT and a stored procedure called usp_AddPassenger which has one parameter of type udtt_Passenger.  Let’s also assume that we have a SQL Server Table Variable and an ADO.NET Datatable each containing 5 rows of data.  If we were to call our stored procedure from SSMS (passing in our SQL Server Table Variable) and ADO.NET (passing in our ADO.NET Datatable), SQL Profiler would report the following:

declare @t [Person].[udtt_Passenger]
insert @t values(1)
insert @t values(2)
insert @t values(3)
insert @t values(4)
insert @t values(5)
exec usp_AddPassenger @t

Although both calls result in the exact same code being reported in SQL Profiler, it’s the mechanism used to do the call that’s different.

When SSMS executes this code, it does so via a SQLStmt call so the code is executed as a batch i.e. INSERTS + EXEC therefore a query plan for the batch and stored procedure is cached and reused .  When ADO.NET executes this code, it does so via a RPC call therefore only the query plan for the stored procedure is cached, not the INSERTS.  It is this difference that causes plan compiles to occur for each INSERT statement when using ADO.NET.

Anyway, to cut a long story short, this is by design and according to Microsoft, has minimal impact on performance (SQL Connect Item submitted by Bob).


The SQL Server 2012 Special Ops Tour

Microsoft is teaming with PASS to bring you the SQL Server 2012 Special Ops Tour, a cross-country, technical training roadshow that will take them to 12 cities across America.  Help shape the course of action for this tour by completing this Survey.

Wonder if Microsoft Australia will do something similar here Smile

%d bloggers like this: