Prev: Subquery or Left Join
Next: Bug or feature?
From: tshad on 29 Mar 2010 21:55
I just saw 2 conflicting statements in 2 different posts and am confused on
whether Hash Matches are good or bad when optimizing.

I saw this earlier today:

A clustered index scan means the query is looking at every entry in the
index. You should try using a WHERE clause to turn that into an index seek
instead. As for the hash match, that's the best inner join type you can get,
so if it's slow all you can do is try to reduce the number of records being
joined.

I just saw this one:

As an aside, though, both execution plans use a Hash Match Inner Join.
Generally speaking this is NOT the most efficient join type for SQL Server;
Loop Join is much more efficient. When you see a Hash Join in a query
execution plan, consider your indexes. A Hash Join is frequently an
indicator of inefficient indexing. I'll delve more deeply into this in
another post.

So which is it?

Thanks,

Tom


From: Kalen Delaney on 29 Mar 2010 22:04
Hi Tom

There is no one best answer. As usual, it depends.... a hash join between
two tables with no indexes is almost always better than a loop join with no
indexes.

However, usually hash join is considered to be a fall-back position when
none of the other types is possible. In many(maybe most) cases, a loop join
with good indexes is a better choice. I usually recommend that if you see a
hash join in your plan, and you query is not performing well, that you build
at least one index on one of the join clauses to try to get a loop join.
Note that there are cases that a hash join will be better even if you have
good indexes. This can happen when joining two very large tables, with lots
of matching rows.

I'd be very curious to know who would state unequivocally that a hash join
was the best.

--
HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

"tshad" <t(a)dslextreme.com> wrote in message
news:O9hHhw6zKHA.2436(a)TK2MSFTNGP04.phx.gbl...
> I just saw 2 conflicting statements in 2 different posts and am confused
> on whether Hash Matches are good or bad when optimizing.
>
> I saw this earlier today:
>
> A clustered index scan means the query is looking at every entry in the
> index. You should try using a WHERE clause to turn that into an index seek
> instead. As for the hash match, that's the best inner join type you can
> get, so if it's slow all you can do is try to reduce the number of records
> being joined.
>
> I just saw this one:
>
> As an aside, though, both execution plans use a Hash Match Inner Join.
> Generally speaking this is NOT the most efficient join type for SQL
> Server; Loop Join is much more efficient. When you see a Hash Join in a
> query execution plan, consider your indexes. A Hash Join is frequently an
> indicator of inefficient indexing. I'll delve more deeply into this in
> another post.
>
> So which is it?
>
> Thanks,
>
> Tom
>
From: tshad on 29 Mar 2010 22:17

"Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message
news:u52Ui16zKHA.3884(a)TK2MSFTNGP06.phx.gbl...
> Hi Tom
>
> There is no one best answer. As usual, it depends.... a hash join between
> two tables with no indexes is almost always better than a loop join with
> no indexes.
>
> However, usually hash join is considered to be a fall-back position when
> none of the other types is possible. In many(maybe most) cases, a loop
> join with good indexes is a better choice. I usually recommend that if you
> see a hash join in your plan, and you query is not performing well, that
> you build at least one index on one of the join clauses to try to get a
> loop join. Note that there are cases that a hash join will be better even
> if you have good indexes. This can happen when joining two very large
> tables, with lots of matching rows.
>
> I'd be very curious to know who would state unequivocally that a hash join
> was the best.
>
It was just someone commenting on someones question about joins. It wasn't
anyone that was writing an article or anything. But I just wanted to check.

Thanks,

Tom
> --
> HTH
> Kalen
> ----------------------------------------
> Kalen Delaney
> SQL Server MVP
> www.SQLServerInternals.com
>
> "tshad" <t(a)dslextreme.com> wrote in message
> news:O9hHhw6zKHA.2436(a)TK2MSFTNGP04.phx.gbl...
>> I just saw 2 conflicting statements in 2 different posts and am confused
>> on whether Hash Matches are good or bad when optimizing.
>>
>> I saw this earlier today:
>>
>> A clustered index scan means the query is looking at every entry in the
>> index. You should try using a WHERE clause to turn that into an index
>> seek instead. As for the hash match, that's the best inner join type you
>> can get, so if it's slow all you can do is try to reduce the number of
>> records being joined.
>>
>> I just saw this one:
>>
>> As an aside, though, both execution plans use a Hash Match Inner Join.
>> Generally speaking this is NOT the most efficient join type for SQL
>> Server; Loop Join is much more efficient. When you see a Hash Join in a
>> query execution plan, consider your indexes. A Hash Join is frequently an
>> indicator of inefficient indexing. I'll delve more deeply into this in
>> another post.
>>
>> So which is it?
>>
>> Thanks,
>>
>> Tom
>>


 | 
Pages: 1
Prev: Subquery or Left Join
Next: Bug or feature?