At 04:25 PM 9/09/2004 -0800, you wrote:

>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.

Some foreign key indexes are terribly unselective. Try running gstat
against the FK indexes uses by the plan, to observe a) their selectivity
and b) the length of the longest duplicated chain (Max Dup).

> >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.

Before SELECT FIRST arrived, we managed fine without it. :-)

>How is the improvement can expect if use SPs ?

You can make use of good indexes to fetch the "master" set; you can pass
the key value(s) of the last set you looked at and use it to find the next
set you want; you eliminate the creation of a set merely to discard all but
"n" rows of it.

>I'm now using 1.0, is that using 1.5 would have improvement on these matter ?

Optimization was improved in 1.5. It's up to you what you try. It might
make an inefficient query a little faster, but it won't make an inefficient
query efficient.

> >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 ?

How to define a descending index? Use the DESC key word on the CREATE
INDEX statement.

"First n" often implies that you want the "top" or highest values
first. When that is the case, the normal ASC index will work against
you. For example, if you want the "most recent" 10 rows and you have a
(default) ascending index on the date, then the index will have the most
recent dates at the END of the index. If you create a descending index,
the most recent dates will be at the beginning.

./heLen