Well, I guess Firebird 1.5 consider these two plans approximately equally good, and then it is just a matter of a small change in the index statistics to change which plan Firebird chooses (in theory, I think the difference may be as small as changing the value of one field in one table). I would assume that a restore recalculates statistics, and that it wasn't the restore itself that made the difference (it wouldn't surprise me if the 'good database' all of a sudden started behaving as the 'restored database'). What the +0 did, was to prevent the bad plan from being used - that particular plan can no longer be used, although Firebird is still free to choose other good and bad plans - and thus help Firebird making a better choice.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of lee simpson
Sent: 28. februar 2008 10:10
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] GBAK restore uses different SQL Plan

Thanks Svein - I tried it and it works - a simple as that then!

I've now got the task to check other SQLs for performance - but at least
there's a solution.

One question though, do you know why the initial database's plan didn't use
the foreign key?

Thanks again.

Lee

On 2/27/08, Svein Erling Tysvaer <svein.erling.tysvaer@...>
wrote:

>
> Simple, Lee, just prevent the index FK_ONSCREEN_2K_CLIENTMATTER from
> being used:
>
> JOIN onscreen_2k O ON (o.CLIENTCODE+0 = m.CLIENTCODE
> AND o.MATTERCODE = m.MATTERCODE)
>
> /*substitute +0 with ||'' if it is a character field */
>
> HTH,
> Set
>
> lee.simpson99 wrote:
> > I am running Firebird 1.5.2.4731 on Windows XP and am having a problem
> > with the speed of some SQLs after a GBAK Restore. Originally I had
> > data pumped a large GDB database (650Mb) and 'fixed' a very slow
> > running SQL, unfortunately this 'fix' was undone when the database was
> > GBAKd and Resored.
> > Investigating the problem showed that the Plan used before and after
> > the restore was different.
> > I have tried GBAKing without garbage collection, Transportable and non
> > Transportable format, 'Use all Space' in the Restore all with the same
> > effect. The indexes were not deactivated and both the Original and
> > Restored had a page size of 4096.
> >
> > I tried this on another much smaller test GDB with the same result.
> >
> > Both databases were running the identical SQL with identical metadata
> > (IB DB Compare showed no differences).
> >
> > I have copied both performance analyses below (smaller database!), and
> > shows an additional 229 non indexed reads on the matters table. (The
> > larger database had over 52000 non indexed reads on the matters table)
> >
> > ONSCREEN_2K has the constraint (amongst others):
> > CONSTRAINT FK_ONSCREEN_2K_CLIENTMATTER FOREIGN KEY (CLIENTCODE,
> > MATTERCODE) REFERENCES MATTERS (CLIENTCODE, MATTERCODE) ON UPDATE
> > CASCADE;
> >
> > MATTERS has the primary key:
> > CONSTRAINT PK_MATTERS PRIMARY KEY (CLIENTCODE, MATTERCODE);
> >
> > Any help would be much appreciated!
> >
> > INITIAL DATABASE BEFORE GBAK AND RESTORE:
> > =========================================
> > Query
> > ------------------------------------------------
> > SELECT
> > O.TIMEDATE,
> > O.ID <http://o.id/>,
> > M.CLIENTCODE,
> > M.MATTERCODE
> > FROM
> > Matters M
> > INNER JOIN onscreen_2k O
> > ON (o.CLIENTCODE = m.CLIENTCODE AND o.MATTERCODE = m.MATTERCODE)
> > INNER JOIN ATTYPE A
> > ON (A.CODE = O.ATTYPECODE)
> > left JOIN PRATES PR
> > ON (PR.RATESCHEME = O.RATESCHEMECODE AND PR.FECODE = O.FECODE AND
> > PR.ATTCODE = O.ATTYPECODE)
> > LEFT JOIN LACODE L
> > ON L.CODE = M.LEGAIDCODE
> > LEFT JOIN RATES LA
> > ON (LA.RATESCHEME = O.RATESCHEMECODE AND LA.LACODE = L.RATECODE AND
> > LA.ATTCODE = O.ATTYPECODE)
> > WHERE
> > O.FECODE = 5
> > AND O.PAUSEVALUE = 0 /* DON'T POST PAUSED ITEMS TO TIMTRANS */
> > AND
> > O.TIMEDATE BETWEEN '13.02.2000' AND '26.02.2008'
> > AND
> > O.TIMTRAN_ID = -1
> > AND
> > O.ID <http://o.id/> <> -1
> > ORDER BY
> > O.TIMEDATE, O.ID <http://o.id/>
> >
> > Good Plan
> > ------------------------------------------------
> > PLAN SORT (JOIN (JOIN (JOIN (JOIN (O INDEX
> > (FK_ONSCREEN_2K_TIMTRAN,ONSCREEN_2K_TIMEDATE),M INDEX
> > (RDB$PRIMARY64),A INDEX (RDB$PRIMARY6)),PR INDEX (RDB$PRIMARY82)),L
> > INDEX (RDB$PRIMARY55)),LA INDEX (RDB$PRIMARY85)))
> >
> >
> > Bad Plan
> > ------------------------------------------------
> > PLAN SORT (JOIN (JOIN (JOIN (JOIN (M NATURAL,O INDEX
> > (FK_ONSCREEN_2K_CLIENTMATTER,FK_ONSCREEN_2K_TIMTRAN,ONSCREEN_2K_TIMEDAT
> > E),A INDEX (RDB$PRIMARY6)),PR INDEX (RDB$PRIMARY82)),L INDEX
> > (RDB$PRIMARY55)),LA INDEX (RDB$PRIMARY85)))