OK, thank i see now exactly where is the probleme is

it's in the PREPARE step

select IDObj From desc_varchar where IDObj='NOT_EXIST';
PREPARE_STATEMENT: 40 ms
EXECUTE_STATEMENT_FINISH: 0 records fetched
0 ms, 4 read(s), 4 fetch(es)


select IDObj From desc_blob where IDObj='NOT_EXIST';
PREPARE_STATEMENT: 28 ms
EXECUTE_STATEMENT_FINISH: 0 records fetched
0 ms, 4 read(s), 4 fetch(es)


select IDObj From desc_empty where IDObj='NOT_EXIST';
PREPARE_STATEMENT: 2 ms
EXECUTE_STATEMENT_FINISH: 0 records fetched
0 ms, 3 read(s), 4 fetch(es)


now why the prepare is soo long ? is it a misc conception in the engine ? what exactly is doing the prepare ?

--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:
>
> > i just setup the default fbtrace.conf with all item to true, but i don't have any usefull informations ...
> >
> > just this when i commit :
> >
> > 0 ms, 1 read(s), 1 write(s), 1 fetch(es), 1 mark(s)
> >
> > ex :
> >
> > -------------------------------------------------------------------------------
> > select
> > IDObj
> >>From
> > desc_blob
> > where
> > IDObj='NOT_EXIST'
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > PLAN (DESC_BLOB INDEX (DESC_BLOB_IDX))
> > 34 ms
> >
> > select
> > IDObj
> >>From
> > desc_empty
> > where
> > IDObj='NOT_EXIST'
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > PLAN (DESC_EMPTY INDEX (DESC_EMPTY_IDX))
> > 2 ms
>
> Use the following trace config:
>
> <database %yourdatabase%>
> enabled true
> log_statement_finish true
> print_plan true
> print_perf true
> exclude_filter %RDB$%
> time_threshold 0
> max_sql_length 2048
> </database>
>
>
> and replace yourdatabase with a regular expression fitting your
> connect/database string.
>
> This should give you detailed information for each
> EXECUTE_STATEMENT_FINISH event.
>
>
> --
> With regards,
> Thomas Steinmaurer (^TS^)
> Firebird Technology Evangelist
>
> http://www.upscene.com/
> http://www.firebirdsql.org/en/firebird-foundation/
>
>
> > --- In firebird-support@yahoogroups.com, Thomas Steinmaurer<ts@> wrote:
> >>
> >>> 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/
> >>
> >
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > Yahoo! Groups Links
> >
> >
> >
>