"Gert-Jan Strik" <so***@toomuchspamalready.nlwrote in message
news:48***************@toomuchspamalready.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.Status
WHERE INVTRY.Web = 'NEW'
AND tabStatus.ForWeb = 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.DateModified = 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,
WebStatusPending, Illus, EditStatus, CatStatus, HoldDate, HoldInit,
Comment, UpdateInd, ActivateDate, DeactivateDate,
AlternateItemID, WebAddedBatchID, WebModifiedBatchID, FeaturedBook,
GiftBook1, GiftBook2, GiftBook3, AcquiredDate,
NewAcquisition, AllowDuplicate, WebAction, WebActionPending,
ApplyPendingWebAction, DateWebActionApplied, attFirstEdition,
attSigned, attSignedPD, attAssociation, attAutograph, attIllustrated,
attIllustratedPD, attColorPlate, attColorPlatePD, attMaps,
attPhotographs, attSets, attSetsVols, attFineBinding, attFineBindingPD,
attForeignLang, attForeignLangPD, attPressBooks,
attPressBooksPD, attABEExport, attILABExport, attNonBook, Verification,
ShowcaseText, HeadlineQuote, SaleSrc, CatCode,
AdCode, ABEExport, ILABExport, DealerSrc, FullDescriptionDateStamp,
MiniDescriptionDateStamp, Desc_LC, DateCreated,
DateModified )
SELECT INVTRY.[Index], INVTRY.DupedFromID, 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.HiResCount,
INVTRY.LowRes, INVTRY.Scan, INVTRY.SSEmail, INVTRY.SSPrint,
INVTRY.Status, INVTRY.ShootSpecs, INVTRY.Comment2,
INVTRY.Owner, INVTRY.Location, INVTRY.Web, INVTRY.WebStatusPending,
INVTRY.Illus, INVTRY.EditStatus, INVTRY.CatStatus,
INVTRY.HoldDate, INVTRY.HoldInit, INVTRY.Comment, INVTRY.UpdateInd,
INVTRY.ActivateDate, INVTRY.DeactivateDate,
INVTRY.AlternateItemID, INVTRY.WebAddedBatchID,
INVTRY.WebModifiedBatchID, INVTRY.FeaturedBook, INVTRY.GiftBook1,
INVTRY.GiftBook2, INVTRY.GiftBook3, INVTRY.AcquiredDate,
INVTRY.NewAcquisition, INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.ApplyPendingWebAction,
INVTRY.DateWebActionApplied, INVTRY.attFirstEdition, INVTRY.attSigned,
INVTRY.attSignedPD, INVTRY.attAssociation, INVTRY.attAutograph,
INVTRY.attIllustrated, INVTRY.attIllustratedPD, INVTRY.attColorPlate,
INVTRY.attColorPlatePD, INVTRY.attMaps, INVTRY.attPhotographs,
INVTRY.attSets, INVTRY.attSetsVols, INVTRY.attFineBinding,
INVTRY.attFineBindingPD, INVTRY.attForeignLang, INVTRY.attForeignLangPD,
INVTRY.attPressBooks, INVTRY.attPressBooksPD,
INVTRY.attABEExport, INVTRY.attILABExport, INVTRY.attNonBook,
INVTRY.Verification, INVTRY.ShowcaseText, INVTRY.HeadlineQuote,
INVTRY.SaleSrc, INVTRY.CatCode, INVTRY.AdCode, INVTRY.ABEExport,
INVTRY.ILABExport, INVTRY.DealerSrc,
INVTRY.FullDescriptionDateStamp, INVTRY.MiniDescriptionDateStamp,
INVTRY.Desc_LC, INVTRY.DateCreated, INVTRY.DateModified
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