Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Hash Match Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 12:17 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 @ 12:34 PM
Points: 3,840, Visits: 8,780
Jeff Moden (5/5/2014)
Steve Jones - SSC Editor (5/5/2014)
Jeff Moden (5/3/2014)
I think a much better question would be, "What determines what will be the first (top) table (according to books online) in a hash match join"?


There's no reliable way to determine this. It should be the smaller input most of the time, but sometimes the optimizer doesn't get that one and that usually means the DBA's phone rings with a complaint.


Yep... that's my whole point. It would be wonderful to see a QOTD where the correct answer is, "It Depends".


Sorry - you were wrong
Correct answer: The first input
Explanation: In a hash match join, the first input is used to build the hash table and the second input is used to probe the hash table. Size does not matter for this operator.

But technical details of the algorithm and experimentation seem to suggest that size does matter when it comes to inserting a hash table. It favors smaller inputs.




"The universe may be complicated, but life is only as complicated as you choose to make it."
Post #1567666
Posted Monday, May 5, 2014 3:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:43 AM
Points: 9,692, Visits: 11,733
Jeff Moden (5/5/2014)
Steve Jones - SSC Editor (5/5/2014)
Jeff Moden (5/3/2014)
I think a much better question would be, "What determines what will be the first (top) table (according to books online) in a hash match join"?


There's no reliable way to determine this. It should be the smaller input most of the time, but sometimes the optimizer doesn't get that one and that usually means the DBA's phone rings with a complaint.


Yep... that's my whole point. It would be wonderful to see a QOTD where the correct answer is, "It Depends".

Well, no, the correct answer in this case would be "The table which the optimizer thinks is smallest", because that is always the first input accessed by the hash join operator (even when the optimizer gets it wrong and this input is very much bigger than the other input so that it doesn't end up forming the build table).
Or did you mean "It depends what the optimizer thinks is best"?


Tom
Post #1567720
Posted Monday, May 5, 2014 3:33 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 41,426, Visits: 38,755
TomThomson (5/5/2014)
Or did you mean "It depends what the optimizer thinks is best"?


That would be the the one because I've also seen it where the optimizer reused a plan with hashes that it probably shouldn't have.


--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1567724
Posted Wednesday, May 14, 2014 8:54 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 25, 2016 12:30 PM
Points: 610, Visits: 231
I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.

From p. 88:

"we can discuss the Hash Match join operator.
It occurs when SQL Server has to join two large data sets, and decides to do so by first
hashing the rows from the smaller of the two data sets, and inserting them into a hash
table. It then processes the larger data set, one row at a time, against the hash table,
looking for matches, indicating the rows to be joined."

I am inclined to trust this source.
Post #1570888
Posted Wednesday, May 14, 2014 9:16 AM


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 @ 12:34 PM
Points: 3,840, Visits: 8,780
robert.diley (5/14/2014)
I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.

From p. 88:

"we can discuss the Hash Match join operator.
It occurs when SQL Server has to join two large data sets, and decides to do so by first
hashing the rows from the smaller of the two data sets, and inserting them into a hash
table. It then processes the larger data set, one row at a time, against the hash table,
looking for matches, indicating the rows to be joined."

I am inclined to trust this source.

Grant provides the best answer, but it makes no difference who says what. There are many occasions where "it depends" and technical documentation provides a simplified answer that at best is only generally true or an answer that's intended to be true according to specifications but isn't actually. Which input SQL Server will choose for the hash table can be easily confirmed by setting up a couple of sample tables, one big and one small, then examine the execution plans for multiple selects with different join configurations. My eyes are my most trusted source.



"The universe may be complicated, but life is only as complicated as you choose to make it."
Post #1570898
Posted Wednesday, May 14, 2014 6:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 7,712, Visits: 11,054
robert.diley (5/14/2014)
I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.

From p. 88:

"we can discuss the Hash Match join operator.
It occurs when SQL Server has to join two large data sets, and decides to do so by first
hashing the rows from the smaller of the two data sets, and inserting them into a hash
table. It then processes the larger data set, one row at a time, against the hash table,
looking for matches, indicating the rows to be joined."

I am inclined to trust this source.


It's defintely a good source, but please check the detailed post I made earlier in this discussion. There is a difference between what the optimizer attempts (which is to make sure that the smaller table is used as the top, build input) and the reality (where the first, topmost input is used for the build phase, even when the optimizer got it wrong).

BTW, I have been asked to be the technical editor for the next edition of Grant's book; I hope that makes me an equally trustworthy source.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1571081
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse