why does this happen??? - 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)

why does this happen???

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 05:52:37

[code]
@temp has 8 rows
crmPotentialTurnover has 20413 rows

MDLYear = 2005 2 rows
MDLYear = 2004 1 row
MDLYear = null 20410 rows

first exec plan is for this query. when adding the hash join hint it runs in 500 ms.

update crmPotentialTurnover
set MDLTotalPrice = t.SumTotalPrice
from crmPotentialTurnover PT
left hash join @temp t on t.MDLYear = PT.ReportDate and t.org_id = PT.org_id
where (PT.org_id = @org_id or @org_id is null)

second exec plan is for this query. this is the default exec plan. it takes around 1 sec to update +- 100 ms:

update crmPotentialTurnover
set MDLTotalPrice = t.SumTotalPrice
from crmPotentialTurnover PT
left join @temp t on t.MDLYear = PT.ReportDate and t.org_id = PT.org_id
where (PT.org_id = @org_id or @org_id is null)
[/code]

now... my question is:
WHY does the second query have a table scan of 160000+ rows?? i don't get.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-11-15 : 06:35:47

No PK created on @temp?

We have a house rule to always create PKs on #TEMP and @VAR tables, as its always seemed to make a difference to me (even for small tables)

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-15 : 07:00:53

163304 = 20413 * 8

The execution plan for nested loops shows a different row count for the inner loop depending on whether you're looking at the estimated plan or the actual plan. In the estimated plan it shows the number of rows estimated for each outer loop row. For the actual plan it shows the total number of rows for all outer loop rows.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 07:16:10

No PK on @temp... hmmm... i'll add it.
EDIT:
PK didn't help any...

it's an acctual plan.
but what i don't understand why does it do a loop join?? seems like an overkill to me...

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-15 : 07:32:14

Overkill? Compared to building a 20000 row hash table, probing it 8* times and then sorting the result? I'm amazed the hash join is faster!
What happens if you force the hash join the other way round?


update 	crmPotentialTurnover
set 	MDLTotalPrice = t.SumTotalPrice
from 	@temp t 
	right hash join crmPotentialTurnover PT on t.MDLYear = PT.ReportDate and t.org_id = PT.org_id 
where	(PT.org_id = @org_id or @org_id is null)

* actually, I'm a bit unsure how it would do a Hash Match/Left Join. Does it mark each row that's matched in the hash table and then sweep the table afterward to get all the nonmatched rows?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 07:42:53

same thing as with left.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 07:48:11

added stuff to the pic.

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-15 : 07:54:55

quote:
Originally posted by spirit1
same thing as with left.

That's strange. I thought using local join hints forced the execution plan to keep the tables in the order specified.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 08:00:18

well @temp is now on top of the exec plan any crmPotentialTurnover is on the bottom.
everything else is the same

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-15 : 08:13:25

quote:
Originally posted by spirit1

well @temp is now on top of the exec plan any crmPotentialTurnover is on the bottom.
everything else is the same

But the order of the inputs for a hash match operator determines which is used to build the hash table and which to probe it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 08:19:20

hmmm.... you're right... after looking at statistics i see that right join is faster than the left by 200 ms.
that's good

thanx General!

well today i dived into a wonderfull world of join hints

preety cool...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources