Hi Gary

The SQL takes 15ms and returns 1865 records. I just went to add the index you mentioned but discovered I already had an index on EntityRef. I missed it when I compiled my origninal email (cutting and pasting from my script).

Both parts of the SQL seem to be fast by themselves, it is the combination of the two that seems to slow them down.

I have just solved the problem by doing the following ....

ORIGINAL SQL

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

FIX

Create a View

CREATE View TEST2 (SupOrdrRef) AS
SELECT DISTINCT SupOrdrRef FROM SupplyLine WHERE CancelFlag = 'F' AND SupTranRef IS Null AND PackSlpRef IS Null


Change First SQL to

SELECT SupplyOrder.SupOrdrRef, OrderNo, OrderDate, Comment, DelAddr, PtdFlag, sEntityRef
FROM SupplyOrder
JOIN Test2 ON SupplyOrder.SupOrdrRef = Test2.SupOrdrRef

Performance is now around 30ms (instead of 3 sec). I dont know why this works but having this is a view is acceptable so I will go with it.

Thanks Gary and all those who have made suggestions :)

Rob Martin
Software Engineer

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

----- Original Message -----
From: Gary Benade
To: firebird-support@yahoogroups.com
Sent: Tuesday, November 23, 2004 7:12 PM
Subject: Re: [firebird-support] SQL Performance problem



SupOrdrRef is the PK of SupplyOrder and an indexed field in
> SupplyLine. The sub select runs in 15ms by itself.

Martin

how quick is this query:

SELECT so.SupOrdrRef, OrderNo, OrderDate, so.Comment
FROM SupplyOrder so
WHERE EntityRef = 8

How many rows does the above query return?
I'm thinking you may need an index on SupplyOrder(EntityRef).

Regards


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