>> In the prepare step, roughly explained, the engine parses the SQL text,
>> checks the syntax and compiles it into a Firebird native executable form
>> (BLR).
>
> Yes this what i understand too, and this why 50 ms seem huge ...
>
>
>> My guess is that you are assigning the SQL text over and over again,
>> which leads to re-preparing the statement everytime the SQL statement is
>> executed.
>
> but in the test, i simply do
> isql connect;
> isql select ...
> isql commit;
>
> and here i see 50 ms :(

C'mon. This ain't bad. *g* Ever tried to do the same with the big guns
Oracle, MSSQL etc.?

>> Optimize your application in a way to keep the most important
>> or most frequently used statements prepared for later reuse. Every tried
>> to insert e.g. 100000 records in a loop with and without a prepared
>> statement?
>
> yes, when i do bulk insert, i alway do with parametized query (and believe it's terrific more faster than without in some case), but for select is not always so evident because i can not keep the prepared statement open (but i will study it to be sure)
>
> EX: user 1 come and say i want info of obj 1
> few time after user 2 come and say i want the info of obj 98
>
> i still don't know how to keep the statement prepared between these 2 query done by 2 different users... but i will investigate

I'm afraid, this is only possible (if at all), if your middle-tier is
somehow caching prepared statements/objects available for being re-used
by different requests, like a connection pool.

>> But, do we drift away from your original problem, where a single
>> execution of your statement on different tables (with/without a longish
>> VARCHAR field) with the same number of records was a magnitude slower?
>
> yes the original probleme is why in a single execution, the prepare is so much huge with long varchar in the table (not in the select). that still the problem

In one of your previous example, you showed us some timings with ~400ms,
AFAIR. Now we are down to < 50ms. I'm loosing the context now. ;-)

I'm afraid, we all are a bit lost on what your problem *really* is.

* What is your system actually doing?
* What is your load?
* What Firebird version and architecture do you use?



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