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