Hi Svein

Here are full details...

Slow query (From Flame Robin)

SELECT DelAddress, ClientOrders.EntityRef, CliStkRef,
ClientOrders.CEntityRef, ClientStock.ItemRef, ClientOrders.ClOrderRef,
Supply, orderno, FREIGHTINC, quote, Quantity, CliOrderNo, ReqDate,
OrderDate, IfLate, CEntityRef, ORegionRef, UnitAmount, TaxAmount,
Client.ClientName, ItemName.Name AS ProductName, TrustRef ,
StockLevel.ShelfQty AS InStock, StockLevel.CustOrdQty AS OnClientOrder,
StockLevel.SupOrdrQty AS SupplyOrders
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 ClientStock.ConsignRef IS NULL
AND ClientStock.PSlipRef IS NULL
AND ClientStock.TransRef IS NULL
AND (ClientStock.ActionType IS NULL OR (ClientStock.ActionType <> 'H'
and ClientStock.ActionType <> 'C' and ClientStock.ActionType <> 'P'))
AND Completed <> 'T'

Prepare time: 00:00:00.
PLAN JOIN (STOCKLEVEL INDEX (STOCKLEVEL_REGIONREF),ITEMNAME INDEX
(ITEMNAME_ITEMREF),CLIENTSTOCK INDEX
(CLIENTSTOCK_TRANSREF,CLIENTSTOCK_PSLIPLINE,CLIENTSTOCK_CONGNLINE,CLIENTSTOCK_PSLIPREF,CLIENTSTOCK_CONSIGNREF,CLIENTSTOCK_ITEMREF),CLIENTORDERS
INDEX (CLIENTORDERS_CLORDERREF),CLIENT INDEX (CLIENT_ENTITYREF))


Executing...
Done.
Execute time: 00:00:21.


Fast Query (without stocklevel and related select fields)


SELECT DelAddress, ClientOrders.EntityRef, CliStkRef,
ClientOrders.CEntityRef, ClientStock.ItemRef, ClientOrders.ClOrderRef,
Supply, orderno, FREIGHTINC, quote, Quantity, CliOrderNo, ReqDate,
OrderDate, IfLate, CEntityRef, ORegionRef, UnitAmount, TaxAmount,
Client.ClientName, ItemName.Name AS ProductName, TrustRef
FROM ClientOrders
JOIN ClientStock ON ClientOrders.ClOrderRef = ClientStock.ClOrderRef
JOIN Client ON Client.EntityRef = ClientOrders.EntityRef JOIN ItemName
ON ItemName.ItemRef = ClientStock.ItemRef
WHERE ClientStock.ConsignRef IS NULL
AND ClientStock.PSlipRef IS NULL
AND ClientStock.TransRef IS NULL
AND (ClientStock.ActionType IS NULL OR (ClientStock.ActionType <> 'H'
and ClientStock.ActionType <> 'C' and ClientStock.ActionType <> 'P'))
AND Completed <> 'T'
Prepare time: 00:00:00.
PLAN JOIN (CLIENTORDERS NATURAL,CLIENT INDEX
(CLIENT_ENTITYREF),CLIENTSTOCK INDEX
(CLIENTSTOCK_TRANSREF,CLIENTSTOCK_PSLIPLINE,CLIENTSTOCK_CONGNLINE,CLIENTSTOCK_PSLIPREF,CLIENTSTOCK_CONSIGNREF,CLIENTSTOCK_CLORDERREF),ITEMNAME
INDEX (ITEMNAME_ITEMREF))

Executing...
Done.
Execute time: 00:00:00.



I tried forcing the slow plan on the fast query (as suggested) but got
errors


Engine Code : 335544642
Engine Message :
index ITEMNAME_ITEMREF cannot be used in the specified plan


Everyting is a table (no views)

In answer to your questions...

1- YES, StockLevel has the following indexes

STOCKLEVEL_PK (RegionRef, ItemRef)
STOCKLEVEL_ITEMREF (ItemRef)
STOCKLEVEL_REGIONREF (RegionRef)

ClientStock has many indexes some of which are composites. I can
provide fuller details if required.

2- All are tables. I hope seeing the full SQL clears things up.

Note ClientStock has many null fields with indexes which are required
for other functionality in the software. If clientstock is the culpret
I will just have to put up with the problem.


Thanks for all your help Svein.



Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Svein Erling Tysvær wrote:

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