Post reply

How to avoid NOT IN?

  • 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

  • February 20, 2012 at 3:08 am

    #1449144

    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

  • February 20, 2012 at 3:23 am

    #1449152

    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.

  • February 20, 2012 at 3:50 am

    #1449161

    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

  • February 20, 2012 at 4:00 am

    #1449168

    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.

  • February 20, 2012 at 4:31 am

    #1449174

    Gail,

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

  • February 20, 2012 at 5:00 am

    #1449183

    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

  • February 20, 2012 at 5:07 am

    #1449187

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

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

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