Hi,

> Why is it, that you put a
>
> || ''
>
> after the
>
> Varer.Plu_Nr
>
> What effect does this have ?

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.

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


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