Subject Re: [IBO] Query opening timings
Author Geoff Worboys
> I appreciate I can do stuff myself to help, such as
> opening on-demand rather than at application start-up;
> pruning unnecessary columns; no FetchAll; use
> SchemaCacheDir (any others?), but it would be
> helpful for a newbie to know if 2 to 6 seconds are
> in any way typical, or is it likely there is something
> fundamental I've missed here?

Most of any delay related to IBO comes from the time it takes to
prepare a query. Usually a single query should prepare in sub-second
time. The rest of the time is presumably the time it is taking to
retrieve the data from the server and this can vary HUGELY depending
on many factors.

You have already mention some obvious stuff - especially setting up so
that complex forms with many queries only open on demand. What I
usually do is setup so that a form with a pagecontrol only opens
queries necessary for the display page, openning others as other pages
are selected.

If you are using a lot of lookups this can greatly slow down the
opening of the form, since the standard TIB_LookupCombo has to have
its lookup dataset opened. You can work around this by using the
TIB_LookupEnh control available from the EnhComponents available from
the IBO registered user download site.

Then you need to tackle your individual queries...

* In designmode prepare the queries in the IBO query editor. The plan
is displayed in the area below the SQL. This will let you see if
indexes are being used where they should. If you see data being
retrieved in NATURAL order then this may be a problem.

* At runtime you can use the IB_Monitor (SQL monitor) to see the
query, plan and timings of the sql interaction with the server. This
may help you work out where your queries can be improved.

* Also at runtime the IB_Profiler can be used to see how hard the
server had to work to execute your query/procedure or whatever. The
results can be helpful to indicate where you can improve things.


Geoff Worboys
Telesis Computing