For the most part SQL runs very well but every once in a while we run into something that runs slow and is not immediately explainable. We have this one update query in the system that runs dirt slow no matter what, while other update queries that do many more records are much faster. The only thing that I can come up with is that it is updating itself and this particular method is inherently slow. Let me explain.
I have a table with 2 columns. RecordID and ChargeID. The query is:
update table set ChargeID = '' where ChargeID = 'X'.
Some instances of this query do upwards of 2,000,000 rows at a time and take HOURS. While other update queries in our database on the same table, different column, takes minutes.
The only thing I can think is that I am updating a column that is also the predicate for my query and something about this combination is just plain slow. Understandably I suppose.
Would I be better doing doing a two-part query:
1. insert all RecordID into temptable where ChargeID = 'X'
2. update table set ChargeID = '' where RecordID join to temptable.