slow update Query(Primary Key)

30 pts.
Tags:
SQL
SQL performance
SQL Query
dear Friends when i run below query its take more than 10 minutes and still running.... update auditdata set TATCallType='12',TATCallUnit='1' from auditdata auditdata inner join Auditdata_sms_12 a_sns on auditdata.ID = a_sns.id auditdata is my original table in above table ID is primary key Auditdata_sms_12 is my temp table whats wrong in aobe SQL Query plz help me thanx in advance..

Answer Wiki

Thanks. We'll let you know when a new response is added.

Both auditdata and a_sns should generally have an index on their id column if your are joining based on these columns. There are exceptions. If there is an index on both columns, the next best thing to do would be to run an explain plan on the sql query to find out what the optimizer is doing. At that point you can pinpoint the problem.

[kccrosser]
How many rows are in each of the tables? Particularly the temp table.

First, why not use the following?

update auditdata set TATCallType=’12′,TATCallUnit=’1′ where ID in
(select id from Auditdata_sms_12 );

If there are a lot of rows in your temp table (like hundreds of thousands or millions?), you may need to break this into smaller chunks to avoid creating excessive rollback segments. It has been my experience that as rollback segments start getting really large, the transaction performance degrades exponentially.

Best is to break into smaller transactions, like:

declare cur cursor for select id from Auditdata_sms_12;
declare @id int
declare @i int
set @i = 0
open cur
fetch next from cur into @id
begin transaction
while @@fetch_status = 0
begin
update auditdata set TATCallType=’12′,TATCallUnit=’1′ where auditdata.ID = @id;
set @i = @i + 1
if (@i % 1000) = 0
begin — every 1000 updates, commit to database
commit transaction — close current transaction
begin transaction — start a new one
end
fetch next from cur into @id
end
commit transaction

This approach also has the benefit that we aren’t trying to create the join product table of Auditdata and Auditdata_sms_12.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    How about something like this: UPDATE auditdata SET TATCallType='12',TATCallUnit='1' FROM auditdata a WHERE EXISTS ( SELECT 1 FROM Auditdata_sms_12 WHERE id = a.id); With an index on Auditdata_sms_12.id the full table scan of the subselect (if you use where ID in (select...) ) could be avoided.
    69,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following