Re: Possible Performance Improvement to Select Statement

"Paul Izzo" <paul.izzo_at_mosca-ag.com> 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)

This probably won't be faster than the left join or not exists, but it may be worth a shot:

select 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 group by pkvk.CUST_JOB_NR
having min(positions_status)=9 and max(positions_status)=9;

Xho

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