--- In firebird-support@yahoogroups.com, "Gustavo" <gusm@d...> wrote:

> Hello:
>
> I have an application which is developped in Delphi 5.

Originally it used dBase files and BDE. There were a lot of TTables
in my code. Now I developped a new version which uses FireBird and
IBX. In a first step I only changed the TTables to TIBTables and in a
second step I took the time (a lot!) to change almost every TIBTable
to a TIBQuery. My new version with FireBird works fine.

>
> The problem is that it doesn't have the performance I expected.

I thought that the FireBird version would be much faster than the
dBase one, but it's not the case. After the first step (using
TIBTables) the FireBird version was significantly slower than the
dBase version. I measured times of several processes and what took
1000 msec with dBase, took 2000 msec with FireBird.

>
> After changing TIBTables to TIBQuerys, the performance

increased and now the version wich uses FireBird it's only a bit
slower than the original dBase version. What I expected was that the
FireBird version using TIBQuerys should be much faster than the dBase
one.

>
> I write here an example of the way I changed my code where I

originally used the TTable component:

>
> In the dBase version there were the following:
>
> Table1:=TTable.create(nil);
> with Table1 do
> begin
> TableType:=ttdBase;
> TableName:='C:\MyApplication\Tables\Table1.dbf';
> IndexFieldNames:='CODIGO';
> Open;
> Locate('CODIGO','001';[]);
> Descrip:=FieldByName('DESCRIP').Value;
> Close;
> Free;
> end;
>
> Now, in the new FireBird version there is the following:
>
> with QueryAux do {I always use the same QueryAux so I

create it once and free it when the application finalizes}

> begin
> DataBase:=DataBase1;
> Transaction:=DataBase1.DefaultTransaction;
> SQL.Text:='SELECT DESCRIP FROM TABLE1 WHERE CODIGO = '+Chr

(39)+'001'+Chr(39);

> Open;
> Descrip:=FieldByName('DESCRIP').Value;
> Close;
> end;

Gustavo,

I am not sure how intelligent IBX is but I imagine it might be better
if you tried to use parameters. If you are re-using essentially the
same query but with different
"codigo" values, it might help things along.

Change SQL.Text line to

SQL.Text:='SELECT DESCRIP FROM TABLE1 WHERE CODIGO = :CODIGO';

Then when you want to use it, you just set the parameter to whatever
is relevant to that query.

ParamByName('CODIGO').AsString := '001';

As Alan suggested, make sure you have the Codigo field indexed either
directly or implicitly (if it is a PK or FK). I have never seen a
query that retrieves a single field from a single table with no joins
and can use an index to meet its simple where criteria (ie a query
like the one you posted) take more than 50 ms unprepared, and it is
even faster with caching. I suspect you are forcing Firebird to do a
natural read which is the brunt of the problem, and also, it must
prepare the query every time you change a value which doesn't help.
But there is no way that query would take 2 seconds given the
oportunity to use an index on Codigo.

Adam