Re: [firebird-support] avoid query subselect
indeed much better thanks! On Friday, July 19, 2019, 02:34:20 PM GMT+3, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: Hi, i suppose this one is much faster SELECT P.ID, CASE WHEN EXISTS(SELECT * FROM CHILD C WHERE C.ID_PARENT=P.ID AND C.MYFIELD=3) ) THEN 1 ELSE 0 END AS ISDATA FROM PARENT P...INNER JOIN TABLE3 T3 ON P.ID=T3.INNER JOIN TABLE3 T4 ON P.ID=T4... Regards,Karol Biieniaszewski
Re: [firebird-support] avoid query subselect
Hi, i suppose this one is much faster SELECT P.ID , CASE WHEN EXISTS(SELECT * FROM CHILD C WHERE C.ID_PARENT=P.ID AND C.MYFIELD=3) ) THEN 1 ELSE 0 END AS ISDATA FROM PARENT P INNER JOIN TABLE3 T3 ON P.ID=T3. INNER JOIN TABLE3 T4 ON P.ID=T4. ... Regards, Karol Biieniaszewski
[firebird-support] Understand query plan Filter after Filter
Hi, what does it mean if below i have twice -> Filter -> Filter? Select Expression -> Filter -> Nested Loop Join (outer) -> Filter -> Table "RDB$DATABASE" as "X RDB$DATABASE" Full Scan -> Filter -> Filter -> Table "RDB$RELATION_FIELDS" as "RF" Access By ID -> Bitmap -> Index "RDB$INDEX_4" Range Scan (full match) Select Expression -> Filter -> Nested Loop Join (outer) -> Filter -> Table "RDB$RELATIONS" as "R" Full Scan -> Filter -> Table "RDB$DATABASE" as "Y RDB$DATABASE" Full Scan Karol Bieniaszewski
[firebird-support] avoid query subselect
HiI have a performance issue on a query where I need all parent record and from child table I only want a flag if there is any child record matching a condition,so I write something like that: SELECT P.ID,IIF(SUB.ID IS NULL,0,1) AS ISDATA FROM PARENT P LEFT JOIN (SELECT FIRST 1 PA.ID FROM PARENT PA INNER JOIN CHILD C ON C.ID_PARENT=PA.ID WHERE C.MYFIELD=3) SUB ON SUB.ID=A.ID INNER JOIN TABLE3 T3 ON P.ID=T3. INNER JOIN TABLE3 T4 ON P.ID=T4. ... I can't use direct left join because for a parent could be more than one record is there any way to replace subselect with something more efficient? thanks
