Hash Match

Question

Hash Match

Viewing 4 posts - 31 through 35 (of 35 total)

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

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robert.diley SSC Eights! Points: 860 More actions · Answer 2

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.

Eric M Russell SSC Guru Points: 125618 More actions · Answer 3

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.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/