How to efficiently invoke table-valued parameters from Ado.Net

Question

I need to efficiently submit tens of thousands of numbers and dates from Ado.Net to SQl Server 2008. Back in the days, before SQL 2008, I was packing those numbers in an image, which was quite fast. Erland Sommarskog was kind enough to include some of my code in his article Arrays and Lists in SQL Server 2005

Because now we can use TVPs, I tried them out. On the client, I am running this:

        dataTable = new DataTable();
        dataTable.Columns.Add("Date", typeof(DateTime));
        dataTable.Columns.Add("Price", typeof(decimal));
        dataTable.Rows.Add(new object[] { someDate, somePrice });
            command.CommandText = "Writers.SavePrices";
            command.CommandType = CommandType.StoredProcedure;
            var param = command.Parameters.AddWithValue("@Prices", dataTable);
            param.SqlDbType = SqlDbType.Structured;

This code works, but I don't think it is efficient. I fired up the Profiler, and I immediately saw that Ado.Net issues the following highly inefficient SQL being sent to the server:

DECLARE @Prices TABLE(...)
INSERT INTO @Prices(...)VALUES(...)
EXEC Writers.SavePrices @Prices=@Prices

As a result, a lot of network bandwidth and CPU on the server is wasted on sending, parsing, and compiling. Is there a more efficient way to use TVPs with Ado.Net?

Do you know [SqlBulkCopy](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx)? It's very efficient and flexible. [Here](http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server) is an example. — Tim Schmelter, Jun 10 '11 at 21:40
@Tim: sure, I can use SqlBulkCopy to pour my data into a staging table. However, i cannot figure out a way to have SqlBulkCopy and TVPs work together. — A-K, Jun 10 '11 at 23:22
I mentioned it as an alternative to TVP. See [here](http://stackoverflow.com/questions/2149897/performance-of-bcp-bulk-insert-vs-table-valued-parameters) and [here](http://stackoverflow.com/questions/2149897/performance-of-bcp-bulk-insert-vs-table-valued-parameters) for more interesting infos about SQLBulkCopy compared to TVP. — Tim Schmelter, Jun 10 '11 at 23:27
@Tim: can you copy your comment into an answer, so that I can accept it? Apparently there is no better way. — A-K, Jun 13 '11 at 15:27
Is ADO.NET sending tens of thousands of `INSERT` statements (one `INSERT` for each row in the TVP) as described in [this blog post](http://sqlskills.com/BLOGS/BOBB/post/The-interesting-case-of-TVPs-and-plan-compilation.aspx)? — Nick Chammas, Feb 07 '12 at 20:30
@NickChammas: this blog post is precisely what the Profiler is telling me: one INSERT per row. — A-K, Feb 08 '12 at 02:41

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

Do you know SqlBulkCopy? It's very efficient and flexible. Here is an example.

I mentioned it as an alternative to TVP. See here and here for more interesting infos about SQLBulkCopy compared to TVP.

(copied from comments)

edited May 23 '17 at 11:44

Community

1
1

answered Jun 13 '11 at 17:38

Tim Schmelter

411,418
61
614
859

How to efficiently invoke table-valued parameters from Ado.Net

1 Answers1