Solved

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

Posted on 2012-03-29
10
237 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
ID: 37785346
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
ID: 37785350
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
ID: 37785371
It is not part of a clustered index.

Dean
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 37785565
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
ID: 37786445
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:
Scott Pletcher earned 500 total points
ID: 37787451
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
ID: 37789496
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
ID: 37789497
Adding RAM may also be helpful...Hard to say given the info you provided...
0
 

Author Comment

by:dthansen
ID: 37791228
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
ID: 37793766
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

861 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

21 Experts available now in Live!

Get 1:1 Help Now