Hi,

> > > Actually changing this:
> > >
> > > JOIN Varer_Detail ON
> > > ((Varer_Detail.VarePlu_ID = Varer.Plu_Nr || '' and
> > > Varer_detail.Afdeling_ID = '001') or
> > > (Varer_Detail.VarePlu_ID = Varer.Plu_Nr || '' and
> > > Varer_detail.Afdeling_ID = '002'))
> > >
> > > to this:
> > >
> > > JOIN Varer_Detail ON
> > > ((Varer_Detail.VarePlu_ID = Varer.Plu_Nr and
> > > Varer_detail.Afdeling_ID IN ('001','002')))
> > >
> > > Runs equally fast
> >
> > This will depend on how much data is inside the tables.
> > The best is that allmost all primary indexes are completly used.

> > VARER - PK = PLU_NR
> > VARAR_DETAIL - PK = VAREPLU_ID, AFDELING_ID
> >
> > Optimally twice the PK for VARER_DETAIL should be used.
> >
> >
> > btw,. Did you test fetching all data !
>
> Yes.
> I have used it on my biggest DB which I have at this point.
> I use IBExpert and when testing performance ALWAYS does a FETCH ALL !!

I'm not familar with IBExpert, but i just want to know for sure.

> Given the above exampels the diff. between the one you gave me, and
> the last one is 21 seconds versus 22 seconds.
>
> So they are more or less the same.
>
> As I read the plan, it uses mostly PK.
>
> The main diff. is, that yours starts with the PK from afdeling, where
> as mine starts with LEVERANDOERER NATURAL. But the Tabel
> LEVERANDOERER never contains more than a few hundred records. Usually
> only 50 - 80 !

When i said completly used i meant that for compound index all segments are
used.
In your case with (Varer_Detail.VarePlu_ID = Varer.Plu_Nr and
Varer_detail.Afdeling_ID IN ('001','002')) the optimizer can only bind
Varer.Plu_Nr to a index, but this is only the first segment of the
Varer_Detail Primary Key. If you write the ON clause in this way
((Varer_Detail.VarePlu_ID = Varer.Plu_Nr and Varer_detail.Afdeling_ID =
'001') or ((Varer_Detail.VarePlu_ID = Varer.Plu_Nr and
Varer_detail.Afdeling_ID = '002')) the optimizer can bind both Plu_Nr and
'001' to the index as for Plu_Nr and '002'.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info