Hash match

Question

Post reply

Hash match

tony28

SSCertifiable

Points: 7114
More actions
April 14, 2014 at 5:29 am

#287674

Hello,
I checked Hash match in my query...
I saw that Probe Residual is this..
[MCS_MESDB].[dbo].[TB_HIST_STATION_CPM01].[COMMIT_NO] as [H].[COMMIT_NO]=[MCS_MESDB].[dbo].[TB_INSPECTION_CPM01].[COMMIT_NO] as .[COMMIT_NO]
in both table is this column in PK
I added to WHERE clause also, but there is still Hash Match.
Which another recommendation are for this ?
Can be problem in dynamic SQL ?
I found that is just join with one table is this OK and there is Merge Join, but if I join next table is there Hash match
Thank you

Viewing 5 posts - 1 through 5 (of 5 total)

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

tony28 SSCertifiable Points: 7114 More actions · Answer 1

one next question..

If I do join more table ... it is needed join like this :

Select * from t1
left join t2
on t2.column1=t1.column1
and t2.column2=t1.column2
left join t3
on t3.column1=t2.column1
and t3.column2=t2.column2

or can I join like this ?

Select * from t1
left join t2
on t2.column1=t1.column1
and t2.column2=t1.column2
left join t3
on t3.column1=t1.column1
and t3.column2=t1.column2

With first join I have better logical reads = subtree cost 0,32...

but with second join I have better Ex.plan without hash match = subtree cost 2,32...

So during this investigation it means that Hash can be better sometimes ? 🙂

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

tony28 (4/14/2014)
I added to WHERE clause also, but there is still Hash Match.
Which another recommendation are for this ?

Hash matches aren't a problem, and without an execution plan nothing useful can be said

Can be problem in dynamic SQL ?

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tony28 SSCertifiable Points: 7114 More actions · Answer 3

GilaMonster (4/14/2014)
tony28 (4/14/2014)
I added to WHERE clause also, but there is still Hash Match.
Which another recommendation are for this ?
Hash matches aren't a problem, and without an execution plan nothing useful can be said
Can be problem in dynamic SQL ?
No.

I know that without exe.plan you cannot help, but you can me say just advice, but I am sure that query is right, according to logical reads and time with parameter which is before update and rebuild ....

I found this during investigation

rebuild and update statistics were 2014 04 12 = this saturday

with parameter, which has update statistics = it means date 20140411 or 20131211 and etc..

this has Hash Match

Table 'TB_table1'. Scan count 1, logical reads 16,

Table 'TB_table2'. Scan count 1, logical reads 16

Table 'TB_table3'. Scan count 1, logical reads 426

Table 'TB_table4'. Scan count 1, logical reads 4

example with parameter, which doesnt have update statistics = it means date 20140414 = today

what I see scan count is number of rows in table4

this doesnt have Hash Match

Table 'TB_table1'. Scan count 393, logical reads 1363

Table 'TB_table2'. Scan count 392, logical reads 1823

Table 'TB_table3'. Scan count 392, logical reads 1588

Table 'TB_table4'. Scan count 1, logical reads 12

So it means that I have to do update statistics or rebuild and will be ok.. yes ? I cannot do now, because is production time.

So maybe will be better update statistics more than one per week..

tony28 SSCertifiable Points: 7114 More actions · Answer 4

tony28

SSCertifiable

Points: 7114

April 14, 2014 at 6:35 am

#1705736

miss