Inner Join Between large and small table

Question

Post reply

Inner Join Between large and small table

Mak Mohan

SSC Eights!

Points: 994
More actions
December 22, 2007 at 5:40 am

#184968

Hi
I am using following query :
update tab1 set tab1.col2=tab2.col2 from tab1 inner join tab2 on tab1.col1 = tab2.col1
where tab1 is having 1200000 records
tab2 is having 900 records
Its taking around 5 min.
I am not getting whats happening.Plz help.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

Turn on the execution plan, and SET STATISTICS IO ON to see whats going on.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

What's going on is that you're updating the same columns used in the join... that's almost never a good thing because it will frequently cause the join to "reform" after any row is updated. Pretty high chance that you're updating an index, as well, because most folks use indexes where they expect to join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Yates SSCoach Points: 19738 More actions · Answer 3

Sorry Jeff, he's not...

update tab1 set tab1.col2=tab2.col2 from tab1 inner join tab2 on tab1.col1 = tab2.col1

can be more clearly written as (why doesn't anyone use the fixed-point font for code anymore?)

[font="Courier New"]

update tab1

set tab1.B = tab2.B

from tab1

inner join tab2

on tab1.A = tab2.A

[/font]

You should have an index on tab1.A and an index on tab2.A, or, if tab2 is quite wide, have a covering index on tab2.A and tab2.B.

Do you have any indices on tab1.B?