--- In firebird-support@yahoogroups.com, Robert martin wrote:

> I identified the stocklevel join as the problem by removing it and
> seeing performance greatly increase. Now that I look closer at the
> Plan I see what you mean about the ClientStock join. With the
> StockLevel stuff removed I get the following plan...
>
> SELECT *
> FROM ClientOrders
> JOIN ClientStock ON ClientOrders.ClOrderRef = ClientStock.ClOrderRef
> JOIN Client ON Client.EntityRef = ClientOrders.EntityRef
> JOIN ItemName ON ItemName.ItemRef = ClientStock.ItemRef
> WHERE ConsignRef IS NULL
> AND ClientStock.PSlipRef IS NULL
> AND ClientStock.TransRef IS NULL;

Quick plan:

> PLAN JOIN (CLIENTSTOCK INDEX
> (CLIENTSTOCK_TRANSREF,CLIENTSTOCK_PSLIPLINE,CLIENTSTOCK_CONGNLINE,
> CLIENTSTOCK_PSLIPREF,CLIENTSTOCK_CONSIGNREF),CLIENTORDERS
> INDEX (CLIENTORDERS_CLORDERREF),CLIENT INDEX (CLIENT_ENTITYREF),
> ITEMNAME INDEX (ITEMNAME_ITEMREF))

Slow plan (with StockLevel included):

> PLAN JOIN (CLIENTSTOCK INDEX
> (CLIENTSTOCK_PSLIPLINE,CLIENTSTOCK_PSLIPREF,CLIENTSTOCK_CONGNLINE,
> CLIENTSTOCK_TRANSREF,CLIENTSTOCK_CONSIGNREF),STOCKLEVEL
> INDEX (STOCKLEVEL_PK),ITEMNAME INDEX (ITEMNAME_ITEMREF),CLIENTORDERS
> INDEX (CLIENTORDERS_CLORDERREF),CLIENT INDEX (CLIENT_ENTITYREF))

Try forcing the above plan (just remove StockLevel) and see if it is
still quick. If not, then StockLevel is more or less innocent and we
have to find out why the plan changes the order of things.

> The indexes for ClientStock all exist, however there is also an
> index (CLIENTSTOCK_ClOrderRef (ClOrderRef) ) that Indexes the field
> that is joined I would have expected the plan to be something like:
>
> PLAN JOIN (CLIENTSTOCK INDEX (CLIENTSTOCK_CLORDERREF), CLIENTORDERS
> INDEX (CLIENTORDERS_CLORDERREF),CLIENT INDEX (CLIENT_ENTITYREF),
> ITEMNAME INDEX (ITEMNAME_ITEMREF))

That is not a possible plan, for the first table of the plan it can
only use indexes for fields in the WHERE clause (unless you use ORDER
BY or something). But you could try to remove indexes for fields where
a lot of the records is NULL.

I've got two more questions:

1) Do you have any duplicate indexes (e.g. a separate index that
duplicates the primary key or a unique constraint)
2) Either you do not show us the full SQL, or some of the 'tables' are
views. If all these are tables, there is no way the indexes
CLIENTSTOCK_PSLIPLINE and CLIENTSTOCK_CONGNLINE could be used.

I'm puzzled by this being slow with StockLevel included and fast
otherwise.

Set