JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
see a small difference, since optimizer has "early out" strategies, but if you do see a big
difference, you have found a weakness in the optimizer and MS would like to know about it
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more columns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materialized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index is a non-clustered
index containing all the information that a query need from a table. This way, SQL Server don't have
to access each page for each row, all information is already in the non-clustered index. This can
make a huge performance difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.|||> Ha.
Get your point... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>It shouldn't make a difference, as long as the semantics of the query stay the same (consider
>>outer
>>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>>difference, you have found a weakness in the optimizer and MS would like to know about it
> Ha.
> J.
>