I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...
Thanks
ChamnapChamnap wrote:
Quote:
Originally Posted by
I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...
If by "standard join" you mean just JOIN, then they're
identical in effect.|||I mean standard join by:
Select * From Quiz, Question Where Quiz.id = Question.id|||Select * From Quiz, Question Where Quiz.id = Question.id
IMHO today inner join is a standard
BTW left join is faster than inner join...
Regards, Wojtas
www.e-krug.com|||Chamnap wrote:
Quote:
Originally Posted by
I mean standard join by:
Select * From Quiz, Question Where Quiz.id = Question.id
That's arguably not a join at all, though any reasonable database
server will optimize it into one under the covers. These days,
recommended practice is to do something like
select (list of columns)
from quiz x
join question y on x.id = y.id
For one thing, when you have a less trivial case than this example,
it makes it harder to leave out a join condition by mistake.|||I have one question about the standard join and inner join, which one
Quote:
Originally Posted by
is faster and more reliable? Can you recommend me to use? Please,
explain me...
The ANSI-92 style INNER JOIN syntax is is recommended in Microsoft SQL
Server. The older join syntax is still accepted and both should provide the
same level of performance and reliability.
In the case of OUTER JOINs, older style joins (*= and =*) are sometimes
ambiguous (unreliable) so the ANSI-92 style OUTER JOIN syntax is strongly
recommended. The older style outer joins are only allowed in databases
with compatibility level lower than 90 and may not be supported in future
SQL Server versions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamnap" <chamnapchhorn@.gmail.comwrote in message
news:1183007085.051745.221660@.i38g2000prf.googlegr oups.com...
Quote:
Originally Posted by
Hello, everyone
>
I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...
>
Thanks
Chamnap
>
<wkrugiolka@.poczta.onet.plwrote:
Quote:
Originally Posted by
>BTW left join is faster than inner join...
Really? What makes you think that? It is certainly not what I have
experienced, or heard about.
Roy Harvey
Beacon Falls, CT|||"news.onet.pl" wrote:
Quote:
Originally Posted by
>
Quote:
Originally Posted by
Select * From Quiz, Question Where Quiz.id = Question.id
>
IMHO today inner join is a standard
>
BTW left join is faster than inner join...
No it isn't.
For starters, an outer join (such as left join) will only return the
same result as an inner join if no rows from the outer table would be
eliminated when running the inner join.
Next, using inner join gives the optimizer more options how to process
the query which on average should lead to better performance then using
the outer join equivalent.
As usual, course there is a disclaimer here too. There could be
situations where the optimizer might select a suboptimal plan, or when
the optimizer shortcuts its optimization process because of the many
possible access paths. In those situations a rule based approach, or an
outer join approach might accidentally run faster.
Gert-Jan|||On Jun 28, 12:38 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl>
wrote:
Quote:
Originally Posted by
"news.onet.pl" wrote:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Select * From Quiz, Question Where Quiz.id = Question.id
>
Quote:
Originally Posted by
IMHO today inner join is a standard
>
Quote:
Originally Posted by
BTW left join is faster than inner join...
>
No it isn't.
>
For starters, an outer join (such as left join) will only return the
same result as an inner join if no rows from the outer table would be
eliminated when running the inner join.
>
Next, using inner join gives the optimizer more options how to process
the query which on average should lead to better performance then using
the outer join equivalent.
>
As usual, course there is a disclaimer here too. There could be
situations where the optimizer might select a suboptimal plan, or when
the optimizer shortcuts its optimization process because of the many
possible access paths. In those situations a rule based approach, or an
outer join approach might accidentally run faster.
>
Gert-Jan
Cardinality, distribution, indexing strategy and the optimizer
strategy are the determinants. Every posting reply is correct given
the appropriate data sets. The bottom line: prototype and test in your
environment with your data sets and system loads if you want a
definitive answer.
-- Bill|||On Jun 28, 2:33 am, "news.onet.pl" <wkrugio...@.poczta.onet.plwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Select * From Quiz, Question Where Quiz.id = Question.id
>
IMHO today inner join is a standard
>
BTW left join is faster than inner join...
>
Regards, Wojtaswww.e-krug.com
what about right join?|||For starters, an outer join (such as left join) will only return the
Quote:
Originally Posted by
same result as an inner join if no rows from the outer table would be
eliminated when running the inner join.
Yes, but when left and inner join returns same results - left is faster
(probably because it doesn't check the dependencies - just join results)
We use left join instead inner in situations we know that inner join will
not cut results...
Regards, Wojtas|||Yes, but when left and inner join returns same results - left is faster
Can you post an example (DDL and sample data) that illustrates this
behavior, including with the execution plans?
I can see how this might happen in cases where the inner join uses an
execution plan that turns out to be sub-optimal. This could occur because
statistics are stale or the disk subsystem is unusually biased in favor of
scans (often used on outer joins). However, I think using LEFT JOINs
instead of INNER JOINs is not the proper solution.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"news.onet.pl" <wkrugiolka@.poczta.onet.plwrote in message
news:f6aa7t$e7p$1@.news.onet.pl...
Quote:
Originally Posted by
Quote:
Originally Posted by
>For starters, an outer join (such as left join) will only return the
>same result as an inner join if no rows from the outer table would be
>eliminated when running the inner join.
>
Yes, but when left and inner join returns same results - left is faster
(probably because it doesn't check the dependencies - just join results)
We use left join instead inner in situations we know that inner join will
not cut results...
>
>
Regards, Wojtas
>
>|||"news.onet.pl" wrote:
Quote:
Originally Posted by
>
Quote:
Originally Posted by
For starters, an outer join (such as left join) will only return the
same result as an inner join if no rows from the outer table would be
eliminated when running the inner join.
>
Yes, but when left and inner join returns same results - left is faster
(probably because it doesn't check the dependencies - just join results)
We use left join instead inner in situations we know that inner join will
not cut results...
I understand your case, but still I disagree. I still claim that it is
not faster, not as a rule. As a rule, it is equally fast or slower.
However, I am aware that this is mostly an academic discussion. In most
situations I would expect the same performance.
If you specify Left Join instead of Inner Join, you are basically doing
two things:
1. you are reducing the number of potential access paths during
compilation
2. you are 'forcing' the access path between the two tables: from the
outer table to the inner table
The result of [1] is positive, because a full compile would require less
time and resources. Of course this is only relevant when the optimizer
actually performs a full compile.
The result of [2] is negative, because it disqualifies query plans that
might be more efficient than the 'forced' left to right access path.
Obviously, this is only relevant if there actually is a more efficient
query plan.
The smarter the optimizer gets, the smaller the performance advantage of
Inner Join will be ([2]), and the smaller the potentially added
compilation cost will be ([1]).
The bottom line is, that you could see suboptimal performance in such a
Left Join scenario when you know that no rows from the outer table will
be eliminated but the optimizer does not.
I think this Left Join trick is a very good query hint if the query
underperforms because of a bad query plan. But using query hints without
a reason (simply out of routine) still sounds inappropriate to me.
Of course, if you have an example where a Left Join performs better than
the Inner Join equivalent, then I would be most interested to see it! If
you could post or describe such an example, that would be great.
Thanks,
Gert-Jan
No comments:
Post a Comment