Change ConsignRef to TableName.ConsignRef, it'll help the planner.
Are there foreign keys for all the items in the join constraints?

Rick DeBay

-----Original Message-----
From: Robert martin [mailto:rob@...]
Sent: Tuesday, January 25, 2005 6:27 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL Performance


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,CLIENT
STOCK_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






Yahoo! Groups Links