Hi Svein

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;


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


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

Tring to force the above plan gives me an error saying
CLIENTORDERS_CLORDERREF cannot be used in the specified plan.

I may just have to go with slow :-(

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:

>
> Hi Rob!
>
> Are you sure that it is
> JOIN StockLevel ON (StockLevel.ItemRef = ClientStock.ItemRef AND
> StockLevel.RegionRef = 74)
> that is the culprit? To me (without knowing the data) it seems fine.
> Rather, I would question all the indexes used for ClientStock.
> Obviously, you didn't show us the entire SQL (since a few of the
> indexes used is not used in your select). Check if any of the indexes
> used has low selectivity (many duplicates), or if one of the indexes
> has brilliant selectivity compared to all others.
>
> What is the plan if you do not include StockLevel in your select? As I
> said, I cannot see anything wrong in that part of the select, but if
> the plan changes significantly, it may still be the cause for things
> taking longer time.
>
> HTH,
> Set
>