Re: [firebird-support] Full join and indices

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Re: [firebird-support] Full join and indices

[setysvar setys...@gmail.com [firebird-support]]

Den 23.01.2017 13:05, skrev Kjell Rilbe 
kjell.ri...@marknadsinformation.se [firebird-support]:

Hi,

I just needed to do a full outer join between two very simple tables G 
and N, each with a single-column PK of identical type char(12). I 
noticed that the plan produces was:


PLAN JOIN (N NATURAL, G NATURAL)

I stopped the query after ~5 minutes. Then I thought I can do this 
calculation in two steps, so I did a left outer join from G to N 
first, then a left outer join from N to G (equivalent to a right outer 
join from G to N). These queries were executed with these plans:


PLAN JOIN (G NATURAL, N INDEX (PK_N))
PLAN JOIN (N NATURAL, G INDEX (PK_G))

They both executed in less than half a minute each.

Now, I was wondering why the full outer join can't do a left join 
using the right table's PK and then a right join using the other 
table's PK. This is essentially what I did with the latter two queries.


The full join operation seems to do something that's a lot less 
efficient (whatever it is that it does).


I'm still on FB 2.5.2, so... has this been improved in 3.0?

Thanks,
Kjell


Not quite answering your question, Kjell, but I do remember having 
observed that some queries with outer joins take too long and that I 
concluded that Firebird is great with inner JOINs, but performancewise 
rather poor with some outer JOINs. I think I once solved one such case 
using a CTE (at least I attempted using a CTE. I think, but am not 100% 
certain it worked. However, my query involved more than two tables, as 
indicated by M in square brackets below):


WITH TMP(PK) as
(SELECT PK FROM G
 UNION
 SELECT PK FROM N)
SELECT ...
FROM TMP
LEFT JOIN G ON TMP.PK = G.PK
LEFT JOIN N ON TMP.PK = N.PK
[ JOIN M ON M.Field = COALESCE(G.Field, N.Field) ]

Good to read you found a similar way to solve your particular problem.
Set

[firebird-support] Full join and indices

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Hi,

I just needed to do a full outer join between two very simple tables G 
and N, each with a single-column PK of identical type char(12). I 
noticed that the plan produces was:


PLAN JOIN (N NATURAL, G NATURAL)

I stopped the query after ~5 minutes. Then I thought I can do this 
calculation in two steps, so I did a left outer join from G to N first, 
then a left outer join from N to G (equivalent to a right outer join 
from G to N). These queries were executed with these plans:


PLAN JOIN (G NATURAL, N INDEX (PK_N))
PLAN JOIN (N NATURAL, G INDEX (PK_G))

They both executed in less than half a minute each.

Now, I was wondering why the full outer join can't do a left join using 
the right table's PK and then a right join using the other table's PK. 
This is essentially what I did with the latter two queries.


The full join operation seems to do something that's a lot less 
efficient (whatever it is that it does).


I'm still on FB 2.5.2, so... has this been improved in 3.0?

Thanks,
Kjell
--

Marknadsinformation logotyp

Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64

Marknadsinformation i Sverige AB
Ulvsundavägen 106C
168 67 Bromma
www.marknadsinformation.se 
08-514 905 90

Företagskontakt.se 
Personkontakt.se