I have read that hash joins usually occur because there are no proper indexes on one or both tables involved in the join. Currently I have a hash join where the two tables involved use a clustered-index seek and the cost is the majotity of the whole query. Is there any way to convert this to a merge or nested loop?
You can experiment with adding a hint to force a nested loop join, and see what that does to the execution plan and performance. In some cases, hash joins can be more efficient than nested loop, but not very often. This experiment will tell you which join is better, and if the Query Optimizer is providing you good data or not. ----------------------------- Brad M. McGehee, MVP Webmaster SQL-Server-Performance.Com
there is nothing wrong with a hash join if it is properly used, and not inadvertent hash and merge joins are best if: 1) the entire table is involved, ie, no SARG 2) a SARG is specified on both tables and good indexes are available SQL Server has a really silly cost formula for loop joins in certain circumstance where the estimated row count is more than 130 rows. this makes it think that the hash is more expensive. in any case, the estimated cost shown in the plan is very poorly calibrated on modern systems, so that alone is not a reason to worry, if your hash join has a table or index scan on one or both tables when a good index is available, that is a reason to worry if your join has less than 500 rows, you can use a LOOP join hint
