I want to set all values of some columns (not all) of a table in an SQL-Server database to NULL. The problem is, that the table has more than 30.000.000 rows and it takes a very long time to update all columns/rows. How can I speed up the follwoing update statement?
Update Table
Set
column1 = null,
column4 = null,
column8 = null,
column12 = null
It is not possible to drop the table!
The problem is, that the table has more than 30.000.000 rows and it takes a very long time to update all columns/rows. How can I speed up the following update statement?
You buy a faster server.
Seriously.
Unable to drop table. Must update 30 million rows in one statement.
That are both the sides that are unchangeable. There is not a lot of optimization possible (you could kill any index on that fields then recreate them). What is left is getting better hardware.
Sorry, there is no better answer. You could try doing updates 10000 at a time, in a loop - that would make the transactions and statements faster, but i doubt it would make the whole operation faster, which your question is about. At the end the work must be done, and if it needs to be done faster, then you need more power = a faster server.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With