2018. 06. 01. 17:33 keltezéssel, liviuslivius
liviuslivius@... [firebird-support] írta:

> Hi,
>
> can you show some sample?
> And what indexes have  you created?
> Can you show plan for both queries and real queries itself?
> What filter "where" are you using?
>
> I am really interested because i have compared both queries with much
> more populated tables and i have got better results time, fetches and
> much bigger difference was memory usage.

I cannot provide any information from the real database. Your solution
is slow in standalone mode for me. The earlier solution slow after
included/adapted into the real database. Then wrote a recursive function
(see below) which is fast for me. I jumped into this recursive CTE
subject because the language reference say "A great benefit of recursive
CTE s is that they use far less memory and CPU cycles than an equivalent
recursive stored procedure.".

CREATE FUNCTION GET_TOP_PARENT (IN_ID BIGINT) RETURNS BIGINT AS
DECLARE VARIABLE TMP_PARENT_ID BIGINT DEFAULT NULL;
BEGIN
IF (IN_ID IS NOT NULL) THEN SELECT PARENT_ID FROM TABLE1 WHERE
(ID=:IN_ID) INTO TMP_PARENT_ID;
IF (TMP_PARENT_ID IS NOT NULL) THEN GET_TOP_PARENT(TMP_PARENT_ID);
ELSE TMP_PARENT_ID=IN_ID;
RETURN TMP_PARENT_ID;
END

SELECT TABLE1.*, GET_TOP_PARENT(TABLE1.PARENT_ID) AS TOP_PARENT_ID FROM
TABLE1

Gabor