C#: 将 DataTable 传递给 SQL Server 很慢

Question

C#: 将 DataTable 传递给 SQL Server 很慢

4

我有一段C#代码，它从数据库A返回一个DataTable，并通过Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet方法将其作为表值参数传递给数据库B的存储过程。该表中约有200K行。在测试环境中，A和B在同一台服务器上，在执行存储过程时只需要几秒钟。但是在生产环境中，ExecuteDataSet调用几乎需要30分钟。检查数据库日志显示，几乎所有时间都花费在了ExecuteDataSet调用开始和存储过程开始之间。

在测试中，两个数据库都在同一台服务器上。在生产中，A和B位于不同的服务器上。

是什么导致了这种极端的延迟，有什么可以解决？这只发生在针对生产服务器运行时。在多个测试环境中运行时没有任何问题。

更新2016/04/08

如果将表值参数作为IEnumerable而不是DataTable传递，则性能是合理的。使用SqlDataRecord向50,000条记录传递需要7秒，而使用DataTable需要400秒。问题取决于服务器。向另一台服务器传递相同数量的数据使用DataTable没有问题。

- kevin cline

使用表值参数处理20万条记录肯定会导致性能问题。随着行数的增加，其性能迅速下降。此外，由于各种问题，将数据传输到两个服务器上会出现性能瓶颈。这个存储过程运行频率有多高？这20万条记录经常更改吗？您能详细说明一下这20万条记录如何被存储过程使用吗？ - Amit Sukralia

1

如果您提到的两个数据库都在同一台服务器上，那么测试性能会更好。如果源表不经常更新，则可以构建某种ETL过程将表从数据库A导入到B。您还可以使用数据库复制来复制表。您还可以使用OPENQUERY或者使用.net中的SqlBulkInsert来完成此操作。我认为，表值参数方法不适用于这个大型数据集。 - Amit Sukralia

1

@kevincline，请确保DataTable为字符串列指定了最大长度（否则默认为-1，即2GB）。否则，服务器端跟踪可能会导致TVPs变慢并且使用过多的内存。 - Dan Guzman

测试和生产数据库的大小是否相同？可能是查询计划错误，或者生产环境中有太多未建立索引的数据，导致无法正常工作。另一件事是，对于这么多行数据，批量插入可能是更好的解决方案。 - LoztInSpace

1

听起来像是一个糟糕的查询计划。我知道你说B是备份，但可能还有其他因素。你尝试过重建所有相关表的统计信息吗？ - LoztInSpace

显示剩余7条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user9398455 · Accepted Answer

我们也遇到了这个问题。Kevin Cline 的更新结果帮助我们弄清楚为什么它在一个服务器上运行得很快，在另一个服务器上却运行得非常慢：

“如果传递的表值参数是 IEnumerable 而不是 DataTable，则性能是合理的。使用 SqlDataRecord 将 50,000 条记录传递需要 7 秒，而使用 DataTable 则需要 400 秒。问题取决于服务器。同样数量的数据通过使用 DataTable 传递到另一台服务器时没有问题。”

我们发现 SQL Server 设置中的 ForceEncryption 是快速执行和慢速执行之间的区别。如果 ForceEncryption 设置为 Yes，则性能较差；如果设置为 No，则性能良好。该设置可在 SQL Server Configuration Manager -> SQL Server Network Configuration -> 协议 -> 右键单击属性 -> 证书选项卡 -> 选择您的证书 -> 标志选项卡中找到。

为了解决这个问题，你有两个选择。要么关闭数据库上的ForceEncryption设置（可能不可取），要么避免使用DataTables，而是像Kevin Cline建议的那样使用SqlDataRecords。