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!

Ben (vanevery (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Ben" <vanevery (AT) gmail (DOT) com> wrote

Yes, options like this can help.

Also, if you can, drop your indices BEFORE you load the data and then
rebuild them.

I've often found this far faster.

You mention an update statement, can you use BCP or Bulk copy?

Thank you, both of you!

I think that i will create a new table, and use CHECKPOINTS in between
INSERT statements.

Your advice has been very useful.