Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lionofdezert
Aged Yak Warrior
885 Posts |
 Posted - 2008-12-15 : 05:24:46
|
| In performance point of view Hash join is much faster then Nested Loop Join, While query small tables query optimizer use Nested Loop Join But if i use query hint to force Hash join, Logical Read is much less then Nested loop but it takes more cpu time to execute.When Should i force Hash join by providing query hint. Thanx in advance |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-15 : 05:40:46
|
Never, in my opinion, unless you know exactly what you are doing.There is a reason SQL Server used Nested Loop Join in favor of Hash Join.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-12-15 : 05:47:36
|
| Yes Peso! i need the reason behind usage of Nested Loop ??? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-15 : 05:52:20
|
Most probably the number of records for the inner table in the join.Also statistics and other things make a difference when query engine will decide for an execution plan.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-12-15 : 05:58:48
|
| Most probably the number of records for the inner table in the join.Also statistics and other things make a difference when query engine will decide for an execution plan.OTHER THINGS ??? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-15 : 05:59:56
|
IO, disk usage, buffers in memory, data cache and so on.If you open Books Online you can find more information.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-12-15 : 06:17:46
|
| Thanx Peso |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-15 : 09:51:53
|
quote:
Originally posted by lionofdezert
Yes Peso! i need the reason behind usage of Nested Loop ???
Because Nested Loops works good with small tables and hash joins are really good when dealing with different large sets when indexes are insufficient. But it depends on In-Memory hash joins,Grace hash joins or recursive hash joins. |
|
|
|
|
|
|
When should i use HASH query hint