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 >
