At 17:02 2004.09.08, Helen Borrie wrote:

>To get this thread back on topic, and away from a debate on whether
>underscores are nice or not, ------
>
>Of course, you are getting the SORT because of the ORDER BY. It doesn't
>make sense to use SELECT FIRST n without an ORDER BY clause.
>
>The optimizer is choosing NATURAL order for the join method, not the sort
>method. Don't make the mistake of thinking that "NATURAL" means "bad" for
>the left stream of a join. It means that the optimizer has calculated that
>it will be faster to form a binary match structure *once* from the left
>stream and to re-use that, than to repeatedly walk through an index...or,
>simply, that a suitable index is not available.
>
>EVERY call to SELECT FIRST n has to form the entire set first, order the
>entire set, output the 'n' rows and discard the rest. So, to speed this
>up. make sure that
>1) your WHERE clause limits the output sufficiently so that the sort set is
>of a reasonable size

I limit the result set to 100 and still very slow.

>2) there are good indexes on the right side (relative to the left side of
>the join in each respective join) to use for the WHERE search..I'd want to
>look at foreign key indexes to make sure they are decently selective and,
>if not, I'd add an OR to avoid using that index

Yes, the fields on the right side are all foreign key constraint against
left side.

>3) think about whether SELECT FIRST n is really the way you want to get
>this set. Before SELECT FIRST existed, we used to use parameterised SPs to
>fetch these sets and, IMO, it's still faster with large tables

Since the application is browser based. SELECT FIRST n is necessary. How is
the improvement can expect if use SPs ? I'm now using 1.0, is that using
1.5 would have improvement on these matter ?

>4) in any case, look at how some descending indexes on the ORDER BY columns
>might assist the arrival of the first n rows from the sort

How to ?

>./heLen

Thank you for your help.

Best regards,

Almond Wong

>
>
>Yahoo! Groups Links
>
>
>
>