SQL update - so frustrating! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL update - so frustrating!

Author

Topic

Speckled
Starting Member

13 Posts

Posted - 2008-04-14 : 11:43:01

Hi,

I am new, and have a feeling I'll be using this site a lot as I'm pretty bad when it comes to SQL!

I can do simple updates, but am having problems with the following. The query runs for what seems forever, 5 hours + at which point it eventually errors telling me it's ran out of space in the 'tempdb'.

If I change this query to a select statement, it returns me the correct rows within about 7 seconds.

Why when I try the update does it just hang? Can you have multiple tables in an update query etc? I'm pretty new to SQL, so any help is really appreciated.

UPDATE SSDM_App.dbo.jobmatl

SET

uf_CMFileRef = test_app.dbo.jobmatl.uf_CMFileRef,

Uf_CMH = test_app.dbo.jobmatl.Uf_CMH,

Uf_CMNUP = test_app.dbo.jobmatl.Uf_CMNUP,

Uf_CMReference = test_app.dbo.jobmatl.Uf_CMReference,

Uf_CMW = test_app.dbo.jobmatl.Uf_CMW,

Uf_Pcs = test_app.dbo.jobmatl.uf_Pcs

FROM test_app.dbo.jobmatl

INNER JOIN test_app.dbo.jobroute

ON test_app.dbo.jobroute.job = jobmatl.job

AND

test_app.dbo.jobroute.suffix = test_app.dbo.jobmatl.suffix

AND

test_app.dbo.jobroute.oper_num = test_app.dbo.jobmatl.oper_num

INNER JOIN test_app.dbo.job

ON test_app.dbo.job.job = test_app.dbo.jobroute.job

AND

test_app.dbo.job.suffix = test_app.dbo.jobroute.suffix

INNER JOIN test_app.dbo.item

ON test_app.dbo.item.job = test_app.dbo.job.job

AND

test_app.dbo.item.suffix = test_app.dbo.job.suffix

INNER JOIN SSDM_App.dbo.item

ON Test_App.dbo.item.item = ssdm_app.dbo.item.item

WHERE Test_App.dbo.job.type = 'S'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 12:07:15

Where are you linking your query conditions to target table. Are simplying firing an update on entire columns of your SSDM_App.dbo.jobmatl table? I think there should be a join step onto this table. If you are still unclear post you table DDLs

Speckled
Starting Member

13 Posts

Posted - 2008-04-15 : 04:36:11

Hi, thanks for your reply.

I think you are correct, basically we have two databases.

Test_App

And

SSDM_App.

I want to update the SSDM_App.dbo.jobmatl table from the Test_App.dbo.jobmatl table on the link item.

Here is the code now, with a link to just one row. Literally, to speed the query up, I've selected one row in the Test_App database to update in the SSDM_App database, just still it hangs. Have I not joined something correctly to the SSDM_App.dbo.jobmatl table?

Any help/tips on how to do updates like this would be really handy!

Thanks,
Ricky

Speckled
Starting Member

13 Posts

Posted - 2008-04-15 : 07:49:24

Hi,

I now have the query working and it seems all the rows in the database have been updated.

Yet, the query still says it's running?! I don't know why? It's been running for 2.30 hours+. I don't know whether to stop it, as I would have thought the query would say complete once it's finished! What is it doing?! :(

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-15 : 11:05:40

If you stop it it'll roll back your updates. I'd let it complete (it should say complete when finished). Performance of the update depends on various items such as number of indexes on the table (it sounds like this may be where the process is at), number of rows being updated, other activity on the server, among others. Personally, I wouldn't stop the process after that much time UNLESS you believe there may be some issue.

Terry

Speckled
Starting Member

13 Posts

Posted - 2008-04-15 : 12:14:35

Hi,

I only just read the above, after stopping the process! It ran for around 4 hours, the rows were updated but then it just seemed to run forever, for no apparent reason.

I have a feeling it may be to do with triggers? Basically, the software we use allow us to update the triggers via a form, so could I disable it from updating the triggers in my update command (if that's possible?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 12:23:58

ALTER TABLE tablename DISABLE TRIGGER triggername disables the trigger on your table.

Speckled
Starting Member

13 Posts

Posted - 2008-04-16 : 04:13:10

Hi again,

Well to update, I decided to leave it last night and woke up in the morning and it had finished :).

It took 5 hours to run, yet it seemed to update the rows I wanted after about 10 minutes! Why did it run for so long afterwards?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 08:44:11

Did you had your show estimation plan option turned on? Have you had a look at what step was the costliest one?

Speckled
Starting Member

13 Posts

Posted - 2008-04-17 : 09:05:18

No estimation plan?

I think it was the triggers causing the slow down, next time I shall use the syntax you provided.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources