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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
dthansenAuthor Commented:
It is not part of a clustered index.

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

DBAduck - Ben MillerPrincipal ConsultantCommented:
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:


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.
Scott PletcherSenior DBACommented:
Is ChargeID in an index(es)?

If not, SQL will have to scan the entire table to find matching rows and update them.  This could easily lead to blocking other tasks, and them blocking this task.  You could have a lot of wait time.

If so, SQL must do both a DELETE and INSERT into the index(es).  That should be much less blocking, unless that column is heavily modified.

Also, how much log space is free and already allocated and formatted?  If the log has to grow during an UPDATE, it will take VASTLY longer to do the UPDATE.

Finally, you could try doing smaller batches of rows at a time, to reduce any blocking/logging contention:

--assuming you're in autocommit mode, not an explicit trans
--if in explicit trans, you'll need a begin trans and commit as part of the WHILE code

SELECT '1' --force @@ROWCOUNT to 1
    UPDATE TOP (50000) table
    SET ChargeID = ''
    WHERE ChargeID = 'X'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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...
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Adding RAM may also be helpful...Hard to say given the info you provided...
dthansenAuthor Commented:
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.

Anthony PerkinsCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.