Update Query Runs Forever

[Neil]

I'm running an update query in SQL 7 from QA, and it runs forever. Has been
running for 20 minutes so far! The query is quite simple: update a single
field in a table, based on a join with another table. There are a total of
1200 rows that are affected (out of a total of 60,000 in the table). Should
not take 20 minutes! Also, retrieving the 1200 rows using the same criteria
is instantaneous.

Anyone have any idea what's going on? Here's my SQL:

UPDATE INVTRY
SET Web = 'REVIEW'
FROM INVTRY INNER JOIN tabStatus
ON INVTRY.Status = tabStatus.Statu s
WHERE INVTRY.Web = 'NEW'
AND tabStatus.ForWe b = 1
AND INVTRY.PRICE <0
AND Invtry.HoldInit IS NULL

[Plamen Ratchev]

Use an alias for the table to update, so there is no ambiguity:

UPDATE Invtry
SET Web = 'REVIEW'
FROM Invtry AS I
INNER JOIN tabStatus AS S
ON I.Status = S.Status
WHERE I.Web = 'NEW'
AND S.ForWeb = 1
AND I.Price <0
AND I.HoldInit IS NULL;

Also, you can rewrite the query as follows and see if it makes any
difference (although in general using the MS SQL syntax for UPDATE with FROM
is faster):

UPDATE Invtry
SET Web = 'REVIEW'
WHERE Web = 'New'
AND Price <0
AND HoldInit IS NULL
AND EXISTS(SELECT *
FROM tabStatus AS S
WHERE Invtry.Status = S.Status
AND S.ForWeb = 1);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Also, look for any locks on the table created by concurrent queries.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Neil]

I'll try this tonight after the users are out of it. But, really, faster or
not, my original query should have run, especially since it was just
updating 1200 rows. As it is, it ran for a couple of hours without
completing, and then I stopped it.

So there has to be something else going on.

Also, this syntax has been in place for years, and has never had a problem.
But it was usually run on smaller batches, usually less than 100 items.
Still, 1200 is not a large number.


"Plamen Ratchev" <Pl****@SQLStud io.comwrote in message
news:4p******** *************** *******@speakea sy.net...

Use an alias for the table to update, so there is no ambiguity:

UPDATE Invtry
SET Web = 'REVIEW'
FROM Invtry AS I
INNER JOIN tabStatus AS S
ON I.Status = S.Status
WHERE I.Web = 'NEW'
AND S.ForWeb = 1
AND I.Price <0
AND I.HoldInit IS NULL;

Also, you can rewrite the query as follows and see if it makes any
difference (although in general using the MS SQL syntax for UPDATE with
FROM is faster):

UPDATE Invtry
SET Web = 'REVIEW'
WHERE Web = 'New'
AND Price <0
AND HoldInit IS NULL
AND EXISTS(SELECT *
FROM tabStatus AS S
WHERE Invtry.Status = S.Status
AND S.ForWeb = 1);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

If you have concurrent queries on the table they may be blocking the update.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Also, if you have triggers on the table, they could be waiting for another
blocked resource.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Gert-Jan Strik]

Neil wrote:

>
I'm running an update query in SQL 7 from QA, and it runs forever. Has been
running for 20 minutes so far! The query is quite simple: update a single
field in a table, based on a join with another table. There are a total of
1200 rows that are affected (out of a total of 60,000 in the table). Should
not take 20 minutes! Also, retrieving the 1200 rows using the same criteria
is instantaneous.

Anyone have any idea what's going on? Here's my SQL:

UPDATE INVTRY
SET Web = 'REVIEW'
FROM INVTRY INNER JOIN tabStatus
ON INVTRY.Status = tabStatus.Statu s
WHERE INVTRY.Web = 'NEW'
AND tabStatus.ForWe b = 1
AND INVTRY.PRICE <0
AND Invtry.HoldInit IS NULL

I agree with Plamen. If the SELECT equivalent of this UPDATE returns the
1200 rows almost instantly, then most likely your UPDATE statement is
being blocked.

You can test this theory by running the SELECT equivalent if you include
a WITH (UPDLOCK) hint.

If so, you can use sp_lock and sp_lock2 to investigate possible
culplits.

If not blocking, then you should check the triggers on table INVTRY.

--
Gert-Jan
SQL Server MVP

[Neil]

"Gert-Jan Strik" <so***@toomuchs pamalready.nlwr ote in message
news:48******** *******@toomuch spamalready.nl. ..

Neil wrote:

>>
I'm running an update query in SQL 7 from QA, and it runs forever. Has
been
running for 20 minutes so far! The query is quite simple: update a single
field in a table, based on a join with another table. There are a total
of
1200 rows that are affected (out of a total of 60,000 in the table).
Should
not take 20 minutes! Also, retrieving the 1200 rows using the same
criteria
is instantaneous.

Anyone have any idea what's going on? Here's my SQL:

UPDATE INVTRY
SET Web = 'REVIEW'
FROM INVTRY INNER JOIN tabStatus
ON INVTRY.Status = tabStatus.Statu s
WHERE INVTRY.Web = 'NEW'
AND tabStatus.ForWe b = 1
AND INVTRY.PRICE <0
AND Invtry.HoldInit IS NULL

I agree with Plamen. If the SELECT equivalent of this UPDATE returns the
1200 rows almost instantly, then most likely your UPDATE statement is
being blocked.

You can test this theory by running the SELECT equivalent if you include
a WITH (UPDLOCK) hint.

If so, you can use sp_lock and sp_lock2 to investigate possible
culplits.

If not blocking, then you should check the triggers on table INVTRY.

--
Gert-Jan
SQL Server MVP

Plamen and Gert-Jan:

Yes, it was the trigger. I disabled the trigger, and the update query
executed in a couple of seconds. This didn't come to light before because it
seems to work fine with a few hundred records. But when the number got
larger (1200) it hung.

The problem with my trigger, perhaps, is that it uses a cursor.

See, the trigger updates the DateModified field, and it copies the entire
record to a history table. Because the record contains a timestamp field, I
can't use * to just copy the record. Instead, I have to list the fields
individually. Thus, I have the SQL to copy the record in its own stored
procedure, so that I have it in just one place. The trigger then just calls
the stored procedure. Since it's calling the stored procedure, I needed to
use a cursor to enable calling it for multiple records.

Here's my trigger:

ALTER Trigger INVTRY_Modified
On dbo.INVTRY
For Update
As

UPDATE INVTRY SET INVTRY.DateModi fied = getdate()
From INVTRY Inner Join Inserted
On INVTRY.[Index]=Inserted.[Index]

declare @Index1 int
declare curItems Cursor Local Forward_Only
For
SELECT [Index]
From Inserted
Open curItems
Fetch Next From curItems Into @Index1

WHILE @@Fetch_Status = 0
BEGIN
Execute Invtry_Archive @Index=@Index1
Fetch Next From curItems Into @Index1
END

Close curItems
Deallocate curItems
And here's the Invtry_Archive stored procedure that it calls:

ALTER Procedure Invtry_Archive

@Index int

AS
SET NOCOUNT ON

BEGIN
INSERT INTO Invtry_History ( [Index], DupedFromID, AUTHILL1, AUTHILL2,
TITLE, AUTHILL3, AUTHILL4, LOT, PAID, PRICE, BUYER, INCAT, LANG,
QTEDATE, Quote, GUIDE, YRPUB, INVDATE, TOPIC1, TOPIC2, TOPIC3, TOPIC4,
TOPIC5, TOPIC6, SHELF, JIT, MImage, HImage,
Feature, Track, HiRes, HiResCount, LowRes, Scan, SSEmail, SSPrint,
Status, ShootSpecs, Comment2, Owner, Location, Web,
WebStatusPendin g, Illus, EditStatus, CatStatus, HoldDate, HoldInit,
Comment, UpdateInd, ActivateDate, DeactivateDate,
AlternateItemID , WebAddedBatchID , WebModifiedBatc hID, FeaturedBook,
GiftBook1, GiftBook2, GiftBook3, AcquiredDate,
NewAcquisition, AllowDuplicate, WebAction, WebActionPendin g,
ApplyPendingWeb Action, DateWebActionAp plied, attFirstEdition ,
attSigned, attSignedPD, attAssociation, attAutograph, attIllustrated,
attIllustratedP D, attColorPlate, attColorPlatePD , attMaps,
attPhotographs, attSets, attSetsVols, attFineBinding, attFineBindingP D,
attForeignLang, attForeignLangP D, attPressBooks,
attPressBooksPD , attABEExport, attILABExport, attNonBook, Verification,
ShowcaseText, HeadlineQuote, SaleSrc, CatCode,
AdCode, ABEExport, ILABExport, DealerSrc, FullDescription DateStamp,
MiniDescription DateStamp, Desc_LC, DateCreated,
DateModified )
SELECT INVTRY.[Index], INVTRY.DupedFro mID, INVTRY.AUTHILL1 ,
INVTRY.AUTHILL2 , INVTRY.TITLE, INVTRY.AUTHILL3 , INVTRY.AUTHILL4 , INVTRY.LOT,
INVTRY.PAID, INVTRY.PRICE, INVTRY.BUYER, INVTRY.INCAT, INVTRY.LANG,
INVTRY.QTEDATE, INVTRY.Quote, INVTRY.GUIDE,
INVTRY.YRPUB, INVTRY.INVDATE, INVTRY.TOPIC1, INVTRY.TOPIC2,
INVTRY.TOPIC3, INVTRY.TOPIC4, INVTRY.TOPIC5, INVTRY.TOPIC6,
INVTRY.SHELF, INVTRY.JIT, INVTRY.MImage, INVTRY.HImage, INVTRY.Feature,
INVTRY.Track, INVTRY.HiRes, INVTRY.HiResCou nt,
INVTRY.LowRes, INVTRY.Scan, INVTRY.SSEmail, INVTRY.SSPrint,
INVTRY.Status, INVTRY.ShootSpe cs, INVTRY.Comment2 ,
INVTRY.Owner, INVTRY.Location , INVTRY.Web, INVTRY.WebStatu sPending,
INVTRY.Illus, INVTRY.EditStat us, INVTRY.CatStatu s,
INVTRY.HoldDate , INVTRY.HoldInit , INVTRY.Comment, INVTRY.UpdateIn d,
INVTRY.Activate Date, INVTRY.Deactiva teDate,
INVTRY.Alternat eItemID, INVTRY.WebAdded BatchID,
INVTRY.WebModif iedBatchID, INVTRY.Featured Book, INVTRY.GiftBook 1,
INVTRY.GiftBook 2, INVTRY.GiftBook 3, INVTRY.Acquired Date,
INVTRY.NewAcqui sition, INVTRY.AllowDup licate, INVTRY.WebActio n,
INVTRY.WebActio nPending, INVTRY.ApplyPen dingWebAction,
INVTRY.DateWebA ctionApplied, INVTRY.attFirst Edition, INVTRY.attSigne d,
INVTRY.attSigne dPD, INVTRY.attAssoc iation, INVTRY.attAutog raph,
INVTRY.attIllus trated, INVTRY.attIllus tratedPD, INVTRY.attColor Plate,
INVTRY.attColor PlatePD, INVTRY.attMaps, INVTRY.attPhoto graphs,
INVTRY.attSets, INVTRY.attSetsV ols, INVTRY.attFineB inding,
INVTRY.attFineB indingPD, INVTRY.attForei gnLang, INVTRY.attForei gnLangPD,
INVTRY.attPress Books, INVTRY.attPress BooksPD,
INVTRY.attABEEx port, INVTRY.attILABE xport, INVTRY.attNonBo ok,
INVTRY.Verifica tion, INVTRY.Showcase Text, INVTRY.Headline Quote,
INVTRY.SaleSrc, INVTRY.CatCode, INVTRY.AdCode, INVTRY.ABEExpor t,
INVTRY.ILABExpo rt, INVTRY.DealerSr c,
INVTRY.FullDesc riptionDateStam p, INVTRY.MiniDesc riptionDateStam p,
INVTRY.Desc_LC, INVTRY.DateCrea ted, INVTRY.DateModi fied
FROM INVTRY
Where [Index]=@Index
END

IF @@ERROR 0 GOTO on_error

RETURN(0)

on_error:
RETURN(1)
What would be great would be if I could copy the record without having to
list all the fields. But because I have to exclude the timestamp field, I
have to list all the fields except that. (Why don't they just make it so you
could do "* except [field list]"? That would be superb!)

Anyway, so I suppose the solution would be to move the above SQL to my
trigger and nix the cursor. Perhaps that would solve the problem.

Thoughts? Suggestions?

Thanks!

Neil

[Plamen Ratchev]

Just move the insert statement to the trigger body and remove the cursor and
the stored procedure. There is no need at all to process data one row at a
time, a single statement can handle it all.

Also, if possible move the update to DateModified to your application logic
(for example, if you have a stored procedure that performs the updates on
the table add the update to the column there). That way the trigger will
have less work to do and will complete faster.

Listing all columns in a statement is good. Using "*" is not a good idea and
can lead to more trouble down the road.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Neil]

"Plamen Ratchev" <Pl****@SQLStud io.comwrote in message
news:Sq******** *************** *******@speakea sy.net...

Just move the insert statement to the trigger body and remove the cursor
and the stored procedure. There is no need at all to process data one row
at a time, a single statement can handle it all.

As noted, the reason I had it in a stored procedure was so that there would
be one place that listed all the fields, in case the fields change, so I
don't have to update it in two places. But, as it is, I'm not using the SP
except through the trigger, so, yeah, I guess I can move it.

Also, if possible move the update to DateModified to your application
logic (for example, if you have a stored procedure that performs the
updates on the table add the update to the column there). That way the
trigger will have less work to do and will complete faster.

Listing all columns in a statement is good. Using "*" is not a good idea
and can lead to more trouble down the road.

Just curious: what kind of trouble down the road would using * cause? I
would think it would prevent trouble down the road since, if the fields
change, it automatically uses the current fields at all times. What kind of
trouble are you referring to.

Also, a question: do you have any idea why the update query and trigger
worked when there were 200 rows being updated, but not 1200?

Thanks!

Neil

>
HTH,

Plamen Ratchev
http://www.SQLStudio.com