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


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)))

Adapted Plan
------------------------------------------------
PLAN SORT (JOIN (JOIN (JOIN (JOIN (O INDEX
(FK_ONSCREEN_2K_TIMTRAN,ONSCREEN_2K_TIMEDATE),M INDEX (PK_MATTERS),A
INDEX (PK_ATTYPE)),PR INDEX (PK_PRATES)),L INDEX (PK_LACODE)),LA INDEX
(PK_RATES)))

Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 32.00 ms
Avg fetch time: 1.88 ms

Memory
------------------------------------------------
Current: 2,303,780
Max : 2,425,092
Buffers: 8,192

Operations
------------------------------------------------
Read : 29
Writes : 0
Fetches: 468


Enchanced Info:
+--------------------------+-----------+-----------+-------------+-----
----+---------+---------+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts |
| | Total | reads | reads
| | | |
+--------------------------+-----------+-----------+-------------+-----
----+---------+---------+
| ATTYPE| 0 | 17 | 0
| 0 | 0 | 0 |
| LACODE| 0 | 17 | 0
| 0 | 0 | 0 |
| MATTERS| 0 | 17 | 0
| 0 | 0 | 0 |
| ONSCREEN_2K| 0 | 64 | 0
| 0 | 0 | 0 |
| RATES| 0 | 17 | 0
| 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+-----
----+---------+---------+

DATABASE AFTER 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


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)))

Adapted 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 (PK_ATTYPE)),PR INDEX (PK_PRATES)),L INDEX (PK_LACODE)),LA
INDEX (PK_RATES)))

Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 31.00 ms
Avg fetch time: 1.82 ms

Memory
------------------------------------------------
Current: 2,299,012
Max : 2,425,356
Buffers: 8,192

Operations
------------------------------------------------
Read : 0
Writes : 0
Fetches: 2,243


Enchanced Info:
+--------------------------+-----------+-----------+-------------+-----
----+---------+---------+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts |
| | Total | reads | reads
| | | |
+--------------------------+-----------+-----------+-------------+-----
----+---------+---------+
| ATTYPE| 0 | 17 | 0
| 0 | 0 | 0 |
| LACODE| 0 | 17 | 0
| 0 | 0 | 0 |
| MATTERS| 0 | 0 | 229
| 0 | 0 | 0 |
| ONSCREEN_2K| 0 | 64 | 0
| 0 | 0 | 0 |
| RATES| 0 | 17 | 0
| 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+-----
----+---------+---------+