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...
>

Hard Coded parameters

Is it possible to force parameters into the reports so enabling me to force a user id value into every report that is picked up from the list. The user ID is a system value and I don't want end users having any knowledge of it?

Cheers

Darren

Hello Darren,

What you can do is create a hidden/internal parameter so that the end user can not see it. From BOL:

Hidden

Select this option if the parameter value should not appear on the report. Although hidden parameters do not appear on a report, they can be set in other ways (for example, in subscriptions and through URLs).

Internal

Select this option if the parameter cannot be changed at run time. On a published report, no visual evidence is provided to indicate that this parameter exists.

Hope this helps.

Jarret

|||

Hi Jarret,

I think this may be the answer, I need to provide a list of published reports , how would we then get the hidden parameter into it.

Can we display a URL with the user ID already in it which will then render the report viewer with the other optional parameters to be picked. How do we stop somebody constructing a URL with a different user ID.

A hidden parameter sounds like the right option but I'm confused how to populate it.

Cheers, Darren

|||

If you're wanting to keep it out of the URL as well, you'll need to go with an Internal parameter, since it can not be changed at run time. Hidden parameters can be changed through the URL. You can change the value of this from within your Report Manager. Go to the properties of your report, then go into the Parameters section. In there, you can change the default value to whatever you like.

Hope this helps.

Jarret

|||Is the Internal parameter fixed in the report then? I want to be able to change the value based on the user running the report but do not want them to be able to change to a user ID not associated to them.|||

I'm not sure how you were planning to change the user ID of the user running the report, but you can set an expression for the default value of any parameter through VS.

If you put =User!UserID as the Default value for your parameter, it will show the domain account used.

Just remember, if you use a hidden parameter, someone can manipulate the value via the URL. This is not possible with an internal parameter.

Jarret

|||

Thanks Jarret,

I think I know what we will do know. The web site maintains a guid session token for each user which originates from the database initially so if I use this as my hidden parameter and let the reports stored procedure calculate the user ID based on this I will be safe.

Cheers

|||

That sounds like it would work, but someone can pass in that parameter via the URL since you're using a hidden parameter. The internal parameter should work in this situation too, and you don't have to worry about it being changed by a user.

Anyway, I'm glad you have a solution. Can you mark this one as answered so others can see the solution?

Jarret