Improve update query performance with T-SQL

I have a SQL table with millions of rows which I need to update. At the moment this is taking a massive amount of time which I am hoping can be improved. The SQL in question is just a basic update statment which is adding 50 to an existing number (as basic as it gets), but the scale of the change means this is taking ages to complete.

Another problem is that the change is causing the transaction log to fill up to maximum capacity, this is causing the script to end prematurly with a warning. This happens whether I have the change wrapped in a transaction or not, I have tried shrinking the database prior to the change but this still occurs either way.

I have also tried breaking the script up into portions using the ID field, i.e. update all rows where ID is like '1%', '2%' etc, but this still takes forever.

Does anyone have any idea how I can do this as efficiently as possible?
Blowfelt82Asked:
Who is Participating?
 
DavidMorrisonCommented:
Is this field in any non clustered indexes?

if so dropping them, doing the update, and then re-adding will speed up the process.

the transaction log issue will be due to your logging mode I would imagine, if you can change this it would help.

Another thing to consider is partition switching

Thanks

Dave
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Can you post the current query?
0
 
Blowfelt82Author Commented:
UPDATE REGISTRATION SET REF_NO = REF_NO + 50

0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Is refno an integer? Is there a pk or some kind of unique index? Is there a timestamp column also being updated?
0
 
Blowfelt82Author Commented:
Indexes was the cause of this, after dropping them the script took seconds!
Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.