> THE DDL of the 3 sample table :
>
> CREATE TABLE DESC_VARCHAR (
> IDOBJ VARCHAR(20),
> COMMENT VARCHAR(10000)
> );
> CREATE INDEX DESC_VARCHAR_IDX ON DESC_VARCHAR(IDOBJ);
>
> ********
>
> CREATE TABLE DESC_BLOB (
> IDOBJ VARCHAR(20)
> COMMENT BLOB
> );
> CREATE INDEX DESC_BLOB_IDX ON DESC_BLOB(IDOBJ);
>
> ********
>
> CREATE TABLE DESC_EMPTY (
> IDOBJ VARCHAR(20)
> );
> CREATE INDEX DESC_EMPTY_IDX ON DESC_EMPTY(IDOBJ);
>
>
> tables are newly created and filled with the exact same reccords (40 millions rows)
>
> the same query on the 3 tables :
>
> select IDObj From desc_varchar where IDObj='NOT_EXIST';
> PLAN (DESC_VARCHAR INDEX (DESC_VARCHAR_IDX))
> => around 430 ms to return
>
> select IDObj From desc_blob where IDObj='NOT_EXIST'
> PLAN (DESC_BLOB INDEX (DESC_BLOB_IDX))
> => around 350 ms to return
>
> select IDObj From desc_xxxx where IDObj='NOT_EXIST'
> PLAN (DESC_EMPTY INDEX (DESC_EMPTY_IDX))
> => around 1.5 ms to return
>
>
> if you understand something ..... moving the page size from 8 to 16 reduce by 10 the speed of the 2 first query, but still 10x more slower than the last variante

If you are using Firebird 2.5, use the Trace API to get some IO
statistics per executed statement.


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/