How to Bulk Update with SQL Server?

问题:

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?

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

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

Table Orders

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

Table Users

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

回答1:

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

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
   

Note

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.