CODEWITHSUNDEEP
sql
pratap m
pratap m

Reputation: 106

query optimization with NOT IN

We have one main table with 10000 records with 5000 records as status=completed and another 5000 records status=incompleted.

The application maintains different category of people...say users,visitors,admin..

Visitors are very less(say 200 records)...as and when they purchase something the appid in application gets updated with status=completed.

We need appid of the visitors who have status!=completed

I know that the direct way is good performance..(below one) 

select appid 
from application 
where status != completed and appid in (select appid from visitors)

appid also contains in visitors and as well as in application.. as application contains 5000 completed and 5000 incompleted

NOT IN (select appid from application where status=completed) is also same performance as with IN (select appid from application where status=incompleted)

select v.appid 
from visitors v 
where v.appid not in (select appid from application where status = completed)

Is my second query gives same performance with 1 st query..

if NOT IN execution is like this..then it is..

I am writing statement below. 

for each v.appid in visitors{ 
  do not select the v.appid if v.appid is in by firing the query as below.

    select appid 
    from application 
    where appid = v.appid and status = completed
}

will the second query fire the above procedure that i mentioned...

to provide better performance same as with 1 st query can i write the below one..

select v.appid 
from visitors v 
where v.appid not in (select appid 
                      from application 
                      where status = completed and appid = v.appid)

How can I write second query so that it performs same level as 1 st query?

Upvotes: 1

Views: 143

Answers (2)

Sunny
Sunny

Reputation: 4809

Use JOINS instead to make query more readable and efficient. First query will convert into:

select 
      a.appid 
from 
      application a INNER JOIN 
      visitors v ON a.appid = v.appid 
where 
      a.status!='completed'

With current DB servers, interms of performance, it doesn't matter much unless you are having large number of rows with many tables.

Upvotes: 0

user359040
user359040

Reputation:

Try using a NOT EXISTS condition:

select v.appid 
from visitors v 
where not exists
(select 1
 from application a
 where a.status = 'completed' and a.appid = v.appid)

Upvotes: 3

Related Questions