How to avoid NOT IN?

Question

How to avoid NOT IN?

snigdhandream

Mr or Mrs. 500

Points: 558
More actions
February 20, 2012 at 3:04 am

#268578

Hi,
I have one view which is based on the below select query.
select t1.col1, t1.clo2, t1.col3...
from table1 t1, table2 t2
where t1.col4 = t2.col4
and t1.col5 NOT IN ('STRING1', 'STRING2')
My client wants me to avoid the NOT IN clause in this query for performance issues. I was thinking to make it an 'IN' clause, but there are some 12/13 values that need to be in the bracket if we use the IN clause. t1.col5 does not have any index on it. How do i avoid using this NOT IN clause? Any feasible way? Somebody suggested me to use 'NOT EXISTS' clause. But there is no subquery here, rather 2 harcoded string values are used. Cn we make the NOT EXISTS work in some way in this situation? Please suggest.
Regards,
Snigdha

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

And you've tested and confirmed that NOT IN does indeed have performance problems?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

snigdhandream Mr or Mrs. 500 Points: 558 More actions · Answer 2

What I checked from the execution plan in the DEV environment is that, it is taking a clustered index scan for the NOT IN operation. whose cost is 27%. There is a hint to create an index on col5... but this column has only 14/15 distinct values where the tables has more than 7000 rows. So creating an index on that won't work.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

So you've tried creating the suggested index and know (not guess) that it won't have any effect? You've tried an IN instead and seen that it's faster?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

cengland0 SSCertifiable Points: 6102 More actions · Answer 4

What would happen to your performance if you created a table that contained the 12 to 15 strings you want to include. Then, do a simple join on that table.

snigdhandream Mr or Mrs. 500 Points: 558 More actions · Answer 5

Gail,

I tried everything, but nothing is faster or slower... everything is taking equal ms.. some times +/- 2 or 3 ms...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

snigdhandream (2/20/2012)
I tried everything, but nothing is faster or slower... everything is taking equal ms.. some times +/- 2 or 3 ms...

Exactly, so stop worrying about NOT IN and spend the time of stuff that does matter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 7

For a start, I'd convert your query to use ANSI joins. It probably doesn't make a difference for such a simple query, but I suspect your real world example is more complex and I've seen some bad plans come out of these old school joins in SQL Server. E.g. it should look like:

select t1.col1, t1.clo2, t1.col3...

from table1 t1

INNER JOIN table2 t2 ON t1.col4 = t2.col4

WHERE t1.col5 NOT IN ('STRING1', 'STRING2')