Best way to rewrite a WHERE NOT IN clause?

Question

I've got a query like the one below:

Select ser.key
From dbo.Enrlmt ser     
Where ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND
      ser.EnrlmtStatCode = '4321' AND
      ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND
      ser.Key not in (select Key 
                           from Enrlmt ser2 
                          where ser2.StartDate <= @AsOfDate 
                            AND ser2.StartDate > ser.wd 
                            AND ser2.EnrlmtStatCode = '4321')

That is extremely slow due to the "NOT IN" clause. I tried rewriting it using a left outer join so that it looked like:

   Select ser.key
     From dbo.Enrlmt ser    
LEFT JOIN dbo.Enrlmt ser2 ON ser.key = ser2.key
                         AND ser2.StartDate <= @AsOfDate 
                         AND ser2.StartDate > ser.wd 
                         AND ser2.EnrlmtStatCode = '4321'
   Where ser2.key is null
     AND ser.wd >= @FromDate 
     AND ser.wd <= @ThrouDate 
     AND ser.EnrlmtStatCode = '4321' 
     AND ser.StuExitCatgCode in ('1','2','3','4','5','6','7')

Which is much faster but the results don't match up. Am i doing something wrong with this rewrite? Is there a better way to do this?

Answer 1

Maybe it's a typo, but in the first query, you are comparing the StuKey column whereas in the second query you are joining on Key

Performance-wise, I'd expect the 2 queries to result in a very similar, if not the same, execution plan. You should check the plans of both to see.

Also, make sure you clear the data cache between runs, as they could actually be performing the same, but due to caching of data, the 2nd appears to be faster.

Answer 2

I think the problem is here:

ser.StuKey not in (select StuKey

as compared to:

ON ser.key = ser2.key

So the rewrite should be:

SELECT      ser.key
FROM        dbo.Enrlmt  ser
LEFT JOIN   Enrlmt      ser2
ON          ser.StuKey         = ser2.Stukey
AND         ser.EnrlmtStatCode = ser2.EnrlmtStatCode
AND         ser2.StartDate     > ser.wd
AND         ser2.StartDate     <= @AsOfDate
WHERE       ser.wd             >= @FromDate 
AND         ser.wd             <= @ThrouDate
AND         ser.EnrlmtStatCode = '4321'
AND         ser.StuExitCatgCode in ('1','2','3','4','5','6','7')
AND         ser2.key IS NULL

(note I also ucut out the ser2.EnrlmtStatCode = '4321' and rewrote it to a column comparison)

Answer 3

Have you tried NOT EXISTS bringing in ser.StuKey to the NOT EXISTS query?

Answer 4

This will be a good balance between efficiency and clarity:

    Select ser.key
From
    dbo.Enrlmt ser
    Left Join (select StuKey 
            from Enrlmt 
            where Enrlmt.StartDate <= @AsOfDate AND 
            Enrlmt.EnrlmtStatCode = '4321') As ser2
    ON ser.key = ser2.key And ser2.StartDate > ser.wd
Where
        ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND
        ser.EnrlmtStatCode = '4321' AND
        ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND
        ser2.key Is Null

You might eek out more speed by making the subquery a UDF. For large amounts of records where the subquery is doing a lot of work, consider putting this all in a UDF or procedure and filling a temporary table with the results of the subquery, using that table in the main query, and then clean up by erasing the temporary table.

Answer 5

Try this

• NOT EXISTS
• Perhaps no need to correlate ser2.StartDate > ser.wd because ser.wd >= @FromDate?

Also:

• If you have to use ser2.StartDate > ser.wd, are they the same datatype?
• Do all datatypes line up? eg: @FromDate, @ThrouDate, StartDate, wd etc?

thus:

Select
    ser.key
From
    dbo.Enrlmt ser     
Where
    ser.wd >= @FromDate AND ser.wd <= @ThrouDate AND
    ser.EnrlmtStatCode = '4321' AND
    ser.StuExitCatgCode in ('1','2','3','4','5','6','7') AND
    not EXISTS (select *
                           from Enrlmt ser2 
                          where ser2.StartDate <= @AsOfDate 
                            AND ser2.EnrlmtStatCode = '4321'
                            AND ser2.StartDate > @FromDate --ser.wd??
                            AND ser2.Key = ser.Key)

Answer 6

+1 for Patrick Karcher's solution