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

I have more than a few times wondered..
why can't the optimiser use the order by clause to scan only the first n
records of the index from the skip point? wouldn't it be a significant
improvement to this select type to not have to first acquire the entire set?
Why did we implement this first/skip this way rather than demand an order by
to take advantage of huge speed efficiencies or just use natural for also
ignore the entire set?
Alan