Re: SQL Question !?

From: Randy DeWoolfson <randyd_at_cais.com.nospam>
Date: 1997/06/05
Message-ID: <33976232.2791@cais.com.nospam>#1/1

Joost Ouwerkerk wrote:
>
> I can't seem to find any documentation about this, so I'm hoping
> someone will be able to help me:
>
> What is the logic for determining what is the ideal order for listing
> table names after the FROM clause in a SELECT statement? I didn't
> think there was one, until I discovered significant performance
> enhancements simply by placing one table name before another. It's
> pretty much trial and error, though. What's the deal?
>
> eg. SELECT x, y FROM a, b WHERE x=y; might be significantly faster
> than:
> SELECT x,y FROM b, a WHERE x=y;
>
> Anyone?
>
> Joost Ouwerkerk
> HSC Foundation,
> Toronto, Ontario, Canada.

Your select statement is parsed in reverse order by the engine.

In general, you will get the most improvement by first addressing the WHERE clause. Put the MOST RESTRICTIVE elements of the where clause LAST. then progressively less restrictive elements towards the beginning. It is also helpful to have an index on the most restictive element.. and not so important on the others.

Then after you do this.. You can play with the FROM clause. The from cluase should be singnificantly less important to the performance.. best way to do this is check the EXPLAIN PLAN results.

hope it gives some insight.
Randy :)

randyd_at_cais.com brandall_at_erols.com info_at_euclidsys.com

                     remove .nospam to reply

Pursuant to US Code Title 47, Section 227, unsolicited commercial E-mail sent to these addresses is subject to a $500 archival fee per copy. E-mail recieved after any reciept of this notice implies acceptance of these terms. A copy of USC Title 47, Section 227 may be found online at http://www.law.cornell.edu/uscode/47/227.html

Received on Thu Jun 05 1997 - 00:00:00 CDT