How to use Ado.Net to call table-valued arguments quickly

ado.net bulkinsert sqlbulkcopy sql-server sql-server-2008

Question

I need to efficiently send SQL Server 2008 tens of thousands of integers and dates from Ado.Net. Prior to SQL 2008, I used to put the numbers into images, which was a quick method. Some of my code was kindly used in Erland Sommarskog's essay SQL Server 2005's arrays and lists.

I used TVPs because we can now utilize them. I have this running on the client:

        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;

Although this code works, I don't believe it is effective. When I activated the Profiler, I realized right away that Ado.Net sends the server the following very inefficient SQL:

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

As a consequence, transmitting, parsing, and compilation use a significant amount of server CPU and network traffic. Exists a more effective method for using TVPs with Ado.Net?

1
7
6/13/2011 1:45:17 PM

Accepted Answer

Do you understand 5zzz? It is incredibly effective and versatile. Here is an illustration.

It was brought up as a substitute for TVP. For more interesting information regarding SQLBulkCopy in comparison to TVP, see here and here.

copying from the comments

2
5/23/2017 11:44:38 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow