> 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/
