>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
