Evidently I wasn't clear. My problem is not in a particular query or a particular process. In my application there are a lot of queries like the one of my example. Each one of them returns a diferent field value from a diferent table. So it's impossible to use only one parametrized query and change the parameter each time I use it.

The time of 2 seconds was not of a particular process. I mentioned it just to show the difference between the performance with dBase/BDE and with FireBird/IBX. I measured times in many processes (each of one involves 10 - 100 queries) and I compare the time between the two versions. What I wanted to show was that the version with FireBird/IBX takes the same time (or a bit more time) than the version with dBase/BDE to do every process of my application that I tested.

So, what I wanted to know is if there is something general I am doing wrong (I'm sure there is) which causes the bad performance. Perhaps is something related with the way I write my queries, or with the components, or with transactions... I don't know.

Gustavo
----- Mensaje original -----
De: Adam
Para: firebird-support@yahoogroups.com
Enviado: Lunes, 15 de Agosto de 2005 23:44
Asunto: [firebird-support] Re: Performance

> In the particular case of my example, Table1 has an index on

CODIGO. But this is only an example. In my application there are a
lot of queries like this and in almost every cases the WHERE clause
can use an index.

>
> The time of 2 seconds I mentioned in my message is not the time

of doing the query of my example. It´s the time of completing a
process which involves many queries like this.

OK, you were not very clear on this point before. The way you
explained it made it look like a simple single "select" statement of
one field was taking 2 seconds, but now if I understand you
correctly, the two seconds is how long it takes to run this query
many times.

>
> Adam: I don´t understand what you are saying with "I suspect

you are forcing Firebird to do a natural read which is the brunt of
the problem"

>

Remembering that we were all picturing a single query taking 2
seconds, the only explanation that made sense was if the field was
not indexed. I mentioned this because I also suggested another
problem in your Delphi code which forced the optimiser to check your
query each time, even though you were effectively just changing a
parameter, however this would only account for a wasted 10-100ms per
query, certainly nothing like 2 seconds.

Now we have a better understanding of the problem, can you give the
following information.

How many queries are being executed in the process that takes 2
seconds?

Does your logic look something like

qrytbl1.sql.text := 'select id from table1';
while not qrytbl1.eof do
begin
qrytbl2.sql.text := select id from table2 where table1id = ' + CHR
(39) + qrytbl1ID.Value + CHR(39)
qrytbl1.next;
end;

If so, this a huge overhead, and easily fixed using a join operation.

Adam





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




SPONSORED LINKS Technical support Computer security Computer technical support
Computer training Free computer technical support


------------------------------------------------------------------------------
YAHOO! GROUPS LINKS

a.. Visit your group "firebird-support" on the web.

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


------------------------------------------------------------------------------




[Non-text portions of this message have been removed]