将大型插入作为表值参数或作为字符串插入语句传递给SQL Server是否更好？

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?

我正在编写一个将数据写入SQL Server 2008r2的.NET应用程序。我有两个插入数据的选项，要么我可以创建一个大的字符串插入语句，并将其作为文本命令发送，或者我可以在.NET DataTable中收集数据，并将其作为表值参数传递。每种方法的好处和成本是什么？

(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:

选项1：

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

Option 2:

选项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.

谢谢你的帮助。

3 个解决方案

#1

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:

选项3：在第一个实例中，我将使用一个insert语句的参数填充插入存储过程，并在C＃代码的循环中多次调用它：

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:

选项4：如果您确实需要插入大量行，可能需要查看SqlBulkCopy类。它使用DataTable，DataRow或IDataReader。你可以使用一些自定义代码从一个对象列表中创建一个IDataReader，这里有一个问题：

Get an IDataReader from a typed List

从类型化列表中获取IDataReader

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.

我已经看到了选项1 - 一些通用的DAL代码会编写一个SQL“批处理”命令来运行。它起作用，但没有对注射攻击提供任何防御。参数化的SQL确实如此。

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.

所有这一切，我倾向于从代码插入每行调用插入sproc一次 - 调用将完全参数化并且性能良好。如果性能成为问题，我会赞成选项4。

#2

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:

有多大？如果它是巨大的，没有什么比SqlBulkCopy更好。我实际上发现TVP表现令人失望。对于查询计划的重用，我是参数化和大量重用语句的粉丝。 Dapper可以帮助您，允许您将对象列表传递给查询 - 然后它将按成员名称添加每个对象的命名参数，每秒数千次操作。例如：

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.

这将迭代列表并依次使用每个对象的.Id和.Name来执行查询。

#3

Values is limited to 1000

值限制为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.

我使用TVP插入数千行，它对我很有用。我使用List集合作为TVP源，因为DataTable有更多的开销。如果可以，插入由PK排序的行。

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

话虽如此，我将尝试Marc Gravell的答案。

JNK has a general distrust of TVP.

JNK普遍不信任TVP。

#1