When calling a stored procedure with a table valued parameter from ADO.Net, each call requires a SQL Compilation. In an application that makes this call thousands of times per second, this causes SQL Compilations thousands of times per second leading
to degraded performance.
When calling the same stored procedure directly from Management Studio, no SQL Compilations are performed.
Steps to reproduce ...
1. Create the following User Defined Type and Stored Procedure (SQL2008 required) ...
CREATE TYPE [dbo].[BigIntListType] AS TABLE(
[Id] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
) WITH (IGNORE_DUP_KEY = OFF)
CREATE PROCEDURE testProc
@itemIds AS BigIntListType READONLY
SELECT col1, col2 FROM table1
JOIN @itemIds ON table1.id = @itemIds.Id
2. Start SQL Profiler and capture "Showplan XML for Query Compile". Start Perfmon and add "SQL Compilations / Second"
3. Run the following code from Management Studio as many times as possible ...
declare @p2 dbo.BigIntListType
insert into @p2 values(601)
insert into @p2 values(1)
exec testProc @itemIds = @p2