I'm not actually answering any of your questions, Frank, but reading your description of how things are done made me think about transaction management. What's the statistics of this database, in particular oldest and next transaction, and do you do a fair amount of insert/update/delete?

Generally, a large gap between oldest active/interesting transaction and next transaction forces Firebird to look through old version of records and that is a typical source of slowdown. If all users exit every evening and your application typically is responsive before lunch but sluggish after, this would be the likely reason. If the application is slow all the time, well, do you actually know that everybody closes the program every night and don't just leave it running? If you do stop and restart your Firebird server and the performance is still sluggish immediately after the restart, then your transaction management isn't the cause, but if not and the statistics reveals a large gap and a restart of the server improves the performance significantly, then this is the likely cause of poor performance.

HTH,
Set

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf Of Frank
Sent: 26. oktober 2008 06:25
To: IBObjects@yahoogroups.com
Subject: [IBO] Performance Issues

Hi there,

I'm working with IBO 4.8.7 and older versions since quite a while on
a large project including many forms and dozens of IB_Queries.

As most of the performance eating tasks are done on the server by
stored procedures most of the queries are plain and simple selects
without any potentialy slowing down joins etc. - those queries are
mostly used to display informations on grids.

As the database is growing and growing I have to cope with various
performance issues.

Three of them bother me especially:

1. Starting the application
As most users tend to start the app. in the morning and let it run
all day I decided to load all forms and open all queries in one shoot
which is done like that:

for i := 0 to ComponentCount -1 do
begin
if Components[i] is TIB_Query then
begin
with Components[i] as TIB_Query do
begin
if not Active then
begin
Prepare;
Open;
end;
end;
end;
end;

Having tables with several 100MB in total it takes actualy approx.
2-3 minutes just to open and prepare them.

Is there a better (faster) way to do this ?


2. Turning Master/Detail Relations ON/OFF
I try to avoid to use multiple TIB_Queries for tables which have to
be displayed with and without master/detail data as well by turning
on/off the mastersource which I realized like that:

Turn of the relation:
iqProdWax.Active := False ;
iqProdWax.MasterSource := NIL ;
iqProdWax.MasterLinks.Text := '' ;
iqProdWax.Active := True ;

Turn on the relation:
iqProdWax.Active := False ;
iqProdWax.MasterSource := dsProdCast ;
iqProdWax.MasterLinks.Text := 'PROD_CAST_PARTS.AUTO_ID_
TREE=PROD_CAST.AUTO_ID' ;
iqProdWax.Active := True ;

There should be an easy way to temporarly disable the relation
without having to re-open the datasource as it just takes to much
time to open them (see topic 1)

Is there a better (faster) way to turn on/off the master/detail
relation ?


3. Fetching Query on Incremental search
Using the incremental search (even on indexed fields) on tables with
thousands of records causes IBO to constantly fetch records from the
server even when jumping only a few records further.
Setting SearchKeyByKey to false helps a bit but using the locate
function is much faster.

Any ideas if there is a way to offer the user a fast and convinient
search interface?