Hi !

I have the problem with a query, that is very slow.

SELECT *
FROM ScherenOberstange
JOIN SchOberFarbe
ON ScherenOberstange.Farbe_ID = SchOberFarbe.ID
JOIN V_TextBaustein Farbe
ON Farbe.TB_ID = SchOberFarbe.Bezeichnung_TB
WHERE ScherenOberstange.Artikel_ID = 162

Firebird comes up with following plan:

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (FARBE TEXTBAUSTEIN NATURAL,FARBE
TEXTBAUSTEINVARIANTE INDEX (FK_TEXTBAUSTEINVARIANTE_1)),FARBE
TEXTBAUSTEINREFSPRACHE INDEX (FK_TEXTBAUSTEINREFSPRACHE_1)),FARBE
REFSPRACHE INDEX (PK_REFSPRACHE)),FARBE TBVSTATUS INDEX
(PK_TBVSTATUS)),JOIN (SCHOBERFARBE INDEX
(FK_SCHOBERFARBE_1),SCHERENOBERSTANGE INDEX (FK_SCHERENOBERSTANGE_1)))

It goes through the complete view textbaustein instead of using the
right ID from Farbe.TB_ID.

The View V_Textbaustein is:

CREATE VIEW V_TEXTBAUSTEIN(
TB_ID,
TB_BEZEICHNUNG,
TBV_ID,
TBV_KURZTEXT,
TBV_LANGTEXT,
TBV_SPRACHEID,
TBV_SPRACHE,
TBV_STATUSID,
TBV_STATUS)
AS
SELECT TextBaustein.ID ,
TextBaustein.Bezeichnung,
TextBausteinVariante.ID,
TextBausteinVariante.KurzText,
TextBausteinVariante.LangText,
RefSprache.ID,
RefSprache.Bezeichnung,
TBVStatus.ID,
TBVStatus.Bezeichnung
FROM TextBaustein
JOIN TextBausteinVariante
ON TextBausteinVariante.TextBaustein_ID = TextBaustein.ID
LEFT JOIN TextbausteinRefSprache
ON TextbausteinVariante.ID =
TextbausteinRefSprache.TextbausteinVariante_ID
LEFT JOIN RefSprache
ON TextbausteinRefSprache.RefSprache_ID = RefSprache.ID
LEFT JOIN TBVStatus
ON TextbausteinVariante.Status = TBVStatus.ID
;

If I change the LEFT JOINS to INNER JOINS there is no problem:

plan:
PLAN JOIN (SCHERENOBERSTANGE INDEX
(FK_SCHERENOBERSTANGE_1),SCHOBERFARBE INDEX (PK_SCHOBERFARBE),FARBE
TEXTBAUSTEIN INDEX (PK_TEXTBAUSTEIN),FARBE TEXTBAUSTEINVARIANTE INDEX
(FK_TEXTBAUSTEINVARIANTE_1),FARBE TBVSTATUS INDEX (PK_TBVSTATUS),FARBE
TEXTBAUSTEINREFSPRACHE INDEX (FK_TEXTBAUSTEINREFSPRACHE_1),FARBE
REFSPRACHE INDEX (PK_REFSPRACHE))

View:

CREATE VIEW V_TEXTBAUSTEIN2(
TB_ID,
TB_BEZEICHNUNG,
TBV_ID,
TBV_KURZTEXT,
TBV_LANGTEXT,
TBV_SPRACHEID,
TBV_SPRACHE,
TBV_STATUSID,
TBV_STATUS)
AS
SELECT TextBaustein.ID ,
TextBaustein.Bezeichnung,
TextBausteinVariante.ID,
TextBausteinVariante.KurzText,
TextBausteinVariante.LangText,
RefSprache.ID,
RefSprache.Bezeichnung,
TBVStatus.ID,
TBVStatus.Bezeichnung
FROM TextBaustein
JOIN TextBausteinVariante
ON TextBausteinVariante.TextBaustein_ID = TextBaustein.ID
JOIN TextbausteinRefSprache
ON TextbausteinVariante.ID =
TextbausteinRefSprache.TextbausteinVariante_ID
JOIN RefSprache
ON TextbausteinRefSprache.RefSprache_ID = RefSprache.ID
JOIN TBVStatus
ON TextbausteinVariante.Status = TBVStatus.ID

I don't understand, why the two LEFT JOINS changes the plan, although
the corresponding tables RefSprach and TBVStatus are not necessary in
the query.

Any solution? Should I make my own plan, or should I make no use of
LEFT JOINS in Views?

If any other information is needed, please ask!

Thanks