I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID into another column.)

What I'm trying to achieve is to normalize the dataset. The not yet normalized data has no indexing. My thought was that I would not build indexes on the raw values, waiting, instead to index the foreign keys that will be replacing the varchar values with tinyint values after the update completes.

SET A.AutoClassID = B.AutoClassID
FROM AutoDataImportStaging.dbo.Automobile as A
JOIN AutoData.dbo.AutoClass as B on (A.AutoClassName = B.AutoClassName)


  • using MSSQL 2008 R2 on Server 2008 R2
  • server has 8 GB RAM
  • server has one RAID10, 7200 RPM SATA (not great, I know, in production this will only read data and not write data; plus recent HD shortage made this necessary for cost)
  • server has dual quad-core Xeon CPU
  • the machine is not doing anything else (currently dedicated to dev, only this process)
  • simple logging turned on (? - but does it still log so that it can rollback?)
  • note that the query references two different DBs, for what that's worth
  • "width" of a record in table getting updated is 455 bytes

Resources During Execution

  • physical RAM is maxed out
  • disk I/O is maxed out
  • CPU is hardly doing anything (choke point is I/O)
  • run time has been 14 hours and counting!

I suspect a few things like I need an index on the raw data, even though I will be dropping the column (AutoClassName) after the normalization updates. I also wonder if I should just loop down the table one record at a time instead of the JOIN, which seemed ridiculous at the time I started this, but now it seems that would have been faster.

How should I change my methodology for my remaining normalization updates (similar to this one) more quickly?

6 Answers 6


You're trying to do this as a single (very large) transaction. Instead, do the update in smaller batches.

You would also benefit from:

  • A temporary index on AutoData.dbo.AutoClass.AutoClassName
  • More RAM. Lot's more RAM.
  • 1
    +1 I agree with the batch updating using the TOP clause. That'd be my approach. Commented Jul 6, 2012 at 15:10
  • If I do UPDATE TOP then I'll need a WHERE clause (WHERE AutoClassID is NULL)? Wouldn't the WHERE clause introduce a new performance hit (a table scan I'm not doing now). No doubt it would diminish the RAM problem I'm having with the JOIN. Commented Jul 6, 2012 at 15:20
  • My response is long overdue, but in my case, SET ROWCOUNT proved to be the most effective. Commented Sep 6, 2013 at 21:44

I would take a different approach.

Instead of updating existing tables, just build a new table that has what you need in it.

This will almost certainly be faster:

    <Other fields>

As currently written, there are a lot of logical operations happening:

  • Read all values of A.AutoClassName
  • Read all values of B.AutoClassName
  • Compare A and B values
  • Of the matching set, read all values of B.AutoClassID
  • Update existing values of A.AutoClassId to be the B.AutoClassId value through whatever indexes exist
  • This sounds like a nice, simple approach, especially given the disk I/O problem I'm having. Thank you for answering so quickly. Commented Jul 6, 2012 at 15:16
  • 1
    I suggest that you double-check that you have enough free space in your log and data files. If the files are auto-growing, performance will nose-dive. I often see people running some big, one-off update and auto-growing their log file without realizing it. Commented Jul 6, 2012 at 18:12

Looping down the table one row at a time, will not be faster!

As suspected, and confirmed by you, this will be i/o bound - having one disk, the reads, write, transaction logs and (any) temp work space will all be competing for the same i/o.

Simple recovery will still log the transactions, but the log will be cleared by a checkpoint. It's possible that you initial log size and auto-growth settings are causing some i/o slow down - the transaction log will need to grow to accommodate the changes.

Have you tried indexing the AutoClassName field? How many different AutoClass values are there?

You may need to batch the updates, based on limitations of your i/o. So update 1 million, checkpoint, repeat....

  • There are only 15 different AutoClass values. Your comments confirm many of my suspicions (and pains!). Thank you for answering. Commented Jul 6, 2012 at 15:12

Create indexes for the joining fields.

You can always drop the indexes when you are finished.

I would be very surprised if the indexes did not significantly improve the update performance.

  • I'm sure indexes would improve. I suppose the question is whether they improve more than the time it takes to create the index (for one use only). Probably yes. :) Commented Jul 6, 2012 at 21:24

Export the way you want, create a new table and import back. As a Bonus, you would have a copy of the data as backup, should miracles happen.


(I know this is an old post, but I had a similar problem just last week.)

If the table is a "log of transactions" in which the most recent records are more actively queried than records from five years ago, then update the table in "date range" pieces: all of today's records, then yesterday's, the day before's, etc. That can be performed in a simple loop, where a BEGIN TRANSACTION, UPDATE and COMMIT are inside the loop. You can even put a WAITFOR DELAY after the COMMIT if other tables need access to the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.