473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Update Query Runs Forever

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
Jun 27 '08 #1
13 4125
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

Jun 27 '08 #2
Also, look for any locks on the table created by concurrent queries.

HTH,

Plamen Ratchev
http://www.SQLStudio.com
Jun 27 '08 #3
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****@SQLStudio.comwrote in message
news:4p******************************@speakeasy.ne t...
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

Jun 27 '08 #4
If you have concurrent queries on the table they may be blocking the update.

HTH,

Plamen Ratchev
http://www.SQLStudio.com
Jun 27 '08 #5
Also, if you have triggers on the table, they could be waiting for another
blocked resource.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #6
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
Jun 27 '08 #7

"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
Jun 27 '08 #8
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

Jun 27 '08 #9

"Plamen Ratchev" <Pl****@SQLStudio.comwrote in message
news:Sq******************************@speakeasy.ne t...
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


Jun 27 '08 #10
"Neil" <no****@nospam.netwrote in message
news:jV*****************@nlpi066.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

Jun 27 '08 #11
>
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

Jun 27 '08 #12
>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
Jun 27 '08 #13
"Neil" <no****@nospam.netwrote in message
news:kl*****************@nlpi061.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

Jun 27 '08 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: David Berman | last post by:
I'm having a problem with an update operation in a stored procedure. It runs so slowly that it is unusable, unless I comment a part out in which case it is very fast. However, I need the whole...
3
by: Dan Berlin | last post by:
I have two tables: T1 : Key as bigint, Data as char(20) - size: 61M records T2 : Key as bigint, Data as char(20) - size: 5M records T2 is the smaller, with 5 million records. They both have...
5
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
6
by: Sparticus | last post by:
I have a database that isn't very big. It has about 2400 rows in it. I try and do an update like this one below (it looks big, but it's really not if you look at it) : UPDATE jobs SET...
1
by: Greg Strong | last post by:
Hello All, Any reason why a select query that runs will not run as an update query? What I've done is created a select query that runs successfully. The query has several custom functions to...
3
by: John Cosmas | last post by:
I have a DATATABLE which I have populated in my application, and I need it written out to a particular table I specify in my ACCESS database. My code works to the point of the MERGE and UPDATE,...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
1
by: gkinu | last post by:
I have 2 tables with a parent-child relationship. Parent table's primary key field is Entry_no and is of type VarChar(50). This parent table has about 50 fields. The child has 3 fields, Entry_No...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.