Subject Re: Performance
Author Adam
> 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

Does your logic look something like

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

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