--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- In firebird-support@yahoogroups.com, 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
> behaviour.
>
> 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.
>
> Adam
>

If you can tell me a better place to post this problem, I'd be very
grateful.
Unfortunately, I can't use Firebird 1.5.x series because there is none
available for my 64 bit system as far as I know. I believe the 2.0 RC
series is all I can use.

Here's some output from my application as requested though:
SQL and Plan from the natural database:
default:0 pool.5 wms::createSearchSQL -
SELECT
MA.member_id, MA.username, MA.gender, MA.birthday, (CURRENT_TIMESTAMP
- MA.last_online),
MA.seeking_gender, MA.seeking_lower_age, MA.seeking_upper_age,
MA.seeking_radius,
MA.num_photos, T.catch_phrase, T.about_me,
CI.city_name, RE.region_name, CO.country_code
FROM MEMBER_ATTR MA
JOIN CITY CI ON MA.city_id = CI.city_id
JOIN REGION RE ON MA.region_id = RE.region_id
JOIN COUNTRY CO ON MA.country_id = CO.country_id
JOIN MEMBER_TEXT T ON MA.member_id = T.member_id
WHERE MA.birthday BETWEEN '1906-11-4' AND '1988-11-3'
AND MA.gender = 1
AND MA.seeking_gender = 0
ORDER BY MA.last_online DESC
ROWS 400
default:0 pool.5 Here's the plan:
PLAN JOIN (MA ORDER IDX_MEMBER_ATTR8 INDEX (IDX_MEMBER_ATTR1), CO
INDEX (PK_COUNTRY), RE INDEX (PK_REGION), CI INDEX (PK_CITY), T INDEX
(PK_MEMBER_TEXT)).
default:0 pool.5 This SQL took 0 second(s).


SQL and Plan from the backed-up then restored database:
default:0 pool.1 wms::createSearchSQL -
SELECT
MA.member_id, MA.username, MA.gender, MA.birthday, (CURRENT_TIMESTAMP
- MA.last_online),
MA.seeking_gender, MA.seeking_lower_age, MA.seeking_upper_age,
MA.seeking_radius,
MA.num_photos, T.catch_phrase, T.about_me,
CI.city_name, RE.region_name, CO.country_code
FROM MEMBER_ATTR MA
JOIN CITY CI ON MA.city_id = CI.city_id
JOIN REGION RE ON MA.region_id = RE.region_id
JOIN COUNTRY CO ON MA.country_id = CO.country_id
JOIN MEMBER_TEXT T ON MA.member_id = T.member_id
WHERE MA.birthday BETWEEN '1906-11-4' AND '1988-11-3'
AND MA.gender = 1
AND MA.seeking_gender = 0
ORDER BY MA.last_online DESC
ROWS 400
default:0 pool.1 Here's the plan:
PLAN SORT (JOIN (RE NATURAL, MA INDEX (IDX_MEMBER_ATTR3,
IDX_MEMBER_ATTR1), CO INDEX (PK_COUNTRY), CI INDEX (PK_CITY), T INDEX
(PK_MEMBER_TEXT))).
default:0 pool.1 This SQL took 47 second(s).

Yes, the plans are very different...and I really don't know much about
what it means. It seems to me superficially that the fast query uses
a plan with indexes directly used in the WHERE clause; whereas the
restored database uses indexes outside of the WHERE clause. Here's
some of my indexes which will not be obvious by the name of it:
CREATE ASC INDEX IDX_MEMBER_ATTR1 ON MEMBER_ATTR(BIRTHDAY);
CREATE ASC INDEX IDX_MEMBER_ATTR3 ON MEMBER_ATTR(REGION_ID);
CREATE DESC INDEX IDX_MEMBER_ATTR8 ON MEMBER_ATTR(LAST_ONLINE);

Thanks for your help thus far. I hope you can help more.
Jay