Subject Re: [Firebird-Architect] Some benchmarks about 'Order by' - temporary indexes?...
Author Arno Brinkman

> I made some benchmarks on some FDBs and I found some interestning
> results running two sets of commands.
> 1. Set 1: Select * from <table> order by <field>;
> 2. Set 2: Create index idx1 on <table>(<field>);
> Select * from <table> order by <field>;
> Note: In the 2nd case I start measuring the time _before_ issuing
> the 'Create index' command.

> FDB 2 Varchr(100) 2,594 1,203 53,62%
> FDB 3 Varchar(40) 4,234 3,630 14,27%
> FDB 3 Smallint 3,209 2,169 32,41%
> Perhaps you can improve the sort engine using the already existing
> code for indexes (more to discuss about indexing blobs etc.) or
> perhaps, do a quicksort on the column and use the recno/RDB$DB_KEY.
> Also perhaps is better to investigate the on-the-fly creating of such
> temporary indexes not only for 'Order by', but also in other
> situations which is implied the natural scan. Also, perhaps these
> structures can be 'cached'(?) until the first change of the values of
> the fields which are implied in the temporary (internal) index.
> Any comments?

I wonder a little why this is faster (certainly in a production environment) in your test, because with creating a index
all the records needs to be visit twice (1 time for creating the index keys and second when accessing the data).
Did you fetch all data in both cases?
How big is your cache (nr of buffers)?

Arno Brinkman

General database developer support:

Firebird open source database (based on IB-OE) with many SQL-99 features:

Support list for Interbase and Firebird users:

Nederlandse firebird nieuwsgroep: