Hi All

I am struggling to get the normal (high) performance from the following SQL.
It is designed to retrieve 1 record per order that has one or more active
lines - a list of incomplete orders. It started as ...

SELECT DISTINCT so.SupOrdrRef, OrderNo, OrderDate, so.Comment, DelAddr,
PtdFlag, sEntityRef
'FROM SupplyOrder so
JOIN SupplyLine sl ON sl.SupordrRef = so.SupordrRef
WHERE so.EntityRef = 8 AND sl.CancelFlag = 'F' AND sl.SupTranRef IS Null AND
sl.PackSlpRef IS Null;

It was too slow so it has evolved to ...

SELECT SupOrdrRef, OrderNo, OrderDate, Comment, DelAddr, PtdFlag, sEntityRef
FROM SupplyOrder
WHERE EntityRef = 8 AND SupOrdrRef IN (SELECT DISTINCT SupOrdrRef FROM
SupplyLine WHERE SupTranRef IS Null AND PackSlpRef IS Null AND CancelFlag =
'F' );


This is slightly faster. The 8 is actually a parameter in the real query.
The SQL takes 3 sec, SupplyOrder has 2300 records and SupplyLine has 18000
records. SupOrdrRef is the PK of SupplyOrder and an indexed field in
SupplyLine. The sub select runs in 15ms by itself. Any suggestions would
be greatly appreciated :)








Rob Martin
Software Engineer

phone 03 377 0495
fax 03 377 0496
web www.chreos.com
Wild Software Ltd