Re: Poor performance in EXCEPT?

On Sun, 24 Sep 2000, Stephan Szabo wrote:

> On Mon, 25 Sep 2000, Ben Leslie wrote:
>
> > I'm just having a bit of a problem using an sql query with an except
> > clause in it.
> >
> > I have a squery, which I am sure will work, however it takes an
> > excessive amount of time (cancelled at ~10-15minutes).
> >
> > The query is:
> >
> > bomond=> select distinct itemid
> >      from purchase p, orders o
> >      where  p.orderid = o.id and date(o.date) > '30/6/00'
> >      except
> >      select id from item;
>
> This might be equivalent and faster (basically untested)
>
> select distinct itemid
> from purchase p, orders o
> where p.orderid=o.id and date(o.date) > '30/6/00'
> and not exists (select item.id from item where item.id=itemid);
>
> There are some cases with IN (which exists is effectively done as
> internally IIRC) where the performance can be poor.


Thankyou very much, that works perfectly.

Regards,

Ben Leslie