Updating records in a Large Table

Question

Updating records in a Large Table

gaestes

SSC Enthusiast

Points: 193
More actions
August 5, 2008 at 9:24 am

#196501

I have a table with about 100 columns and several million rows. About 200,000 records need to be reprocessed each day for a variety of reasons. The table has a clustered index on SourceRecordID. While reprocessing these records, all columns but SourceRecordID could possibly change. Also, each record could have different columns that have changed.
What would be the fastest way to update these reprocessed records in the big table? Since the clustered index is not changing, will SQL 2000 do an "Update in Place"? Would it be faster to deletes these records from the big table and reinsert them?
Also, this server will be upgraded to SQL 2005 in the next month or so. Are there differences between the way it's handled between these versions?
Thanks for your help.
Greg

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

What would be the fastest way to update these reprocessed records in the big table? Since the clustered index is not changing, will SQL 2000 do an "Update in Place"? Would it be faster to deletes these records from the big table and reinsert them?

For sure you'll have better performance updating records in place: if you deleted and re-inserted, SQL Server would have to refresh the clustered index, which I wouldn't recommend...

I don't see any difference between SQL 2000 and 2005 regarding this issue.

Regards

Gianluca

-- Gianluca Sartori

gaestes SSC Enthusiast Points: 193 More actions · Answer 2

Thank you.

That is what I was thinking but wasn't sure how to tell if an "update is place" was actually happening. Is there any way to tell, thru the query plan or something, if it's doing an update in place or delete/insert behind the scenes?

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

The query plan should be quite clear regarding this.

You should see in your plan something like "Index Update" that is the way SQL Server decided to update your data. Other parts of the plan can contain information about how the engine decided to find the rows to update, but the main task is the index update.

-- Gianluca Sartori