Is it better to pass large inserts to SQL Server as a table valued parameter, or as a string insert statement?

Question

I am writing a .NET application that writes data to SQL Server 2008r2. I have two options for inserting the data, either I can create a large string insert statement, and send it as a text command, or I can collect the data in a .NET DataTable, and pass it as a table valued parameter. What are the benefits and costs of each method?

(I am omitting a good deal of code since I am just asking about the relative benefits, not the specific syntax)

e.g.:

Option 1:

    string insert = @"insert into MyTable (id, val) values
        ( 1, 'a'),(2,'b'),(3,'c'),(4,'d');"

Option 2:

    DataTable dt = new DataTable();
    dt.Columns.Add("id", typeof(int));
    dt.Columns.Add("val", typeof(string));
    ....
    create procedure uspMyProc 
                    @tt ttMyTableType readonly
                as
                begin
                    insert into TestTable1 (id, strValue)
                    select myId, myVal from @tt;
                end"

Thanks for any help.

Option #3. Use SqlCommand. The structure can be dynamic (*if and only if needed*), while keeping the data parametrized. — , May 25 '12 at 15:29
(Many SqlCommand inserts are is plenty fast when transactions are used correctly.) — , May 25 '12 at 15:37

score 7 · Accepted Answer · edited May 23 '17 at 11:53

Option 3: In the first instance I would populate the insert stored procedure with one insert statement's worth of parameters and call it multiple times in a loop from the C# code:

Option 4: If you truly have lots of rows to insert, perhaps you need to look into the SqlBulkCopy class. It consumes either DataTable, DataRow or an IDataReader. You can make an IDataReader from a list of objects using some custom code, a question of this ilk is asked here:

Get an IDataReader from a typed List

I would say it depends.

If you really want to pass many rows of parameters in tabular form, for whatever reason, use a table valued parameter - that's what it's there for.

I have seen Option 1 - some generic DAL code would script out a SQL "batch" of commands to run. It worked, but didn't give any defence against injection attacks. Parameterised SQL does.

All that said, I would favour calling the insert sproc once for each row to be inserted from code - the calls will be fully parameterised and performance is fine. If performance becomes a problem I would favour Option 4.

Thank you. I was not aware of the SqlBulkCopy class. I think that is exactly what I am looking for. — Sako73, May 25 '12 at 17:39

score 5 · Answer 2 · answered May 25 '12 at 15:36

5

How big is big? If it is huge, nothing beats SqlBulkCopy. I've actually found TVP performance disappointing. For query plan re-use, I'm a fan of parameterised and massively-reused statements. Dapper can help with this, allowing you to pass a list of objects to a query - it will then add in the named parameters per object by member name, at many thousands of operations per second. For example:

conn.Execute(
    "insert foo (Id,Name) values (@Id,@Name)",
    listOfObjects);

This will iterate the list and use .Id and .Name from each object in turn to execute the query.

answered May 25 '12 at 15:36

Marc Gravell

1,026,079
266
2,566
2,900

Please provide a link to Drapper – paparazzo May 25 '12 at 15:51
@Blam either type "dapper" into nuget, or: http://code.google.com/p/dapper-dot-net/ (it is the custom stack we use for high-perf data access here at stackoverflow) – Marc Gravell May 25 '12 at 16:02

score 4 · Answer 3 · edited May 23 '17 at 12:09

4

Values is limited to 1000

And values appears to have some performance issues

Insert Performance Issues With Multiple Values

I use TVP for inserting thousand of rows and it works great for me. I use a List collection as the TVP source as DataTable has more overhead. Insert the rows sorted by the PK if you can.

With that said I am going to try out the answer from Marc Gravell.

JNK has a general distrust of TVP.

edited May 23 '17 at 12:09

Community

1
1

answered May 25 '12 at 15:42

paparazzo

44,497
23
105
176

are you sure about `DataTable` and overhead vs Enumerable type and each record with a round trip from c# to sqlserver on every record? (i reached this question only cause i resarch this issue) for my question [http://stackoverflow.com/questions/34365407/c-sharp-sql-server-best-way-to-delete-multiple-rows-in-one-go-using-a-stor#34366719] in which i try to use it for mutiple deletes by record id. – Robb_2015 Dec 20 '15 at 02:17
_"The data is structured as a strongly-typed set of rows on the client, and winds up as a strongly-typed set of rows on the server in a single round-trip"_ from - https://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/ – Robb_2015 Dec 20 '15 at 02:28
@Robb_2015 Are you sure you read my answer? Values clearly addresses Option 1:. As for Option2 I state it (TVP) works great. No where in my answer do I assert a TVP is not stronly typed nor that is results in multiple round trips. – paparazzo Dec 20 '15 at 16:09

Is it better to pass large inserts to SQL Server as a table valued parameter, or as a string insert statement?

3 Answers3