title: Problems with table-value parameter performance
body: <p>I don't know whether this is an issue with how I'm using them or Microsoft's implementation, but SQL 2008 table-value parameters are painfully slow.</p>
  
  <p>Generally if I need to use a TVP it's because I've got lots of records - currently they appear to be unusably slow for anything more than the fewest records.</p>
  
  <p>I'm calling them in .Net like this:</p>
  
  <pre><code>// get the data
  DataTable data = GetData();
  
  com.CommandText = "sprocName"
  
  // create the table-value parameter
  var tvp = com.Parameters.AddWithValue("data", data);
  tvp.SqlDbType = SqlDbType.Structured;
  
  com.ExecuteNonQuery();
  </code></pre>
  
  <p>I ran profiler to see why, and the actual SQL statement is something like this:</p>
  
  <pre><code>declare @data table ...
  
  insert into @data ( ... fields ... ) values ( ... values ... )
  -- for each row
  insert into @data ( ... fields ... ) values ( ... values ... )
  
  sprocName(@data)
  </code></pre>
  
  <p>That's a really slow way to do it though.
  It would be much quicker if it did this instead:</p>
  
  <pre><code>insert into @data ( ... fields ... ) 
  values ( ... values ... ),
         ( ... values ... ),
         -- for each row
         ( ... values ... )
  </code></pre>
  
  <p>I'm not sure why it isn't using the newer, much quicker syntax. Or even whatever it does under the hood with <code>SqlBulkCopy</code>.</p>
  
  <p>The new syntax was added in SQL 2008, but then so are TVPs (I think).</p>
  
  <p>Is there some option to make it do this? Or something that I'm missing?  </p>