hash join / merge join option

can anyone tell me in what situation you should force a hash join or merge
join option in a query?generally speaking, it's usually a best practice to allow sql server's
optimizer to use whichever type of join it deems to be most efficient
instead of forcing a join.|||I know, but sometimes when I tried to force a merge join, it's actually
faster, although I don't know why.
so can you explain why?
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1109792227.328553.193860@.o13g2000cwo.googlegroups.com...
> generally speaking, it's usually a best practice to allow sql server's
> optimizer to use whichever type of join it deems to be most efficient
> instead of forcing a join.
>|||hash joins usually occur when the optimizer cannot find the appropriate
indexes to use to speed up the query.
therefore, when you force an merge join on the query using a particular
column, it will speed up your query. instead of forcing a join hint,
study your query and determine if you can apply any appropriate
indexes. that way, the optimizer will always use the best possible type
of join.
in general, you want your joins to be loop joins, followed by merge
joins and lastly, hash joins.|||I thought loop join is not good but inner join is faster than loop join in
most of the cases.
I think loop join is better than inner join when you call a query which join
tables from a linkedserver
or when a small table joins a big table.
I agree with you that it's bad pratice to force a merge join or hash join.
but as far as merge join or hash join, in case they can't find right
indexes(it happens pretty often actually), I guess they catergorize the
data into small chunks, so it's easier to match the rows between tables.
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
> hash joins usually occur when the optimizer cannot find the appropriate
> indexes to use to speed up the query.
> therefore, when you force an merge join on the query using a particular
> column, it will speed up your query. instead of forcing a join hint,
> study your query and determine if you can apply any appropriate
> indexes. that way, the optimizer will always use the best possible type
> of join.
> in general, you want your joins to be loop joins, followed by merge
> joins and lastly, hash joins.
>|||Loop join and inner join are not mutually exclusive. "Inner" controls the
semantics of how you want rows in the two tables to be related; this is
determined by how you actually write your query. If you don't specify the
semantic type of join, the default is INNER JOIN.
"loop|hash|merge" are the algorithms SQL Server can use internally to
process the join. There is no default.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eBzZ4M2HFHA.3484@.TK2MSFTNGP12.phx.gbl...
>I thought loop join is not good but inner join is faster than loop join in
> most of the cases.
> I think loop join is better than inner join when you call a query which
> join
> tables from a linkedserver
> or when a small table joins a big table.
> I agree with you that it's bad pratice to force a merge join or hash join.
> but as far as merge join or hash join, in case they can't find right
> indexes(it happens pretty often actually), I guess they catergorize the
> data into small chunks, so it's easier to match the rows between tables.
>
> "szeying.tan" <szeying.tan@.gmail.com> wrote in message
> news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
>|||Britney
I replied to this in the .server group. Please do not post the same question
independently in multiple groups, so that people do not waste time answering
questions that have already been answered.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uHk$dz1HFHA.3624@.tk2msftngp13.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>|||i think you're confusing the type of join (inner, outer, left, right
etc) syntax and the algorithm that sql server uses under the hood to
process a particular join.
sql server optimizer is very smart (99% of the time ;))
if it can't find the right indexes, more often than not. you're not
designing your indexes correctly. if you want some tips on what indexes
should generally be created per your query's design, please let me
know.|||britney,
generally speaking, merge joins are used if both inputs are sorted on the
join predicate (ie, if there are tables 'orders' and 'order_details', joined
on 'orderid' column, and both tables are indexed on that column). if there
are no such indexes, sql server will choose loop join, or decide to sort
(one or both) inputs on fly and use merge join, whichever method it thinks
would perform better for a given situation. iow, it is not a good idea to
force use of either merge or loop join - unless you 'force' merge join by
adding the appropriate indexes.
hth
dean
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eBzZ4M2HFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I thought loop join is not good but inner join is faster than loop join in
> most of the cases.
> I think loop join is better than inner join when you call a query which
join
> tables from a linkedserver
> or when a small table joins a big table.
> I agree with you that it's bad pratice to force a merge join or hash join.
> but as far as merge join or hash join, in case they can't find right
> indexes(it happens pretty often actually), I guess they catergorize the
> data into small chunks, so it's easier to match the rows between tables.
>
> "szeying.tan" <szeying.tan@.gmail.com> wrote in message
> news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
>