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

问题描述

我做了一些工作，涉及插入一批记录到一个SQL数据库。批量的大小会有所不同，但为了讨论我们可以说5000条记录，每5秒。它很可能是较少虽然。多个进程将被写入该表中，没有什么是从中读取。

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();

我不感兴趣，不必回滚了我的变化，所以我也不会真的使用事务，除非它似乎以提高性能考虑的能力。如果我删除此交易code我插入采取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屋！