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.

First of all some words about FDBs:

FDB type Table recs: Page size: ODS:
FDB 1 local test fdb 36 MB 368754 8192 10.1
FDB 2 remote 36 MB 29000 16384 10.1
FDB 3 remote 130 MB 378492 16384 11.0

The FDB 1 contains only one table on my computer (Athlon XP 64bit
3000+, 1GB RAM, WinXP SP2, 250GB Maxtor - Firebird 2 RC4 - SS variant)
Its a table in which no deletes or edits were done.

FDB 2 and 3 are real-world bases with users on-line during the test
on a server which has 4 CPUs Opteron 848 @ 2,2GHz, 8GB RAM, Win2003
R2 64bit, RAID10 on AMCC/3ware 9550SX, GBit LAN, Firebird 2 RC4 SS.
(when will be ready the final release? we need it because the 1.5.3
has a bug in the index engine...).

The results for the two sets are in seconds:
FDB Data type: Set 1 Set 2 Gain Percent:
FDB 1 Varchar(30) 2,329 2,265 2,75%
FDB 1 Integer 1,843 1,750 5,05%
FDB 1 Time 1,813 1,641 9,49%

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?

HTH,

m. Th.