--- In firebird-support@yahoogroups.com, Hannes Streicher

> This is a matter of execution order of the statements
> your query will just get the first 20 record in natural order no
> sorting necessary
>
> an ORDER BY will precede the FIRST
> so all 150000 records will be processed ,
> then sorted
> then the first 20 sent
>
> to speed it up with an order by you shud consider a where clause to
> reduce the subresult which needs to be sorted

Thanks for this info. I have done some further testing, and what I'm seeing is that if I filter down the data range to be sorted to a smaller set (as you correctly pointed out), it does tend to improve performance. But its not a linear improvement. Here's the metrics I have so far on this:

1. List all 150,000 rows, no sort order, filter first 20 rows with FIRST/SKIP limit - 20 ms
2. List all 150,000 rows, with sort order, filter first 20 rows with FIRST/SKIP limit - about 11 seconds
3. Filter down to 5,000 rows, with sort order, filter first 20 rows with FIRST/SKIP limit - about 4 seconds

Its certainly better, and I realize there must be some overhead to initialize the optimizer for this, but I need to get this down to 1-2 seconds max.

My testing has been with a PHP application connecting to a local FB database on the same machine. I've seen pretty good improvement if the FB database was on another machine in the local server network. Do you think this would get me down to 1-2 seconds?

Myles