Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Join Operations – Hash Match


Join Operations – Hash Match

Author
Message
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26822 Visits: 18338
Comments posted to this topic are about the item Join Operations – Hash Match



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 969
Fantastic article, Jason!

I know what a challenge it is to set up a realistic test scenario that illustrates the behavior you're trying to explain, and you did a great job with that. I also learned some new (to me) details about the way the hash match works.

Bookmarking now for future reference :-)



SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26822 Visits: 18338
Thanks Troy. Much appreciated.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

dwain.c
dwain.c
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6087 Visits: 6431
Great article Jason! An enjoyable and informative read.

One question. In the section where you're describing left-deep/right-deep, is it safe to assume you're saying that the context only applies to a Hash Merge? In the table column, you don't refer to left-deep/right-deep like you do in the first two rows. Clearly though there is some impact caused to all the JOIN types by the different predicates and in each case it seems to be in the same direction.

A very tiny complaint too. You made me search for your first article because you didn't hyperlink it into this one! Shame on you. I found it though and I'm reading it now.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26822 Visits: 18338
dwain.c (3/4/2014)
Great article Jason! An enjoyable and informative read.

One question. In the section where you're describing left-deep/right-deep, is it safe to assume you're saying that the context only applies to a Hash Merge? In the table column, you don't refer to left-deep/right-deep like you do in the first two rows. Clearly though there is some impact caused to all the JOIN types by the different predicates and in each case it seems to be in the same direction.

A very tiny complaint too. You made me search for your first article because you didn't hyperlink it into this one! Shame on you. I found it though and I'm reading it now.


Thanks Dwain.

Per the left deep right deep and effect on the other join operators - I will have to look at that closer.

Sorry about that link. It was at the top (of the article) and probably could have been more visible.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

dwain.c
dwain.c
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6087 Visits: 6431
SQLRNNR (3/4/2014)


Sorry about that link. It was at the top (of the article) and probably could have been more visible.


Pardon my aging eyes. The link is there I just didn't see it. Complaint withdrawn.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26822 Visits: 18338
dwain.c (3/4/2014)
SQLRNNR (3/4/2014)


Sorry about that link. It was at the top (of the article) and probably could have been more visible.


Pardon my aging eyes. The link is there I just didn't see it. Complaint withdrawn.


No problem. I can see how it would easily be missed. It could be a lot more visible (probably by including the title of the article Wink ).



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

simon.dobner 32307
simon.dobner 32307
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 86
Hi Jason,

a quick question, because I'm just not getting it straight in my head.

You said "For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table"


Kimberly Tripp said [SQLSkills IE1, module 10 Indexing strategies, Slide 37, Join Strategies]

"Hash join
Two-phase operation (build, then probe): Build table(smaller set) and probe table (larger set) allowing SQL to join extremely large sets - in MEMORY (can spill)"



If you are both correct, which is a distinct possibility, that would indicate that you are talking about different things.

When you say 'first(top) input', do you mean the first table the from clause, or the ordering in the plan post optimisation, or something else ?


Can you help me to clear up my confusion between the differences [in my understanding] between your article and Kimberly's course?

Thanks
Simon
SQLRNNR
SQLRNNR
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26822 Visits: 18338
I haven't taken any of the IE courses by SQLSkills so couldn't compare what is actually on the slides and the context.

But from what you posted it looks like the same thing is being said - just in a different way.

Both reference the hash being a two phase operation. Both statements reference the hash table as the first table being built (which is the top table in the execution plan) and the second table being the bottom input from the plan and that it probes the first table (in other words probes the hash table that was built from the top input).

First and second table are according to the execution plan and not the order you had them listed in your query.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

simon.dobner 32307
simon.dobner 32307
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 86
OK, Thanks, got it now.

'Input' is not necessarily the same as 'table in the from clause'

Regards
Simon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search