>
> This will ensure that no index can be used for Varer.Plu_Nr,

because Plu_Nr

> is a varchar() i concatenate it with a empty string. If it was a

numeric

> datatype then you can add it with zero (Plu_Number + 0).
> A index can only be used when the left or right side of the

comparison

> contains a segment of the index. Als should the other side not

contain a

> expression which depends on the same table.

Understood....

>
> > I have tried without, and it seems just as fastm but with a

different

> > plan.
> >
> > 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 !!

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 !

Regards

Michael