Hi all,

I have a performance/plan problem here...

Basically this: I have a view with a couple of joins. I created the view to
avoid re-typing the statement over and over again.

View DDL:

CREATE VIEW LEVERANCIER_NAW
(
RELATIEID,
NAAM,
ADRES,
POSTCODE,
PLAATS,
LAND,
VBANR,
BVHNR,
TELEFOON,
FAX
) AS
select l.RelatieID, r.naam, a.adres, a.postcode, a.plaats,
land.omschrijving,
l.vbanr, l.bvhnr, rt.telefoonnummer, rt2.telefoonnummer as fax
from leverancier l
join adres a on (l.relatieid = a.relatieid)
join relatie r on (l.relatieid = r.relatieid)
join land on (a.landcode = land.landcode)
left join RELATIE_TELEFOON rt on (l.relatieid = rt.relatieid and
rt.telefooncode = 1
and rt.nummer = 1)
left join RELATIE_TELEFOON rt2 on (l.relatieid = rt2.relatieid and
rt2.telefooncode = 2
and rt2.nummer = 1)
where r.actueel = 'T'
and a.adrescode = 2

When I do something like this

select * from LEVERANCIER_NAW
where relatieid = 60

I get the following plan:

JOIN (JOIN (JOIN (LEVERANCIER_NAW L INDEX (RDB$PRIMARY8),LEVERANCIER_NAW R
INDEX (RDB$PRIMARY4),LEVERANCIER_NAW A INDEX
(RDB$FOREIGN49,I_ADRES_RELATIE),LEVERANCIER_NAW LAND INDEX
(RDB$PRIMARY76)),LEVERANCIER_NAW RT INDEX
(RDB$FOREIGN16,RDB$FOREIGN17)),LEVERANCIER_NAW RT2 INDEX
(RDB$FOREIGN16,RDB$FOREIGN17))

Here, I can see that the primary key index (RDB$PRIMARY8) is used to get the
record from the LEVERANCIER table - fair enough.

Now, I created the following query:

select ar.aanbodid, ar.ARTIKELOMSCHRIJVING,
ar.POTHOOGTE,
ar.POTMAAT,
ar.AANTALPERFUST,
ar.AANTALDOZEN,
ar.OPMERKING,
ar.INKOOPPRIJS , l.RelatieID, l.naam, l.adres, l.postcode, l.plaats, l.land,
l.vbanr, l.bvhnr, l.telefoon, l.fax
from
aanbodsregel ar left outer join LEVERANCIER_naw l on ( l.relatieid =
ar.leverancierid)
where ar.aanbodid = 59

Here, the LEVERANCIER tables gets a NATURAL plan:

PLAN JOIN (AR INDEX (RDB$FOREIGN103),JOIN (JOIN (JOIN (L L NATURAL,L R INDEX
(RDB$PRIMARY4),L A INDEX (RDB$FOREIGN49,I_ADRES_RELATIE),L LAND INDEX
(RDB$PRIMARY76)),L RT INDEX (RDB$FOREIGN16,RDB$FOREIGN17)),L RT2 INDEX
(RDB$FOREIGN16,RDB$FOREIGN17)))

this is very slow...

However, if I re-type the query with the view statement included, like this:

select ar.aanbodid, ar.ARTIKELOMSCHRIJVING,
ar.POTHOOGTE,
ar.POTMAAT,
ar.AANTALPERFUST,
ar.AANTALDOZEN,
ar.OPMERKING,
ar.INKOOPPRIJS
, l.RelatieID, r.naam, a.adres, a.postcode, a.plaats, land.omschrijving,
l.vbanr, l.bvhnr, rt.telefoonnummer, rt2.telefoonnummer as fax
from AANBODSREGEL ar
left outer join leverancier l on (l.relatieid = ar.leverancierid)
left outer join adres a on ( l.relatieid = a.relatieid and a.adrescode = 2)
left outer join relatie r on ( l.relatieid = r.relatieid and r.actueel =
'T')
left outer join land on (a.landcode = land.landcode)
left outer join RELATIE_TELEFOON rt on (l.relatieid = rt.relatieid and
rt.telefooncode = 1
and rt.nummer = 1)
left outer join RELATIE_TELEFOON rt2 on (l.relatieid = rt2.relatieid and
rt2.telefooncode = 2
and rt2.nummer = 1)
where ar.aanbodid = 59

I get the following plan:

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (AR INDEX (RDB$FOREIGN103),L INDEX
(RDB$PRIMARY8)),A INDEX (RDB$FOREIGN49)),R INDEX (RDB$PRIMARY4)),LAND INDEX
(RDB$PRIMARY76)),RT INDEX (RDB$FOREIGN16,RDB$FOREIGN17)),RT2 INDEX
(RDB$FOREIGN16,RDB$FOREIGN17))

Here, the primary key index on LEVERANCIER is used again...

What can I do about this? Comments anyone?
--
Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase
http://www.interbaseworkbench.com