Subject Re: [firebird-support] Performance
Author Aage Johansen
Gustavo 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;
> Open;
> Descrip:=FieldByName('DESCRIP').Value;
> Close;
> end;
> This is only an example and is simplified to see the way I did the
> I suspect that there might be other things that I have to change to
increase the performance.
> My questions are:
> Why the performance of my application with FireBird is worse than
with dBase?
> What can I do to increase the performance of my application with
> Thanks in advance.

You'll have a loss of performance using the same query component for
different purposes. I used to do this, but in a project I changed three
general-purpose query components into about 50 components where each of
them use one (and only one) SQL statement. These queries are prepared at
first use, and they stay prepared. They are used for data validation, and
now use a ReadOnly/ReadCommitted transaction.
YMMV, but you might find that not having to _always_ prepare the statements
will give better performance.

[and, please to no hijack threads - start a new one when you introduce a
new subject]

Aage J.