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

--- In firebird-support@yahoogroups.com, Robert martin wrote:
> 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