表值参数性能问题

Question

我不知道这是我使用方法的问题还是Microsoft的实现问题，但是SQL 2008的表值参数非常慢。

通常情况下，如果我需要使用表值参数，那么是因为有很多记录 - 目前它们似乎对于超过最少记录的任何内容都无法使用。

我在 .Net 中像这样调用它们：

// get the data
DataTable data = GetData();

com.CommandText = "sprocName"

// create the table-value parameter
var tvp = com.Parameters.AddWithValue("data", data);
tvp.SqlDbType = SqlDbType.Structured;

com.ExecuteNonQuery();

我运行了分析器来查看原因，实际的 SQL 语句类似于：

declare @data table ...

insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )

sprocName(@data)

不过，那是一种非常慢的方法。 如果改为这样做，速度会快得多：

insert into @data ( ... fields ... ) 
values ( ... values ... ),
       ( ... values ... ),
       -- for each row
       ( ... values ... )

我不确定为什么它没有使用更快的新语法，或者底层使用了什么 SqlBulkCopy。

新语法是在 SQL 2008 中添加的，但我认为 TVPs 也是如此。

是否有一些选项可以让它这样做？或者我漏掉了什么？

- Keith

---

如果SQL Profiler跟踪标记为RPC（而不是批处理），则表示显示的文本不是通过网络传输的真实文本，而是从实际RPC参数重新构建的文本。这并不能解释为什么它很慢，但可能没有意义。 - Simon Mourier

嗨，Keith。感谢您接受我的答案 :-）。我已经忘记了这个问题，所以在阅读它时，我意识到有一些东西是我最初回答时学到的，可能会有所帮助。请查看我的更新。 - Solomon Rutzky

Answer 1

如果TVPs比其他选项“明显慢”，那么很可能是您没有正确实现它们。

1. You should not be using a DataTable, unless your application has use for it outside of sending the values to the TVP. Using the IEnumerable<SqlDataRecord> interface is faster and uses less memory as you are not duplicating the collection in memory only to send it to the DB. I have this documented in the following places:
   • How can I insert 10 million records in the shortest time possible? (lots of extra info and links here as well)
   • Pass Dictionary to Stored Procedure T-SQL
   • Streaming Data Into SQL Server 2008 From an Application (on SQLServerCentral.com ; free registration required)

2. You should not use AddWithValue for the SqlParameter, though this is not likely a performance issue. But still, it should be:

   SqlParameter tvp = com.Parameters.Add("data", SqlDbType.Structured);
   tvp.Value = MethodThatReturnsIEnumerable<SqlDataRecord>(MyCollection);
   

3. TVPs are Table Variables and as such do not maintain statistics. Meaning, they report only having 1 row to the Query Optimizer. So, in your proc, either:
   • Use statement-level recompile on any queries using the TVP for anything other than a simple SELECT: OPTION (RECOMPILE)
   • Create a local temporary table (i.e. single #) and copy the contents of the TVP into the temp table
   • You could try adding a clustered primary key to the User-Defined Table Type
   • If using SQL Server 2014 or newer, you can try making use of In-Memory OLTP / memory-optimized tables. Please see: Faster temp table and table variable by using memory optimization

关于为什么您看到了：

insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )

而不是：

insert into @data ( ... fields ... ) 
values ( ... values ... ),
       ( ... values ... ),

如果确实是这样发生的话，那么：

如果插入操作是在事务内完成的，那么实际上没有性能差异。 新的值列表语法（即VALUES (row1), (row2), (row3)）仅限于大约1000行左右，因此对于没有该限制的TVP来说并不是可行的选择。然而，这不太可能是使用单独的插入的原因，因为当使用INSERT INTO @data (fields) SELECT tab.[col] FROM (VALUES (), (), ...) tab([col])时没有限制，我在这里记录了这一点：表值构造函数的最大行数。相反... 最有可能的原因是进行单个插入允许从应用程序代码流式传输值到SQL Server： 1.使用迭代器（即#1中提到的IEnumerable<SqlDataRecord>），应用程序代码将每行作为返回自方法的数据发送； 2.即使使用INSERT INTO ... SELECT FROM (VALUES ...)方法（该方法不限于1000行），也需要构建VALUES (), (), ...列表，这仍然需要在将任何数据发送到SQL Server之前构建整个VALUES列表。如果有大量数据，则构建超长字符串需要更长的时间，并且在此过程中占用更多的内存。

请查看来自SQL Server客户咨询团队的白皮书：使用TVP最大化吞吐量。

- Solomon Rutzky

Answer 2

请查看“表值参数与BULK INSERT操作”一节。链接如下：http://msdn.microsoft.com/en-us/library/bb510489.aspx。引用：“……对于插入少于1000行，表值参数的性能表现良好。”此外，它还有一个表格，显示基于插入操作速度使用哪种技术。希望这可以帮到您，祝您好运。

- Robert

---

然而，在实践中，即使对于几百行的表格，TVPs 仍然比值列表选项慢得多，甚至比第一次调用后的 SqlBulkCopy 还要慢（第一次运行时，由于某种原因，SqlBulkCopy 需要额外的 1-2 秒）。干杯。 - Keith

@Keith和Robert，那个链接文档中的信息需要放在上下文中理解。请查看我的回答，特别是我在底部提供的白皮书。 - Solomon Rutzky