Hi,

In upgrading from Firebird 2.0 to Firebird 2.5, I have a query that has dramatically changed performance. It would be great if I could understand the performance information given in FlameRobin, and how this can be used to return the query to its former performance.

The statistics reported by FlameRobin for the two database versions are as follows:

Firebird 2.5:
827,493,131 fetches, 6 marks, 509 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 2,376,096 index, 200,925,311 seq.
Delta memory: 118,904 bytes.
Total execution time: 00:13:04 (hh:mm:ss)

Firebird 2.0:
16,930,040 fetches, 6 marks, 122,122 reads, 6 writes.
0 inserts, 0 updates, 0 deletes, 2,376,082 index, 1,252,998 seq.
Delta memory: 88,432 bytes
Total execution time: 18.109s

The Firebird 2.5 query was run with the Windows Classic Server 2.5.1.26351 64-bit. The database has the ODS Version 11.2 with a page size of 8192 and a default character set of UTF8. Page buffers is set to 2048.

The Firebird 2.0 query was run with the Windows Classic Server 2.0.5.13206 32-bit. The database has ODS Version 11 with a page size of 4096 and a default character set of ASCII. Page buffers is set to 75.

The query run is the following:
SELECT Phrase.*
FROM Phrase
INNER JOIN (
SELECT DISTINCT Phrase.PhraseKey
FROM Phrase,
(
SELECT PhraseKey, ParentPhraseKey
FROM Phrase
WHERE
Description CONTAINING 'Some Value'
AND PhraseologyKey = (SELECT PhraseologyKey FROM Phraseology WHERE Name = 'Some Name')
) Matches
WHERE
(
( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Phrase.PhraseKey, Matches.PhraseKey ) ) = 1 )
OR ( ( SELECT Flag FROM HAS_PHRASEOLOGY_PARENT( Matches.PhraseKey, Phrase.PhraseKey ) ) = 1 )
)
) T ON (Phrase.PhraseKey = T.PhraseKey) OR (Phrase.ParentPhraseKey = T.PhraseKey)

HAS_PHRASEOLOGY_PARENT is a stored procedure with the following definition:
SET TERM ^ ;
CREATE PROCEDURE HAS_PHRASEOLOGY_PARENT (
PHRASEKEY Char(18) CHARACTER SET OCTETS,
PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS )
RETURNS ( FLAG Integer )
AS
DECLARE VARIABLE CurrentKey CHAR(18) CHARACTER SET OCTETS;
BEGIN
CurrentKey = PhraseKey;
SELECT 0 FROM rdb$database INTO FLAG;
WHILE (CurrentKey IS NOT NULL) DO
BEGIN
IF (CurrentKey = ParentPhraseKey) THEN
BEGIN
Flag = 1;
CurrentKey = NULL;
END ELSE BEGIN
SELECT ParentPhraseKey
FROM Phrase
WHERE PhraseKey = :CurrentKey INTO :CurrentKey;
END
END
SUSPEND;
END^
SET TERM ; ^

The query plan for both database versions is:
PLAN (T MATCHES PHRASEOLOGY INDEX (RDB$33))
PLAN (RDB$DATABASE NATURAL)(PHRASE INDEX (RDB$PRIMARY31))
PLAN (RDB$DATABASE NATURAL)(PHRASE INDEX (RDB$PRIMARY31))
PLAN JOIN (SORT (JOIN (T PHRASE NATURAL, T MATCHES PHRASE NATURAL)), PHRASE INDEX (RDB$PRIMARY31, PHRASEPARENTSORTORD))

The schema of the PHRASE table is:
CREATE TABLE PHRASE
(
PHRASEKEY Char(18) CHARACTER SET OCTETS NOT NULL,
PHRASEOLOGYKEY Char(18) CHARACTER SET OCTETS NOT NULL,
CODE Varchar(20),
DESCRIPTION Blob sub_type 1,
PARENTPHRASEKEY Char(18) CHARACTER SET OCTETS,
UNIT Varchar(12),
SORTORD Integer NOT NULL,
DATEADD Timestamp NOT NULL,
DATEMOD Timestamp NOT NULL,
PRIMARY KEY (PHRASEKEY)
);
CREATE INDEX PHRASEPARENTSORTORD ON PHRASE (PARENTPHRASEKEY,SORTORD);

The schema of the PHRASEOLOGY table is:
CREATE TABLE PHRASEOLOGY
(
PHRASEOLOGYKEY Char(18) CHARACTER SET OCTETS NOT NULL,
NAME Varchar(50) NOT NULL,
NOTES Blob sub_type 1,
DATEADD Timestamp NOT NULL,
DATEMOD Timestamp NOT NULL,
PRIMARY KEY (PHRASEOLOGYKEY),
UNIQUE (NAME)
);

Regards,
Gareth Marshall