Improve update query performance with T-SQL
Posted on 2011-09-06
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?