Hi Helen

Thanks for your reed back.

I realized the * was not so hot, the real query specifies all fields and
actually contains a couple of other tables. The query shown below more
simply demonstrates the problem. Your suggestion re using the = is a
good one however I had already tested it to no avail. Interestingly
adding the sl.* actually makes the SQL about 100ms slower !

My further testing of other where clauses that would return the correct
208 records indicates that all are equally slow. The second I flip the
SQL around it runs fast, but of course returns the wrong results!

for example your query

SELECT sl.* /* notice the identifier! */
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
WHERE so.EntityRef = 4
AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND (sl.CancelFlag = 'F' or sl.CancelFlag is null)

Returns 208 records and takes 1.568ms

The same query with the F changed to T

SELECT sl.* /* notice the identifier! */
FROM SupplyLine sl
JOIN SupplyOrder so ON so.SupOrdrRef = sl.SupOrdrRef
WHERE so.EntityRef = 4
AND sl.SupTranRef IS NULL
AND sl.PackSlpRef IS NULL
AND (sl.CancelFlag = 'T' or sl.CancelFlag is null)

returns 334 records and takes 0.125s (note it takes 0.079s with just * instead of sl.*).

This is all very strange :)

>
>