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. — user166390
– user166390, Commented May 25, 2012 at 15:29
(Many SqlCommand inserts are is plenty fast when transactions are used correctly.) — user166390
– user166390, Commented May 25, 2012 at 15:37

Community · Accepted Answer · 2017-05-23 11:53:49Z

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.

Marc Gravell · Accepted Answer · 2012-05-25 15:36:05Z

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.

@Blam either type "dapper" into nuget, or: code.google.com/p/dapper-dot-net (it is the custom stack we use for high-perf data access here at stackoverflow)

Community · Accepted Answer · 2017-05-23 12:09:29Z

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, 2017 at 12:09

CommunityBot

11 silver badge

answered May 25, 2012 at 15:42

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

3 Comments

Robb_2015 Over a year ago

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 [stackoverflow.com/questions/34365407/… in which i try to use it for mutiple deletes by record id.

Robb_2015 Over a year ago

"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 - lennilobel.wordpress.com/2009/07/29/…

paparazzo Over a year ago

@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.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Linked

Related