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]

Re: Update Query Runs Forever

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]

Re: Update Query Runs Forever

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

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Neil]

Re: Update Query Runs Forever

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" <Plamen@SQLStud io.comwrote in message
news:4pSdnTHNk7 AtxNjVnZ2dnUVZ_ gCdnZ2d@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]

Re: Update Query Runs Forever

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

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Plamen Ratchev]

Re: Update Query Runs Forever

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]

Re: Update Query Runs Forever

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]

Re: Update Query Runs Forever


"Gert-Jan Strik" <sorry@toomuchs pamalready.nlwr ote in message
news:484596C5.E F33048B@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]

Re: Update Query Runs Forever

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]

Re: Update Query Runs Forever


"Plamen Ratchev" <Plamen@SQLStud io.comwrote in message
news:SqKdnd4sC8 z4YdjVnZ2dnUVZ_ vjinZ2d@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

[Plamen Ratchev]

Re: Update Query Runs Forever

"Neil" <nospam@nospam. netwrote in message
news:jVn1k.4024 $co7.2050@nlpi0 66.nbdc.sbc.com ...

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.
>

If you add a column to one of the tables, the statement using "*" will cause
your trigger to fail. You make the assumption that someone who will change
one of the tables later on will know about this trigger and the process of
archiving to another table, but what if not? Even you did not think about
the trigger when seeing this performance problem at first...

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?
>

If you have concurrent users running queries on the table, a transaction
that updates 200 rows may have a chance for time span to complete in between
user queries. But a transaction that updates 1200 rows one at a time takes
more than 10 times longer and gets blocked. A set based update may be very
fast for 1200 rows, but the cursor you use is not.

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[Plamen Ratchev]

Re: Update Query Runs Forever

>

If you have concurrent users running queries on the table, a transaction
that updates 200 rows may have a chance for time span to complete in
between user queries. But a transaction that updates 1200 rows one at a
time takes more than 10 times longer and gets blocked. A set based update
may be very fast for 1200 rows, but the cursor you use is not.
>

In the above I meant "insert rows one at a time", since you use the cursor
to insert...

Plamen Ratchev

http://www.SQLStudio.com

[Neil]

Re: Update Query Runs Forever

>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.
>>

>
If you add a column to one of the tables, the statement using "*" will
cause your trigger to fail. You make the assumption that someone who will
change one of the tables later on will know about this trigger and the
process of archiving to another table, but what if not? Even you did not
think about the trigger when seeing this performance problem at first...

Forgive my ignorance, but why would * cause the trigger to fail? Why
wouldn't * just copy all fields?

>

>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?
>>

>
If you have concurrent users running queries on the table, a transaction
that updates 200 rows may have a chance for time span to complete in
between user queries. But a transaction that updates 1200 rows one at a
time takes more than 10 times longer and gets blocked. A set based update
may be very fast for 1200 rows, but the cursor you use is not.

No, this was done in the middle of the night, when no one else was on the
system. It ran for a couple of hours last night, and then I killed it. There
was no possibility of concurrent users. That's why I'm perplexed over it
failing with 1200 rows, but working with 200.

Thanks,

Neil

[Plamen Ratchev]

Re: Update Query Runs Forever

"Neil" <nospam@nospam. netwrote in message
news:klp1k.2607 $ZE5.2234@nlpi0 61.nbdc.sbc.com ...

>
Forgive my ignorance, but why would * cause the trigger to fail? Why
wouldn't * just copy all fields?
>

If you add a column to table Invtry then using "*" to insert into your
archive table will fail. See the example below:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol CHAR(1));

INSERT INTO Foo VALUES(1, 'a');
INSERT INTO Foo VALUES(2, 'b');

GO

CREATE TABLE FooHistory (
keycol INT PRIMARY KEY,
datacol CHAR(1));

GO

-- Completes fine, tables have same structure
INSERT INTO FooHistory
SELECT * FROM Foo;

GO

DELETE FooHistory;

GO

-- Add one more column to Foo
ALTER TABLE Foo ADD datacol2 CHAR(1);

GO

-- Fails because columns do not match
INSERT INTO FooHistory
SELECT * FROM Foo;

>
No, this was done in the middle of the night, when no one else was on the
system. It ran for a couple of hours last night, and then I killed it.
There was no possibility of concurrent users. That's why I'm perplexed
over it failing with 1200 rows, but working with 200.
>

I think this is still due to locking. Starting with version 7.0 SQL Server
supports row level locking. A small update can place only row (or page)
level locks. But a large update can escalate the row/page level locks to
full table level lock. Since your stored procedure insert (which needs to
obtain shared lock to read the data) is executed while the trigger
transaction is not completed, if you have an exclusive update lock on the
table it cannot read the data.

As Gert-Jan indicated you can use sp_lock to check the locks.

HTH,

Plamen Ratchev

http://www.SQLStudio.com