Fastest way to update 20 + million records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fastest way to update 20 + million records

Author

Topic

mfahner
Starting Member

11 Posts

Posted - 2008-03-19 : 21:40:18

Hello,
What is the fastest way to update 20million records in our database.
I have tried to do a simple update statement like this:
update trail_log with (tablockx, holdlock)
set trail_log .entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id

but it take 10 plus hours to run since it cannot commit the transactions until the very end. So was was thinking that I need to commit in batch like after 50K but that is slow as well.
Set rowcount 50000
Declare @rc int
Set @rc=50000
While @rc=50000
Begin
Begin Transaction
update trail_log With (tablockx, holdlock)
set trail_log.entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
and trail_log.entry_by not like '%[0-9]%'
Select @rc=@@rowcount
--Commit the transaction
Commit
End
go
I have let the above statement run for 1.5 hours and it only update 450000 rows. Any ideas...
Maybe I'm doing it wrong. Please Help!!

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-03-19 : 23:50:44

You have to consider lot of things for updating such a big pile of records
1) Check if there is any Triggers on that table if these triggers are just Audit trail just disable those triggers and enable them after update
2) Check if TRAIL_LOG.ENTRY_BY = USERS.USER_ID both have Indexes on it if not, Create a Cluster index on those column which make life much easier
3) Do not Start a transaction for these update which involve massive record update, What happen when you start transaction it will start logging your records update, imagine if you have 20 million record then you have similar number of record log as well so do not bother to but in transaction. I assure you it will be very quick
4) Check if TRAIL_LOG.ENTRY_BY is a part of any Composite index, If it is just remove that index and recreate that after update, What happen when you are updating some thing which is a part of composite index it need to main the index while updating it.
I think it will help you if you have any question you are more then welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources