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 t
he same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics sta
y the same. You might
see a small difference, since optimizer has "early out" strategies, but if y
ou 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 co
lumns 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 materia
lized 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 wa
y, SQL Server don't have
to access each page for each row, all information is already in the non-clus
tered 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 st
ay 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 ab
out 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:
>
> Ha.
> J.
>