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,
> 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 <> -1
> ORDER BY
> O.TIMEDATE, 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)))