How to Bulk Update with SQL Server?

Question

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

Tampon

I've saw a dozen of questions with a similar title but none of them try to improve using SQL (just with C# code). – Tampon

Try doing it by batch. Can you include the table definitions of Orders and Users? – Finnigan

@wewesthemenace, I've updated the question with your request. Your ideia of updating doing by batch would be using a loop like M.Ali answer? – Tampon

Yes, that would be it. – Finnigan

Answer 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.

Cardholder

Answer 2

For my case, load data as need(Dotnet winform), and try create a new table, apply bulk and update the basic table with join by bulk table!, for 1M rows , it take me about 5 second

Emu

full source code about it ? create new table , apply bulk and join by bulk table? – Septa