Improve update query performance with T-SQL

Blowfelt82
Blowfelt82 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron TomoskyDirector of Solutions Consulting

Commented:
Can you post the current query?

Author

Commented:
UPDATE REGISTRATION SET REF_NO = REF_NO + 50

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
Aaron TomoskyDirector of Solutions Consulting

Commented:
Is refno an integer? Is there a pk or some kind of unique index? Is there a timestamp column also being updated?

Author

Commented:
Indexes was the cause of this, after dropping them the script took seconds!
Cheers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial