Hi,

> > The view was to remove the IN predicate what should speed up this query.
>
> Yes, I think it's close to my Query #3' :
>
> select distinct pres.productid
> from sys_idlists L
> inner join presence pres on(L.id = pres.outletid)
> where
> ((l.groupid = 1)
> and (pres.valuedate >= '2003-09-01')
> and (pres.valuedate < '2003-11-01'))
>
> And it's exactly as fast.

Because the distinct in the select the view with distinct was unneeded :-)
and that was what i didn't notice the first time.

> What surprises me, is that I cannot seem to find something at least as

fast

> as the "explicit IN" version. And I just noticed that if you have two IN
> predicates then the "explicit IN" is always faster, w/o distinct.
>
> SELECT PRES.PRODUCTID
> FROM PRESENCE PRES
> WHERE
> PRES.OUTLETID IN (112191, 112193, 112194, 112200, 112201, 112205)
> and PRES.VALUEDATE >= '2003-09-01'
> and PRES.VALUEDATE < '2003-11-01'
> and PRES.STATUS IN (1,2,3)
>
> --> 50ms
> --> 330ms with distinct
> --> 80ms with Group by !!!

Are you doing a fetchall when measuring? The difference between distinct and
GROUP BY is not clear to me here.
Could you also provide the PLANs from the 3 options above.

> select pres.productid
> from sys_idlists L1
> inner join presence pres on L1.id = pres.outletid
> inner join sys_idlists L2 on pres.status = L2.id
> where
> L1.groupid = 1 and L2.groupid = 2
> and pres.valuedate >= '2003-09-01'
> and pres.valuedate < '2003-11-01'
>
> --> 481ms
> --> 590ms with distinct
> --> 590ms with group by
>
> Again, sys_idlists contains only the 9 required records.

Also show us the PLANs.

> I can't believe there is no way to achieve the "explicit IN" performance
> with a JOIN, a sub-select or anything else.

A JOIN or sub-select needs always disk reads and there's no way to compare
that with hard given constants.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81