如何使用 SQL Server 批量更新? [英] How to Bulk Update with SQL Server?

问题描述

我有一个包含 1000 万行的表，我需要将它与另一个表连接并更新所有数据.这花费了 1 个多小时，它使我的事务日志增加了 10+ GB.还有其他方法可以提高这种性能吗?

I have a table with 10 millions rows that I need to join with another table and update all data. This is taking more than 1 one hour and it is increasing my transaction log in 10+ GBs. Is there another way to enhance this performance?

我相信每次更新后，都会检查索引和约束并记录所有信息.有没有办法告诉 SQL Server 仅在更新完成后检查约束并最少记录更新操作?

I believe that after each update, the indexes and constraints are checked and all information are logged. Is there a way to tell SQL Server to check constraints only after the update is finished and to minimally log the update action?

我的查询如下.我修改了一些名称，以便于阅读.

My query follows below. I've modified some names so it becomes easier to read.

UPDATE o
SET o.Info1 = u.Info1, o.Info2 = u.Info2, o.Info3 = u.Info3
FROM Orders o
INNER JOIN Users u
ON u.ID = o.User_ID

如评论中所问，表定义将类似于以下内容(再次简化以创建通用问题).

as asked in comments, the table definition would be something like the following (simplifying again to create a generic question).

ID int PK
OrderNumber nvarchar(20)
User_ID int FK to table Users
Info1 int FK to table T1
Info2 int FK to table T2
Info2 int FK to table T3

表用户

ID int PK
UserName nvarchar(20)
Info1 int FK to table T1
Info2 int FK to table T2
Info2 int FK to table T3

推荐答案

首先，没有BULK UPDATE这样的东西，你可以做的一些事情如下:

First of all there is no such thing as BULK UPDATE, a few things that you can do are as follow:

1. 如果可能，请在执行此操作之前将数据库置于简单恢复模式.
2. 更新前删除索引，更新完成后重新创建.

3. 小批量更新，比如

1. If possible put your database in simple recovery mode before doing this operation.
2. Drop indexes before doing update and create them again once update is completed.

3. do updates in smaller batches , something like

WHILE (1=1)
 BEGIN
   -- update 10,000 rows at a time 
   UPDATE TOP (10000) O
   FROM Table O inner join ... bla bla

    IF (@@ROWCOUNT = 0)
           BREAK;
END

注意

如果您使用简单模式选项，请不要忘记在将恢复模式切换回完整模式后进行完整备份.因为在您进行完整备份之前，简单地将其切换回完整恢复模式不会对日志进行分层.

if you go with the simple mode option, dont forget to take a full-backup after you switch the recovery mode back to full. Since simply switching it back to full recovery mode will not strat logging until you take a full backup.

这篇关于如何使用 SQL Server 批量更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！