在事务中的包装插入在多大程度上有助于Sql Server上的性能？ [英] How much does wrapping inserts in a transaction help performance on Sql Server?
问题描述
好吧，所以说我有100行插入，每行有大约150列（我知道听起来像很多列，但我需要存储在一个表中的数据）。插入将随机发生（即，每当一组用户决定上传包含数据的文件时），每月大约20次。但是数据库会被连续负载处理其他功能的大型企业应用程序。列是varchars，int，以及各种其他类型。
在事务中封装这些插入（而不是一次运行一个插入）的性能增益是巨大的，最小的，还是介于两者之间？ / p>
为什么？
编辑：
这是为Sql Server 2005，但我会感兴趣在2000/2008如果有不同的话要说。此外，我应该提及，我理解交易主要是为了数据一致性，但我想关注的性能效果。
p>这可能是一个影响。交易点不是关于你做了多少，而是关于保持数据更新一致。如果您有需要插入在一起并且相互依赖的行，则这些行是您在事务中包装的记录。
事务是关于保持数据一致。这应该是你在使用事务时第一件事。例如，如果您从您的支票帐户中扣款（提款），您要确保信用（存款）也已完成。如果其中任何一个不成功，整个事务应该回滚。因此，两个操作都必须包装在一个事务中。
当进行批处理插入时，将它们拆分成3000或5000条记录并循环遍历集合。 3000-5000是一个甜蜜的数字范围为我的插入;除非你已经测试了服务器可以处理它。此外，我将把大约每3000或5000记录插入批处理。更新和删除我将大约1000的GO，因为他们需要更多的资源提交。
如果你这样做从C＃代码，然后在我看来，应构建一个批次导入例程，而不是通过编码一次一个地处理数百万个插入。
Ok so say I have 100 rows to insert and each row has about 150 columns (I know that sounds like a lot of columns, but I need to store this data in a single table). The inserts will occur at random, (ie whenever a set of users decide to upload a file containing the data), about a 20 times a month. However the database will be under continuous load processing other functions of a large enterprise application. The columns are varchars, ints, as well as a variety of other types.
Is the performance gain of wrapping these inserts in a transaction (as opposed to running them one at a time) going to be huge, minimal, or somewhere in between?
Why?
EDIT: This is for Sql Server 2005, but I'd be interested in 2000/2008 if there is something different to be said. Also I should mention that I understand the point about transactions being primarily for data-consistency, but I want to focus on performance effects.
It can be an impact actually. The point of transactions is not about how many you do, it's about keeping the data update consistent. If you have rows that need to be inserted together and are dependent on each other, those are the records you wrap in a transaction.
Transactions are about keeping your data consistent. This should be the first thing you think about when using transactions. For example, if you have a debit (withdrawl) from your checking account, you want to make sure the credit (deposit) is also done. If either of those don't succeed, the whole "transaction" should be rolled back. Therefore, both actions MUST be wrapped in a transaction.
When doing batch inserts, break them up in to 3000 or 5000 records and cycle through the set. 3000-5000 has been a sweet number range for me for inserts; don't go above that unless you've tested that the server can handle it. Also, I will put GOs in the batch at about every 3000 or 5000 records for inserts. Updates and deletes I'll put a GO at about 1000, because they require more resources to commit.
If your doing this from C# code, then in my opinion, you should build a batch import routine instead of doing millions of inserts one at a time through coding.
这篇关于在事务中的包装插入在多大程度上有助于Sql Server上的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
