November 17, 2011 12 Comments
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 interesting case of TVPs and plan compilation (Bob Beauchemin’s Blog)
- TVPs and plan compilation – the reprise (Bob Beauchemin’s Blog)
- Using table-valued parameters from clients cause recompiles with each use (SQL Connect Item submitted by Bob)