Re: SQL performance tuning..

[Arjan Hoornstra <ahoor@YAHOO.COM>]

There are a few things to say about query writing,
although I'm not sure whether they all apply for all
rdbms's.
- the order of tables you specify is not important
because the optimiser takes care of this
- the order of where clauses are not imporatant
because the optimiser takes care of this
- do not use a function (isnull, max)on an indexed
column, the index will not be used.
- try not to use 'in' or 'not in', try rewriting the
query using 'exists'.
- try using <= and => rather than < and >

Maybe this helps? Try giving us your query and rdbms,
and maybe something will come up...


--- Rick_Creel@AONCONS.COM wrote:
> The SQR I am working on is in a PeopleSoft
> environment, specifically PeopleSoft
> Projects.
> PSoft Ver 7.53
> Oracle Ver 8.06
> SQR Ver 4.34
>
> I am joining the PROJ_RESOURCE table (which has over
> 3 million of rows of data)
>   to the PROJ_ACTIVITY table
> (which has just over 127,000 rows of data).
>
> For purposes of efficiency, is there a "rule of
> thumb" as to what sequence to
> specify the table names in the "FROM" clause?
> Do you say
> FROM PS_PROJ_RESOURCE PR,
>       PS_PROJ_ACTIVITY PA
>
> or
> FROM PS_PROJ_ACTIVITY PA,
>         PS_PROJ_RESOURCE PR
>
> or does it matter?
>
> In the WHERE clause, what about the sequence of the
> keys for each table,
> especially the keys that are
> used to join the 2 tables?
>
> The SQR I have works, but it is slow as molasses in
> the winter!  I need to
> fine-tune the queries to make them
> as fast and efficient as possible.  I hate to admit
> it, but I have no clue how
> to optimize SQL code and have never
> been given any training in how to optimize code.
>
> Thanks for any help/advice!


__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail – Free email you can access from anywhere!
http://mail.yahoo.com/