Adam and the others:

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.

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"

What are you meaning with this?

Gustavo

----- Original Message -----
From: Adam
To: firebird-support@yahoogroups.com
Sent: Monday, August 15, 2005 8:16 PM
Subject: [firebird-support] Re: Performance


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




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

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]