Re: Possible Performance Improvement to Select Statement

Paul,

> 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)
>

Use 'NOT EXISTS' instead of 'NOT IN'.

Not exists stops searching when it finds a matching record (in theory :) ).

David Rolfe
Orinda Software
Dublin, Ireland Received on Thu Feb 03 2005 - 04:39:39 CST