What happens after UPDATE statement?

Question

Post reply

What happens after UPDATE statement?

Mon-257021

SSC-Addicted

Points: 468
More actions
February 7, 2006 at 3:07 pm

#170883

Hi,
Does anyone know what happens after UPDATE statement updates the records? I have this UPDATE query in a stored procedure:
WHILE... there are RowId ranges to process
BEGIN
         SELECT range FROM a range table....
         UPDATE Table1
          SET Flag1 = 6
          ,Flag2 = 0
          ,Flag3 = 0
          ,Column1 = column2
          ,Column3 = column4
          WHERE RowId BETWEEN '1a' and 1'd'
           SET @RowCount = @@ROWCOUNT
         PRINT CONVERT(VARCHAR(100), @RowCount)
          UPDATE a flag in the range table so that the recently processed RowId range will no longer be processed again.
END
Table1 has 120million records so we used RowId to batch the processing so that the transaction log will not be used up. RowId is unique index. The UPDATE statement is in a loop so that it will process one RowId range at a time. We are using @@ROWCOUNT to display the number of records updated.
The problem is a range has approximately 6 million records. To check the progress we've executed a SELECT query to check how many records have already been updated in a range. We've found out that after 15 minutes all 6 million records have already been updated but the UPDATE statement still hasn't stopped. The UPDATE statement only stopped successfully after 5 hours.
Does any one know why the UPDATE statement completed only afte 5 hours even though all the records have been udpated already.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

check your execution plan !

I presume sqlserver in scanning your full table and suffers a bunch of waits !

"RowId is unique index" Is there a clustering index ?

You might want to post some DDL and statistics

"WHERE RowId BETWEEN '1a' and 1'd'" I presume '1a' and '1b' are _not_ static but are derived from "SELECT range FROM a range table.... "

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Mon-257021 SSC-Addicted Points: 468 More actions · Answer 2

Thanks. We've discovered that the table we are updating has update trigger. When we disabled the trigger the UPDATE statement ran faster.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

aha, the trigger issue

That's exactly the reason why one should always evaluate if a "procedure" should _always_ be executed when modifying data for the object.

Only if _that_ is the case, and the "procedure" is small and simple and should be executed within the transaction of the modification, only then a trigger might be a solution.

The nice thing with sqlserver-triggers is that you can shut them down if needed. e.g. for planned maintenance, and reactivate them afterwards to support normal day-to-day activities.

Now with SQL2005 this even goes for indexes.

Thanks for the feedback.