ADO.net SqlTransaction 提高了性能 [英] ADO.net SqlTransaction improves performance

问题描述

我正在做一些涉及将一批记录插入 Sql 数据库的工作.批处理的大小会有所不同，但为了论证，我们可以说每 5 秒有 5000 条记录.不过可能会少一些.多个进程将写入此表，没有任何内容从中读取.

I'm doing some work that involves inserting a batch of records into a Sql database. The size of the batch will vary but for arguments sake we can say 5000 records every 5 secs. It is likely to be less though. Multiple processes will be writing to this table, nothing is reading from it.

我在快速测试期间注意到的是，围绕整个批处理插入使用 SqlTransaction 似乎可以提高性能.

What I have noticed during a quick test is that using a SqlTransaction around this whole batch insert seems to improve performance.

例如

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

我对能够回滚我的更改不感兴趣，所以我不会真正考虑使用事务，除非它似乎可以提高性能.如果我删除此事务代码，我的插入时间将从 300 毫秒缩短到 800 毫秒左右！

I'm not interested in having the ability to rollback my changes so I wouldn't have really considered using a transaction except it seems to improve performance. If I remove this Transaction code my inserts go from taking 300ms to around 800ms!

这样做的逻辑是什么?因为我的理解是事务仍然将数据写入数据库但锁定记录直到它被提交.我本来预计这会产生开销...

What is the logic for this? Because my understanding is the transaction still writes the data to the DB but locks the records until it is committed. I would have expected this to have an overhead...

我正在寻找的是执行此插入的最快方法.

What I am looking for is the fastest way to do this insert.

推荐答案

如果您正在寻找插入/加载数据的快速 wqay，请查看 SqlBulkCopy 类

If you are looking for a fast wqay to insert/load data have a look at SqlBulkCopy Class

这篇关于ADO.net SqlTransaction 提高了性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！