Hi,

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

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info