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.
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.
I'm calling them in .Net like this:
// 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();
I ran profiler to see why, and the actual SQL statement is something like this:
declare @data table ...
insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )
sprocName(@data)
That's a really slow way to do it though. It would be much quicker if it did this instead:
insert into @data ( ... fields ... )
values ( ... values ... ),
( ... values ... ),
-- for each row
( ... values ... )
I'm not sure why it isn't using the newer, much quicker syntax. Or even whatever it does under the hood with SqlBulkCopy
.
The new syntax was added in SQL 2008, but then so are TVPs (I think).
Is there some option to make it do this? Or something that I'm missing?