Blowfelt82
asked on
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?
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?
Aaron Tomosky
Can you post the current query?
ASKER
UPDATE REGISTRATION SET REF_NO = REF_NO + 50
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is refno an integer? Is there a pk or some kind of unique index? Is there a timestamp column also being updated?
ASKER
Indexes was the cause of this, after dropping them the script took seconds!
Cheers.
Cheers.