Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Join Operations – Hash Match Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 12:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 17,963, Visits: 15,964
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
Post #1547172
Posted Tuesday, March 4, 2014 9:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:26 AM
Points: 1,336, Visits: 799
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



Post #1547397
Posted Tuesday, March 4, 2014 9:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 17,963, Visits: 15,964
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
Post #1547402
Posted Tuesday, March 4, 2014 6:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1547610
Posted Tuesday, March 4, 2014 7:49 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 17,963, Visits: 15,964
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
Post #1547623
Posted Tuesday, March 4, 2014 8:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1547626
Posted Tuesday, March 4, 2014 8:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 17,963, Visits: 15,964
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 ;) ).




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1547628
Posted Saturday, May 3, 2014 1:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 9, 2014 11:35 PM
Points: 27, 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
Post #1567229
Posted Sunday, May 4, 2014 5:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 17,963, Visits: 15,964
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
Post #1567376
Posted Sunday, May 4, 2014 5:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 9, 2014 11:35 PM
Points: 27, Visits: 86
OK, Thanks, got it now.

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

Regards
Simon
Post #1567377
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse