Performance question

Hello there
I have two tables i match with them. the field i match is none clustered
index.
When i make INNER JOIN between them it takes less then left or right join.
And inspite both of the fields have index the execution plan use hash join.
Whay it is like that?The reason for this is the way that the query processer works internally.
The Inner join will select rows which are in both of the two tables
mentioned in the join.
The left join will select all the rows from the left table, regardless of if
they exist in the right hand table.
If you look at the execution plans it's likely that you'll see an index s
for the inner join and an index scan for the left join.
Regards
Colin Dawson
www.cjdawson.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uHNGAOrZGHA.1348@.TK2MSFTNGP05.phx.gbl...
> Hello there
> I have two tables i match with them. the field i match is none clustered
> index.
> When i make INNER JOIN between them it takes less then left or right join.
> And inspite both of the fields have index the execution plan use hash
> join.
> Whay it is like that?
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> I have two tables i match with them. the field i match is none clustered
> index.
> When i make INNER JOIN between them it takes less then left or right
> join. And inspite both of the fields have index the execution plan use
> hash join.
> Whay it is like that?
Since neither tables, nor indexes, nor indication of table sizes were
included, I can only respond in general terms.
SQL Server has three differnt strategies to join tables: loop join, merge
join and hash join.
With a loop join, SQL Server finds rows in one table, and for each row
looks up a row in the other table.
With a merge join, SQL Server scans both tables in parallell. Merge join
presumes that the inputs are sorted on the same value.
With a hash join, SQL Server scans both tables and builds a hash table
that is then used for lookup.
Loop join is good when the other table is large, and only a handful row
will be hit. But if many rows are to be selected, the loop join is
expensive, and it's cheaper to scan once.
SQL Server does not know beforehand which will be the most expensive,
but the optimizer computes estimates from the statistics sampled about the
data.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx