.net - 表值参数性能问题

Question

我不知道这是否是我使用它们的方式或 Microsoft 实现的问题，但 SQL 2008 表值参数非常慢。

通常，如果我需要使用 TVP，那是因为我有很多记录 - 目前，对于超过最少记录的任何内容，它们似乎都慢得无法使用。

我在 .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();

我运行了 profiler 来查看原因，实际的 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 中添加了新语法，但 TVP 也是如此(我认为)。

有什么选项可以让它做到这一点吗？或者我缺少什么？

Answer 1

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

1. 您不应该使用 DataTable，除非您的应用程序除了将值发送到 TVP 之外还需要使用它。使用IEnumerable<SqlDataRecord>接口(interface)速度更快并且使用更少的内存，因为您不必在内存中复制集合只是为了将其发送到数据库。我已将其记录在以下位置:
   • How can I insert 10 million records in the shortest time possible? (这里还有很多额外的信息和链接)
   • Pass Dictionary to Stored Procedure T-SQL
   • Streaming Data Into SQL Server 2008 From an Application (在 SQLServerCentral.com 上；需要免费注册)

2. 您不应该使用AddWithValue对于 SqlParameter，尽管这不太可能是性能问题。但仍然应该是:

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

3. TVP 是表变量，因此不维护统计数据。这意味着，它们向查询优化器报告只有 1 行。所以，在你的过程中，要么:
   • 对任何使用 TVP 的查询使用语句级重新编译，而不是简单的 SELECT:OPTION (RECOMPILE)
   • 创建本地临时表(即单个 # )并将 TVP 的内容复制到临时表中
   • 您可以尝试向用户定义的表类型添加聚集主键
   • 如果使用 SQL Server 2014 或更高版本，您可以尝试使用内存中 OLTP/内存优化表。请参阅: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]) 时没有限制。 ，我在这里记录了: Maximum Number of Rows for the Table Value Constructor 。相反...
• 原因很可能是执行单独插入允许将值从应用代码流式传输到 SQL Server:
  1. 使用迭代器(即上面 #1 中提到的 IEnumerable<SqlDataRecord>)，应用代码会发送从方法返回的每一行，并且
  2. 构建VALUES (), (), ...列表，即使执行 INSERT INTO ... SELECT FROM (VALUES ...)方法(不限于 1000 行)，仍然需要构建整个 VALUES在将任何数据发送到 SQL Server 之前列出。如果数据量很大，构造超长字符串的时间会更长，而且会占用更多的内存。

另请参阅 SQL Server 客户咨询团队提供的这份白皮书:Maximizing Throughput with TVP