> 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

>

Just be aware that views don't seem to use underlying table indexes
even if they are available. We found an issue a while back where some
reports openned within seconds at our office, but onsite where there
was a lot more data, they were sluggish.

Running IBPlanalyzer, you can see that any query on a view wont take
advantage on an index on a field of that view, and so it suffers big
performance penalties with lots of data. We now use stored procedures
for a lot of things, as they can be made to use the underlying index.

Your view appears to be simple, and based on the nulls in the query,
there is no useful index that could be used for that query. You may
want to normalise the SupplyLine table and create a relationship table
between SupTran and SupplyLine, as well as a relationship table
betweem PackSlp and SupplyLine.

Good luck

Adam