Long Query Times

I have a table with 23.5 million rows and 20 columns. I updated the table to set one of the columns t开发者_StackOverflow社区o null. This query took an hour to complete. Granted, I don't have an amazingly fast database server, but is this update time normal? I didn't have an index on this table when I ran the update. How much would that have helped?

Thanks in advance!

Considering it updated ALL rows, an index wouldn't have helped any.

Were there reads going on at the same time? Updates cause row level locking, even if brief, could cause a lot of traffic and waiting in the transaction log.

This is certainly an acceptable time for your database server's number of rows, especially since you said it is not that fast. If you had an index it wouldn't have helped. Indexes are used to help the database server find specific records faster.

Instead of updating and setting the column to null, you can drop the column and re-add it as nullable. In my experience, this is many times faster than updating and setting each row to have null in that field.