Link to home
Start Free TrialLog in
Avatar of dthansen
dthansen

asked on

SQL Query - Updating an in an index you're using

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.

Thanks,
Dean
Avatar of DcpKing
DcpKing
Flag of United States of America image

Is ChargeID all or part of a clustered index (primary key, for example)? If so, then changing it means you're physically moving records around, rather than just changing their content.
PS. If ChargeID is part of a clustered index, your best bet may be to drop the index, do the change, and re-index the table.
Avatar of dthansen
dthansen

ASKER

It is not part of a clustered index.

Dean
Avatar of DBAduck - Ben Miller
The way this works is that whenever you update a table the data that you are updating has to be read into memory and then it is changed and committed to the log file then data file (in a nutshell, it is more complex than that, but it will suffice).

If you do not have an index on ChargeID then it will most likely read (scan) the clustered index (which is the entire table width) into RAM to do the update.  This can kill things for performance and could be much faster if there was an index on ChargeID.

Again, it will move data around in the index, but the reality is that it could be that you are reading a whole lot of data into the data cache to update your field.

In a query window, have that query there and hit the button for Estimated Execution Plan and look at what it believes it needs to do to complete the update and it will most likely involve a Clustered Index Scan if there is 2,000,000 rows to update.
Is the table being used for anything else during the time you're doing this update? If so, your update may be waiting for other processes to release locks from the table. If you think this might be the case, read this article:

http://www.sql-server-performance.com/2010/concurrency-sp-locks/

You might also try dropping any indices on ChargeID from Table before doing the update, if the ChargeID field has indices on it, and then replacing them afterwards, on the assumption that the RDBMS is updating not just the field but the indices too, every time one of these values gets changed.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You have to determine at the time of slow operation what type of blocking lock are preventing the operation to complete faster.  It looks as if your update involves so many rows that the systems never gets an exclusive table lock to complete the operation.  Some other processes are preventing that.  What you could do it activate READ_COMMITED_SNAPSHOT option, beef up you tempdb file size and performance and you should see some improvement....

But unless you determine what process is preventing the lock grant, there is little chance you can improve this by simply modifying code...Reducing transaction row size my help too...
Adding RAM may also be helpful...Hard to say given the info you provided...
1. There is an index an ChargeID.
2. The update happens explicitly. The BEGIN TRAN happens in a parent SP that calls this SP so I can't reduce the transaction size.
3. I do agree I have not provided a tremendous amount of information.

My observation has been that the update of ChargeID runs very well UNLESS ChargeID is part of my predicate. I guess I was hoping someone had encountered similar behavior and could confirm that ChargeID being part of the predicate was indeed the problem and could enlighten me as to why this particular scenario is so much slower than other updates of ChargeID.

I am going to try the method below to remove ChargeID from the predicate and see if that helps. I will post my results.

1. insert all RecordID into temptable where ChargeID = 'X'
2. update table set ChargeID = '' where RecordID join to temptable.

Thanks for all your responses.

Dean
Unless you index temptable, I suspect that is going to be a dog and even if you have it should not be much better.

Unfortunately, you have been given a lot of suggestions and we have not had much feedback, so it is dificult to be more precise, but I suspect doing it in batches is the way to go.