Hi All

I have the following SQL which is slow for larger datasets...



SELECT *
FROM ClientOrders
JOIN ClientStock ON ClientOrders.ClOrderRef = ClientStock.ClOrderRef
JOIN StockLevel ON (StockLevel.ItemRef = ClientStock.ItemRef AND
StockLevel.RegionRef = 74)
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;

The culprit is the following line

JOIN StockLevel ON (StockLevel.ItemRef = ClientStock.ItemRef AND
StockLevel.RegionRef = 74)

I need this as the ItemRef and regionref form a composite primary key
into StockLevel. The following plan is created....

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

StockLevel_PK is ItemRef, RegionRef

Is there a way to improve the performance of the above (Note the Select
clause has been replaced by a * for this example).


Any suggestions would be greatly appreciated.

Rob