Posted by Erland Sommarskog on 03/09/06 01:24

Ben (vanevery@gmail.com) writes:
> We are planning to add a new attribute to one of our tables to speed up
> data access. Once the attribute is added, we will need to populate
> that attribute for each of the records in the table.
>
> Since the table in question is very large, the update statement is
> taking a considerable amount of time. From reading through old posts
> and Books Online, it looks like one of the big things slowing down the
> update is writing to the transaction log.
>
> I have found mention to "truncate log on checkpoint" and using "SET
> ROWCOUNT" to limit the number of rows updated at once. Or "dump
> transaction databaseName with No_Log".
>
> Does anyone have any opinions on these tactics? Please let me know if
> you want more information about the situation in order to provide an
> answer!

The most effecient way to implement this may be to go the long
way. That is, rather than using ALTER TABLE, renamed the table to
old_tbl, create the new table with the new definition, and load
data from the old table, populating the new column while you are
it. You then need to restore triggers, indexes and foreign keys, and
also move referencing foreign keys from other tables to point to
the new table definition.

When you copy data, it may be a good idea to do that in a loop. Something
like:

SELECT @loopid = MIN(id) FROM old_tbl
WHILE @loopid IS NOT NULL
BEGIN
INSERT new_tbl (...)
SELECT ...
FROM old_tbl
WHERE id BETWEEN @loopid AND @loopid + 500000

SELECT @loopid = MIN(id) FROM old_tbl WHERE id > @loopid + 500000
END

Important here is that you loop over the clustered index, else
it will not perform well.

If you set the database in simple recovery before you do this, SQL
Server will truncate the log after each iteration more or less, and
this can avoid that the log grows excessively large.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]