Hi Set,

Thank you very much for your help! Your query runs much faster than even the original query did, and is easier to understand.

Regards,
Gareth

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >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
>