locked
Using ADO.NET SqlDbType.Structured TVP (Table Valued Parameters) causes SQL Compilation for every call RRS feed

  • Question

  • 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 
     (
      [Id] ASC
     ) WITH (IGNORE_DUP_KEY = OFF)
    )
    GO
    
    CREATE PROCEDURE testProc
      @itemIds AS BigIntListType READONLY
    AS
      SELECT col1, col2 FROM table1
      JOIN @itemIds ON table1.id = @itemIds.Id
    GO
    
    

    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
    
    

    Take a note of Profiler and Perfmon.  You will see a no SQL compilations for each batch execution - this is good!

    4. Now create a C#/VB.Net app using the 3.5 framework and call the proc from ADO.Net (snippet below) ...

          Dim command As New SqlCommand("testProc", connection)
          command.CommandType = CommandType.StoredProcedure
          Dim parameter As New SqlParameter("@itemIds", SqlDbType.Structured)
          parameter.TypeName = "BigIntListType"
          parameter.Value = someSqlDataTable
          command.Parameters.Add(parameter)
          Return command.ExecuteReader()

    Take a note of Profiler and Perfmon.  You will see a SQL compilation for each batch execution - this is bad!

    Is there any way to improve this behaviour using ADO.Net?  My application is a high performance application requiring 10's of thousands of SQL batches per second.  I need to eliminate any unnecessary compilations.  Compilations do not take place from Management Studio, only from ADO.Net.

    Thanks

    • Moved by Dan Benediktson Friday, October 8, 2010 4:44 PM query compilation questions probably belong in Database Engine forum, not Data Access forum (From:SQL Server Data Access)
    Tuesday, June 8, 2010 1:01 PM

All replies

  • I'm running into this exact problem. Perfmon shows SQLCompilations/sec for each call to a stored procedure where we use a TVP.

    I've also made sure that all the latest updates for SQLServer 2008 were installed.

    Tuesday, October 5, 2010 12:56 AM
  • Thursday, December 23, 2010 9:59 PM
  • The 3rd option suggested in Bob's Blog seems to be the most efficient way at least as of now. i.e. Create a single batch of statements which declare a table type, initializes it and passes it as a parameter to the required SP / UDF.
    Sr. Solution Architect HP
    • Proposed as answer by AtulK Friday, January 28, 2011 6:26 AM
    Friday, January 28, 2011 6:25 AM