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