在事务中包装插入对 Sql Server 的性能有多大帮助? [英] How much does wrapping inserts in a transaction help performance on Sql Server?

问题描述

好吧，假设我有 100 行要插入，每行大约有 150 列(我知道这听起来像很多列，但我需要将这些数据存储在一个表中).插入将随机发生(即，每当一组用户决定上传包含数据的文件时)，大约每月 20 次.然而，数据库将承受持续负载处理大型企业应用程序的其他功能.列是 varchars、ints 以及各种其他类型.

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?

为什么?

这是针对 Sql Server 2005，但如果有不同的说法，我会对 2000/2008 感兴趣.另外我应该提到，我理解事务主要是为了数据一致性的观点，但我想关注性能影响.

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.

在进行批量插入时，将它们分成 3000 或 5000 条记录，然后在整个集合中循环.3000-5000 对我来说是一个很好的插入数字范围；除非您已经测试过服务器可以处理它，否则不要超过它.此外，我将大约每 3000 或 5000 条记录将 GO 放入批处理中以进行插入.更新和删除我将 GO 设置为 1000 左右，因为它们需要更多资源来提交.

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.

如果您从 C# 代码执行此操作，那么在我看来，您应该构建一个批量导入例程，而不是通过编码一次执行数百万次插入.

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屋！