> And as you expected, this is not very efficient. It takes 1.40 min
> to run while the following SP that scan the original query and skips
> unwanted records runs in 240ms !

Jerome, what about

SELECT P.ID, P.PRODUCTID, P.OUTLETID, P.VALUEDATE, P.SALEPRICE,
P.FACING
FROM PRESENCE P
WHERE
(P.OUTLETID IN (112169, 112170, 112171, 112172, 112173, 112174,
112175)) and
(P.STATUS = 2) and
not exists (
select 1 from presence p2 where P2.VALUEDATE > P.VALUEDATE and
P2.PRODUCTID = P.PRODUCTID and
(P2.OUTLETID = P.OUTLETID) and
(P2.VALUEDATE >= '2003-09-01') and
(P2.VALUEDATE < '2003-11-01') and
(P2.STATUS = 2))
)
order by P.PRODUCTID, P.OUTLETID, P.VALUEDATE

My experience with exists is that it usually executes very fast, so
I'd expect it to be considerably better than 1 min 40 sec (even though
I would be slightly surprised if it could beat 240 ms).

Set
- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation