> 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 all three cases you are < 50ms, so what's the problem? Your niggles
are in the millisecond area right now. ;-)


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

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