如何将DataAdapter.UpdateBatchSize设置为“最佳”值？

Question

我终于让我的插入批处理开始工作了，现在我一直在摆弄批处理的大小，但是我看不出值50和值10000在性能上有什么不同。这对我来说似乎很奇怪，但我不知道幕后发生了什么，所以这可能是正常的行为。

我在一个表中插入160k行，我的测试值的平均时间是115 +/- 2秒。没有批处理，它需要210秒，所以我对改进相当满意。目标表为：

CREATE TABLE [dbo].[p_DataIdeas](
    [wave] [int] NOT NULL,
    [idnumber] [int] NOT NULL,
    [ideaID] [int] NOT NULL,
    [haveSeen] [bit] NOT NULL CONSTRAINT [DF_p_DataIdeas_haveSeen]  DEFAULT ((0)),
  CONSTRAINT [PK_p_DataIdeas] PRIMARY KEY CLUSTERED 
(
  [wave] ASC,
  [idnumber] ASC,
  [ideaID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
) ON [PRIMARY]

我阅读了What to look for when setting UpdateBatchSize，答案是简单地测试几个不同的值。我可以理解这一点，但如果您了解表设计、SQL问题和即将插入的数据，难道不能计算或至少猜测出一个好的值吗？

有没有人可以推荐的最佳实践？

Answer 1

可以通过查看SQL事件探查器或调用SqlConnection.RetrieveStatistics()来查看批处理的效果。您应该看到的是，每个批处理对应于到DB的一次往返。

至于如何优化批处理大小，一个非常粗略的经验法则是，当批处理大小超过大约50个时，性能往往会停止提高--事实上，有时大批处理可能比小批处理运行得更慢。如果我太忙而没有时间测试，我通常会从大约20个批次开始(除非我使用的是表值参数，其中最多500个批次可能比更小的批次更快)。但是，最佳数量取决于插入的总大小(它们是否都能装入RAM)、DB日志所在磁盘的速度、日志是否在自己的驱动器/LUN上(如果不在，性能开销会很大)，等等。

可实现的速度通常首先受到往返次数的限制，然后是事务大小，然后是日志磁盘速度(特别是顺序访问是否可能，或者是否由于与相同磁盘轴上的其他文件的竞争而被迫随机访问)，最后是RAM。然而，所有这些因素在某种程度上也是相互关联的。

提高插入性能的第一步是在事务中执行插入--可能每批或两个事务处理一个事务。除此之外，表值参数可能是下一步，使用带有INSERT INTO Table SELECT column FROM @TableArgument的存储过程。

Answer 2

虽然更改UpdateBatchSize在一定程度上会有所帮助，但使用DataAdapter更新大量记录的基本方法将会很慢。这是因为最终，DataAdapter将为每一行生成单独的SQL语句(insert、update或delete)。在发送到SQL Server时，UpdateBatchSize仅影响在一个TSQL批处理中发送的单个语句的数量。

为了获得更大的性能改进，您希望SQLServer在一条语句中插入/更新/删除多条记录(通常使用某种联接)。表值参数(如RickNZ所述)就是这样做的一种方式。另一种可能是使用SqlBulkCopy (尽管通常需要使用临时表)。

Answer 3

确保还有一个活动的事务，这将极大地提高性能(在我使用MysqlDataAdapter的测试中，大约是30倍)。