Thanks Helen & Gary for your suggestions.

However your suggestions only reduce the SQL time by 100ms (at most). It now takes around 2.9 seconds. I assume this is something to do with my table structures / Indexing. I include the following table and index definitions. Any suggestions would be greatly appreciated.


CREATE TABLE Supplyorder
(
SupOrdrRef Numeric(11) NOT NULL,
EntityRef Numeric(11),
OrderDate Date,
OrderNo VarChar(12),
DelAddr VarChar(254),
Comment VarChar(254),
SystemDate Date,
sEntityRef Numeric(11),
PtdFlag D_Boolean,
PeriodNum Numeric(6),
EmailFlg D_Boolean,
CONSTRAINT Supplyorder_SupOrdrRef PRIMARY KEY(SupOrdrRef)
)^

CREATE INDEX Supplyorder_Entityref ON Supplyorder (Entityref)^

CREATE TABLE Supplyline
(
SupLineRef Numeric(11) NOT NULL,
SupOrdrRef Numeric(11),
PackSlpRef Numeric(11),
SupTranRef Numeric(11),
ItemRef Numeric(11),
Quantity Numeric(13,4),
CancelFlag D_Boolean,
CostUpFlag D_Boolean,
PreArrDate Date,
ArriveDate Date,
SOUnitCost Numeric(13,2),
Comment VarChar(30),
SORDate Date,
RegionRef Numeric(11),
CONSTRAINT Supplyline_SupLineRef PRIMARY KEY(SupLineRef)
)^

CREATE INDEX Supplyline_Supordrref ON Supplyline (Supordrref)^
CREATE INDEX Supplyline_Packslpref ON Supplyline (Packslpref)^
CREATE INDEX Supplyline_Suptranref ON Supplyline (Suptranref)^
CREATE INDEX Supplyline_Itemref ON Supplyline (Itemref)^

Any suggestions would be greatly appreicated :)

Rob Martin
Software Engineer

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

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Monday, November 22, 2004 6:55 PM
Subject: Re: [firebird-support] SQL Performance problem


At 02:44 PM 22/11/2004 +1300, you wrote:

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

select
so.SupOrdrRef,
so.OrderNo,
so.OrderDate,
so.Comment,
so.DelAddr,
so.PtdFlag,
so.sEntityRef
FROM SupplyOrder so
where
so.EntityRef = 8
and exists (
select 1 from SupplyLine sl
where
sl.SupordrRef = so.SupordrRef
AND sl.CancelFlag = 'F'
AND sl.SupTranRef IS Null
AND sl.PackSlpRef IS Null)



[Non-text portions of this message have been removed]