Hi Robert

try this

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

Regards
Gary

----- Original Message -----
From: "Robert martin" <rob@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, November 22, 2004 3:44 AM
Subject: [firebird-support] SQL Performance problem


>
> 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
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>