Re: Hash Joins vs. Nested Loops

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0302201025.670fb5b8_at_posting.google.com>...
> Someone I was working with told me the following. I was wondering if
> anyone has any comments, because I have not seen this in any
> articles/books.
>
> He said that when he tunes he generally likes to try to force nested
> loop joins over hash joins because these tend to increase performance?
> He has done this for a long time and he said that is based on
> experience.
>

There isn't an execution plan that is always fastest. Whichever plan requires the least amount of I/O and CPU resource will give you the best performance. Understanding data distribution should be the first step in SQL tuning. If you only need to access a few rows to satisfy the conditions in your SQL, nested loop will be 1000 times faster than hash join. If you need to access 10 million rows then hash join can be 10 or 100 times faster.

> Anyone else notice this? I have never seen this anywhere else. BTW, I
> know the difference between hash joins and nested loops and know when
> either is generally used by the optimizer.
Received on Fri Feb 21 2003 - 22:14:51 CST