c# - 使用大型数据集读取/写入 sql server 的最快方法？

Question

我在数据库中有大约 6000 万条记录，必须处理所有这些记录。所以想法是使用c#代码读取数据，处理它然后把它放回数据库中。数据不会进出同一个表 - 涉及多个表。

我想看看最好的做法是什么？我是否应该在数据集中一次读取 100K 条记录，然后处理每条记录，然后使用批量插入数据库，然后读取下一组？

Answer 1

不要靠近 DataSet 或 DataAdapter!

要获取数据，请使用 DataReader - 通过调用 ExecuteReader 来使用 SQL 文本或通过 SqlCommand 调用存储过程。然后，您可以一次从 DataReader 中检索一条记录，而无需 DateSet、 Entity Framework 或 Linq to SQL 或 NHibenate 附带的任何对象跟踪包袱——所有这些框架都添加了层以允许您执行对象和更改跟踪 - 您不需要，只会增加您的开销。

当您将结果写回数据库时，通过 SqlBulkCopy 执行此操作，启用 TableLock 并将数据库属性设置为“恢复模型”而不是“完整”。确保禁用目标表上的约束，并且未定义任何索引(如果需要，然后删除并在最后重新创建)。

SqlBulkCopy 在发送回 SQL Server 时自己进行批处理，前提是您确保指定了 BatchSize(默认是一个批处理中的所有内容)。您可能还想在 SqlBulkCopy 上设置 UseInternalTransaction，以便每个批处理都在其自己的事务中完成 - 这将进一步减少事务日志的使用。

读者和作者线程可能有帮助，也可能没有帮助，我没有详细说明其中的区别。如果您提到的“第三方进程”很耗时，您可能还需要一个或多个处理线程或其他一些机制。

可以在单个线程上完成所有这些操作，一次一条记录，而且速度非常快(取决于您正在进行的处理的成本)。

如果您确实需要使用多个线程，则不要在这两个线程之间交换单个记录，因为您会损失太多 CPU 周期线程切换:将其分成“合理”的批处理。 “合理”可能介于 1k 和 100k 之间，具体取决于记录大小和您所做的处理。甚至可以让 Parallels 为您做这件事。

鉴于您说涉及多个表，可能只是为每个源表启动一个线程可能会很好，并锁定写入 SqlBlukCopy 对象以进行同步。