ADO.net SqlTransaction 提高性能

Question

我正在进行一些涉及将一批记录插入到Sql数据库中的工作。批次的大小会有所变化，但是为了说明问题，我们可以说每5秒钟5000个记录。实际上可能会少一些。多个进程将写入此表，没有任何内容从中读取。

在快速测试期间我发现，在整个批量插入周围使用SqlTransaction似乎可以提高性能。

例如：

SqlTransaction trans = Connection.BeginTransaction()
myStoredProc.Transaction = trans;
sampleData.ForEach(ExecuteNonQueryAgainstDB);
transaction.Commit();

我对回滚更改的能力不感兴趣，所以我原本并没有考虑使用事务，但是似乎使用事务可以提高性能。如果我移除这个事务代码，我的插入操作时间会从300毫秒增加到大约800毫秒！

这是什么逻辑？因为我的理解是，事务仍然将数据写入数据库，但是在提交之前锁定记录。我本来期望这会有一些开销……

我希望找到最快的插入方式。

- user630190

---

如果你只是写入数据，你也可以指定 trans.IsolationLevel = IsolationLevel.Chaos（最低级别），这样你的事务就不会锁定其他并发事务。 - Albireo

Answer 1

提交（commit）是耗费时间的。如果没有显式事务，每个执行的查询都有一个事务。使用显式事务，你的查询不会创建额外的事务。因此，你只有一个事务而不是多个事务。这就是性能提升的原因。

- Daniel Hilgarth

---

我以为提交很便宜，实际上回滚才是昂贵的。我觉得在设置大量事务时确实存在开销，但是在开始而不是结束 - 如果我没记错的话。 - Marc Gravell

Answer 2

如果您正在寻找一种快速的方式来插入/加载数据，请查看SqlBulkCopy类。

- Adriaan Stander

---

SqlBulkCopy的问题在于你需要了解表结构。虽然同意它是最快的方法，但差距相当大。 - user630190

Answer 3

你得到的是完全正常的。

如果您使用通常的隔离级别（比如已提交或快照），那么当您不使用事务时，数据库引擎必须每次进行插入操作时都检查冲突。也就是说，它必须确保每当有人从该表中读取数据（例如使用 SELECT *）时，不会出现脏读，即在插入本身正在进行时，没有其他人正在读取。

这意味着，锁定、插入行、解锁、锁定、插入行、解锁等等。

当您将所有内容封装在一个事务中时，您实际上将这一系列“锁定”和“解锁”缩减为一次提交阶段中的一次。

- Jorge Córdoba

---

我猜这种方法的缺点是在事务期间表被锁定了？如果多个进程试图写入此表，这些事务是否会被有效地排队或者是否没有必要，因为这些操作只涉及插入而不是更新或删除现有行？ - user630190

需要锁定表格，因为select *会影响所有行，不能在中途中断。但这并不意味着它会花费很长时间。我不知道任何具体的实现方式，但可以想象你可以构建一个内存中的表格副本，如果有足够的内存，就可以快速地进行交换。 - Jorge Córdoba

Answer 4

我刚刚写完了一篇博客文章，介绍了通过明确指定事务开始和结束位置可以获得的性能提升。

使用Dapper，我观察到批量插入时间缩短了原来的一半，批量更新时间缩短了原来的三分之一。

- undefined