Subject Re: [firebird-support] Re: how much faster does a "real server" do?
Author Helen Borrie
At 01:56 AM 29/05/2006, you wrote:
> > You're right, it's nothing like what you need. If you must do this
> > partitioning, at least avoid SELECT FIRST n SKIP .etc and use a
>cursor > instead:
>Ah, ok...I didn't know that would be faster than using "first"

Here's what the SELECT FIRST n SKIP m syntax does under the hood:

1. Gets the entire set specified by the WHERE clause and creates a
temporary set (in memory on disk, depending on size and resources)

2. Physically orders the temporary set according to the ORDER BY clause.

3. Starts outputting rows from the temporary set, skipping the first
m rows if SKIP is specified, until n rows have been sent

4. Ignores the rest of the temporary set

Other things to note about this:

It makes no sense to use FIRST n or SKIP m on a non-ordered
set. Unlike your legacy database table, the Firebird table has no
physical row order. Within your transaction's view, it has a default
order which is determined by the position of its page on the
disk. In a very "young" database that has never been restored, you
could guess that the sequence is (more or less) creation sequence,
but there is no way you can predict or assume that. By the rules,
tables in relational databases are for storage only: all retrieval
is done by query and, unless there is an ORDER BY, the order is
assumed random.

In cursor operations, the engine refers to a row by its db_key, which
is a unique representation stored with every row and record version,
of its physical "coordinates" on disk. There are techniques by which
you can get a performance advantage from its uniqueness and refer to
it by reference as though it were a key. But it is not like RecNo in
Paradox and never should be regarded as equivalent.