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 1234»»»

Hash Match Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 11:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:14 PM
Points: 31,036, Visits: 15,464
Comments posted to this topic are about the item Hash Match






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566638
Posted Thursday, May 1, 2014 1:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:21 PM
Points: 962, Visits: 2,189
Interesting one, thank you for the post, Steve.

(Glad to be a part of the Steve's 12, who got it right. Now at least I take a hint that I am not smart and I am not dumb either... well lets go with the latter )


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1566648
Posted Thursday, May 1, 2014 6:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
Before I answered this question, I read

[url=http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx][/url]

It reads, in part:

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.


then later:

The hash join first scans or computes the entire build input and then builds a hash table in memory.


I'm having trouble reconciling the official description with the "correct" answer.
Post #1566682
Posted Thursday, May 1, 2014 7:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 9:20 AM
Points: 825, Visits: 721
gbritton1 (5/1/2014)
Before I answered this question, I read

[url=http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx][/url]

It reads, in part:

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.


then later:

The hash join first scans or computes the entire build input and then builds a hash table in memory.


I'm having trouble reconciling the official description with the "correct" answer.


+1

I followed the same reference in my investigation on this QOTD. Any insight on this seemingly discrepant definition would be appreciated.

Brian
Post #1566718
Posted Thursday, May 1, 2014 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 1,333, Visits: 2,298
bkmsmith (5/1/2014)
gbritton1 (5/1/2014)
Before I answered this question, I read

[url=http://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx][/url]

It reads, in part:

The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.


then later:

The hash join first scans or computes the entire build input and then builds a hash table in memory.


I'm having trouble reconciling the official description with the "correct" answer.


+1

I followed the same reference in my investigation on this QOTD. Any insight on this seemingly discrepant definition would be appreciated.

Brian


As did I, after much digging to see what SQL Server defined as the 'build' input.

Post #1566723
Posted Thursday, May 1, 2014 8:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:29 AM
Points: 337, Visits: 303
See lots of discussion about why the right answer is...

Here is a reference to the right answer from TechNet BOL.
Looks like this answer to this question has not changed for at least 15 years...

http://technet.microsoft.com/en-us/library/aa237090(v=SQL.80).aspx

For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table. Output matches (or nonmatches) as dictated by the join type. If multiple joins use the same join column, these operations are grouped into a hash team.

Post #1566737
Posted Thursday, May 1, 2014 8:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
So, we have conflicting opinions! I suspect that the old Technet article is incorrect, or at least no longer correct. It only makes sense that SQL would build the hash table from the smaller input to minimize work.
Post #1566739
Posted Thursday, May 1, 2014 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:40 PM
Points: 112, Visits: 55
still unclear what the correct answer is. my textbook also mentions that "the hash table is built from the smaller input"
Post #1566758
Posted Thursday, May 1, 2014 9:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:29 AM
Points: 337, Visits: 303
gbritton1 (5/1/2014)
So, we have conflicting opinions! I suspect that the old Technet article is incorrect, or at least no longer correct. It only makes sense that SQL would build the hash table from the smaller input to minimize work.


Most likely this is convergence not conflict.

Here is a 2008 R2 article that says the same thing as the old one.
This and the last link I posted are actually listed as part of the SQL 2014 BOL but where not changed.
http://technet.microsoft.com/en-US/library/ms189582(v=SQL.105).aspx
For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table. Output matches (or nonmatches) as dictated by the join type. If multiple joins use the same join column, these operations are grouped into a hash team.


There are some sources that say the QA will try to choose the smaller set as the build input, but here is some truth from SQL 2012 BOL.

http://technet.microsoft.com/en-us/library/ms173815.aspx
When using the HASH Join hint:
If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords

So the first table in the ON statement is used unless REMOTE table exists or is specified.

IMHO: Considering the Join Hint documentation for HASH, the Query Optimizer would have to change the join order so that the smaller set is first (like any good Query writing utility would do) to do what the other article says it is doing.

Note: I could not find anything in the BOL articles unique to SQL 2014 so we are left to guess that SQL 2014 BOL just has pointers to the old articles because the information has not changed.
Post #1566759
Posted Thursday, May 1, 2014 10:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:29 AM
Points: 337, Visits: 303
jackson.fabros (5/1/2014)
still unclear what the correct answer is. my textbook also mentions that "the hash table is built from the smaller input"

Is this exactly what your textbook says?
Let's hope not since the build input should be made from the smaller set and the probe input from the remainder in a HASH JOIN.
A "hash table" would be something else but not sure how it would have different inputs.

There are even HASH JOIN where the build and probe inputs are the same thing...
Post #1566764
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse