At 04:34 PM 13/04/2004 +0200, you wrote:

>Hi,
>i need some help for understand 2 performance problem.
>I use FireBird 1.5 Final, TIBOQuery and TIB_Connection with
>[fetDomainName].
>
>1) My application open about 130 query when the application start,
>almost of this query are simple select on a very little database table.
>
>I made some test on a little LAN (100Mb) and i have measured my
>application startup time:
>
>TEST DATABASE: size 370MB - NO SCHEMA CACHE
>
>- Client like AMD XP 1800: 17 Seconds
>- Client like PENTIUM CELERON 700: 32 Seconds
>
>I have used IBO Monitor to see what happen when my application start and
>discover if there are some looong query but the problem are not
>localized in a specif point. All the startup-time are used by IBO for
>prepare, send the query, load fields definition and then fetch the first
>record for my 130 query.
>Can i reduce this startup-time ?

Certainly. Don't open queries at startup time. Open them when they are
needed. No human being can look at the output of 130 queries in the first
minutes of using an application.

>2) The second problem seem to be a FireBird problem not an IBO problem
>but i explain it.
>I found the problem on a "old" and slow Server. When the database file
>size grow i see a big degradation of FireBird performance. Attention not
>when the number of records grow but when the database file size grow!
>
>I have discover this strange behaviour on a DB of 540MB on a old Server,
>this are the step of my test:
>
>- original DB size 540 MB -> my IBO application work well but with some
>performance problem on a Stored Procedure who make a lot of selects,
>inserts and updates.
>
>- i make some copies of some tables, then i drop the copies of the
>tables ... my DB file size are grow to 670MB -> now my IBO application
>work very slowly
>
>- i make a Backup and Restore of the DB -> the size of DB now are 390 MB
>-> my IBO application now work very well!
>
>Someone have see this problem ?
>I test and verify that the problem exists also on more fast Server but
>are not so evident and the performance degradation are not so
>exponential!

It's a mix of problems, not IBO, not Firebird, but a need to do
housekeeping. The result from running on a restored database proved
this. Nearly 20% of your database is garbage that hasn't been collected.
1) Uncommitted transactions 2) Rollbacks on queries that were opened but
never used 3) Doing lots of drops without sweeping 4) Maybe some indexes
getting out of balance.

Use gfix -s[weep] as often as you need to. If your db is slowing down and
growing, you need to sweep more often. You will soon find out how often
you need to do it. You can do it while the database is active but you get
more effective sweeping ( = "cleaner") if you do it when no users are
logged in.

You can also try periodically setting some of your big indexes inactive
then active, when users are off-line. That rebuilds them.

And DO something about those 130 queries. Yikes!!

Helen