Re: Possible Performance Improvement to Select Statement

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:
> Paul Izzo wrote:
> > The following select statement works for me but it takes a lot of
> > process time and is rather slow. I'd like to know if there's another
> > way of doing the same query only faster and less process time
> > intensive.
> >
> > The following script contains 2 queries that use 2 tables. One query
> > is used only to exclude a certain range of data.
> >
> > select distinct pkvk.CUST_JOB_NR
> > from pkvk,pkvp
> > where pkvk.JOB_STATUS != 9
> > and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
> > and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
> > and pkvk.CUST_JOB_NR
> >
> > not in (select distinct pkvk.CUST_JOB_NR
> > from pkvp,pkvk
> > where pkvk.JOB_STATUS != 9
> > and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
> > and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
> > and pkvp.POSITIONS_STATUS != 9)
>
> Isn't this equivalent to
>
> select distinct pkvk.CUST_JOB_NR
> from pkvk,pkvp
> where pkvk.JOB_STATUS != 9
> and pkvp.CUST_JOB_NR = pkvk.CUST_JOB_NR
> and pkvp.CUST_JOB_TYPE = pkvk.CUST_JOB_TYPE
> and pkvp.POSITIONS_STATUS = 9;

No. If the same CUST_JOB_NR value can have one row where POSITIONS_STATUS = 9 and another one where POSITIONS_STATUS != 9, then the results are not the same.

> i.e only include records that match to one with a positions_status of 9
> rather than exclude ones that don't?
>
> Even if it isn't you don't need the distinct in the not in query (so
> you can possibly avoid a sort) and as others have said not exists might
> well be faster.

Or try a "minus".

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB