Subject Re: gbak-restored database is Extremely Slow on queries in Firebird 2.0
Author Adam
--- In, Jay <sk_orpioz@...> wrote:
> I'm using Firebird 2.0 RC5 Classic on Ubuntu 6.06.
> I have a database loaded with dummy records that I
> load up from a C++ program. After the data is loaded
> (about 1/4 million records) I can perform queries with
> a performance of about <= 1 second.
> However, after using gbak to backup and then restore
> the database, those same queries run at about 40 to
> greater than 60 seconds!
> Is there anything I'm missing that _should_ be done
> after a restore?
> Can anyone please offer some suggestions? I can
> provide more info if needed. I'm pretty much a novice
> with Firebird so I'm not very familiar with database
> profiling techniques.

If you are a 'newbie', then a beta version is not the most sensible
place to start your experience. Stick to Firebird 1.5.3 (latest
stable) unless a critical piece of functionality for you is introduced
in Firebird 2. You will note that the release notes in Firebird 2
specifically ask you not to post questions in this list.

I will try and help you assuming you encounter the same issue under
Firebird 1.5. I can picture two possibilities that may cause such

1. gbak -i

If you unintentionally used this switch during restore, then your
indices will be all inactive.

2. Different plans

When you restore, the index statistics are all reset. This can cause
the optimiser to choose a different path to solve the query. Perhaps
some index is being counter productive. From iSQL, type SET PLAN; You
should post an example query with plan both before backup-restore and
after backup-restore to see whether the plan has changed. If so, there
are some tricks to preventing particular indices from being used in a
given query which may help things.