Solved

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

Posted on 2012-03-29
10
236 Views
Last Modified: 2013-01-03
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
0
Comment
Question by:dthansen
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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.
0
 

Author Comment

by:dthansen
Comment Utility
It is not part of a clustered index.

Dean
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
WHILE @@ROWCOUNT > 0
    UPDATE TOP (50000) table
    SET ChargeID = ''
    WHERE ChargeID = 'X'
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
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...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Adding RAM may also be helpful...Hard to say given the info you provided...
0
 

Author Comment

by:dthansen
Comment Utility
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now