Helo with Update

Question

Helo with Update

chinn

SSCrazy

Points: 2840
More actions
November 11, 2009 at 12:28 pm

#216691

Hello Everybody,
We have Sql2008 EE and 32bit.
We are running this update...
Looks simple but it take 6 hrs still runs and tempdb goes close to 100gb before we cancel...
and status is runnable to start with and goes to suspended and will be there CPU time and diskIO
Increases...
If anybody can shed any light would help..
UPDATE tbl1
SET tbl1.col1 = db2.dbo.tbl2.col1 ,
[tbl1].[col2] = db2.dbo.tbl2.col2
FROM tbl1, tbl2
where tbl1.col3 =db2.dbo.tbl2.col3
and tbl1.col4 = db2.dbo.tbl2.col4
tbl1 has 1572763 rows
tbl2 on db2 has 17499914 rows
This update takes for ever and also the tempdb is growing like 100 gb or more...
Any help on this will be appreciated..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Can you post the table definitions and the execution plan for the update?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 2

chinn (11/11/2009)
UPDATE tbl1
SET tbl1.col1 = db2.dbo.tbl2.col1 ,
[tbl1].[col2] = db2.dbo.tbl2.col2
FROM tbl1, tbl2
where tbl1.col3 =db2.dbo.tbl2.col3
and tbl1.col4 = db2.dbo.tbl2.col4

Your statement looks a bit strange to me. You used two local tables of one database within your (old style) FROM clause and refer to a table of a table located in another database within your WHERE and SET clauses.

Never tried this, so I don't really know what this really causes, but it smells like an Cartesian product.

Greets

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

UPDATE tbl1
SET tbl1.col1 = db2.dbo.tbl2.col1 ,
[tbl1].[col2] = db2.dbo.tbl2.col2
FROM tbl1, tbl2
where tbl1.col3 =db2.dbo.tbl2.col3
and tbl1.col4 = db2.dbo.tbl2.col4

To start with, why Not rewrite the script like this

UPDATE tbl1

SET tbl1.col1 = db2.dbo.tbl2.col1 ,

[tbl1].[col2] = db2.dbo.tbl2.col2

FROM tbl1

Inner join tbl2

ON tbl1.col3 =db2.dbo.tbl2.col3

and tbl1.col4 = db2.dbo.tbl2.col4

Then some thing else needs to be looked into, have Indexes on the Col3 and Col4 on both the tables, and have stats on the Col1 and Col2 on Tbl1 and have Indexes or include those columns as included Columns in the existing (Col3 and Col4) Indexes.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty