slow update (primary key)

Question

update auditdata set TATCallType='12',TATCallUnit='1' from auditdata auditdata 
     inner join Auditdata_sms_12 a_sns
     on auditdata.ID = a_sns.id

when I above query it takes more than 10 minutes to execute.

what wrong in this

Auditdata.ID is primary key..

if i run Update command is that also update the indexes??? is this reason of update getting is slow

Answer 1

Updated I realized after reading the initial query again that you were not updating the primary ID field, but 2 other data fields. Please re-read the first statement of my response and comment accordingly. Sorry.

Is there a clustered index defined on either field that you are updating? There are advantages to clustered indexes ,I don't know them off hand, but they can cause large performance hits during updates. My understanding is that an update to a clustered index can cause the entire index to have to recompile. If you have a lot of data in the table this can definitely cause your problem.

Also, make sure there are no triggers on the table. If there is a trigger that isn't acting right it can cause the same performance hit.

Answer 2

Looking at your comment, main table contains less rows than temp table.

Try using EXISTS clause (or in some sense, reduce the comparison to less number of rows (i.e. 1500000)

update auditdata set TATCallType='12',TATCallUnit='1' 
from auditdata auditdata 
WHERE EXISTS 
(SELECT id from Auditdata_sms_12 a_sns WHERE a_sns.id = auditdata.ID)

The idea is to limit the comparisons.

EDIT: AuditdataSMS12 should have the index on ID to be able to get the row quickly. That is the table you are actually looking up for a given ID.

Answer 3

How long does a simple select (eg

 select id from auditdata auditdata 
    inner join Auditdata_sms_12 a_sns
    on auditdata.ID = a_sns.id

) take and how many records does it find?

If Sql server has to read through all 5 million records , or update a million records and it does not have enough memory or fast enough hardware, then there may not much you with the query.

You probably need to monitor the Sql server hardware and also look at the Query Plan to see what bit takes up the time.

Answer 4

There are a couple things at play here.

First, the SQL statement looks broken. The "FROM" clause in an update is designed to be used as a JOIN'd update. Since you're updating rows with hard-coded values, there's no need to do that.

Secondly, and more esoterically, if the indexes are all correct as you say they are, then perhaps you're dealing with a slow disk I/O for either the initial writes OR the transaction log area (undo in Oracle, logs in SQL Server, etc.).

As a sanity check I'd do two things. One, only update rows that do not already have the conditions set. Many DBMS products will happily perform physical disk I/O for a row that doesn't change (although many don't). Try it with the limit.

Two, apply the update in smaller batches. This can really help with log contention and with slower disks.

So, something like the following to initially try:

UPDATE auditdata 
   SET TATCallType = '12' 
     , TATCallUnit = '1' 
  FROM auditdata 
 WHERE TATCallType <> '12' 
   AND TATCallUnit <> '1'
   AND EXISTS( SELECT *
                 FROM Auditdata_sms_12 a_sns 
                WHERE a_sns.id = auditdata.ID )

If you want to do batches, in SQL Server it's pretty easy:

SET ROWCOUNT 2000

UPDATE ...

(run continually in a loop via T-SQL or by hand until @@ROWCOUNT = 0)

SET ROWCOUNT 0