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

Wow, Gareth, a query that is both lean and unreadable simultaneously!

With identical plans, I'm not knowledgeable enough to answer your question, so rather than answering it, I wonder if the below select returns the desired result and how it is performancewise?

with recursive Matches as
(SELECT p.PhraseKey
FROM Phrase p
JOIN Phraseology Po on p.PhraseologyKey = po.PhraseologyKey
WHERE p.Description CONTAINING 'Some Value'
AND po.Name = 'Some Name'),
RecursiveParent as
(SELECT p.PhraseKey, p.ParentPhraseKey
FROM Phrase p
JOIN Matches m on p.PhraseKey = m.PhraseKey
UNION ALL
SELECT p.PhraseKey, p.ParentPhraseKey
FROM Phrase p
JOIN RecursiveParent rp on p.PhraseKey = rp.ParentPhraseKey),
RecursiveChild as
(SELECT p.PhraseKey, p.ParentPhraseKey
FROM Phrase p
JOIN Matches m on p.PhraseKey = m.PhraseKey
UNION ALL
SELECT p.PhraseKey, p.ParentPhraseKey
FROM Phrase p
JOIN RecursiveChild rc on p.ParentPhraseKey = rc.PhraseKey)

SELECT p.*
FROM Phrase p
JOIN RecursiveParent rp on p.PhraseKey = rp.PhraseKey
UNION
SELECT p.*
FROM Phrase p
JOIN RecursiveChild rc on p.PhraseKey = rc.PhraseKey

HTH,
Set