SQL NOT IN possibly performance issues

Question

I am attempting to refactor several old pieces of code... I have refactored the current piece below and have highlighted the NOT IN statement causing performance issues. I am attempting to rewrite the NOT IN section with a left outer join.

Can anyone help, or suggest a better way if possible?

SELECT 
    left(unique_id,16) AS casino_id , 
    right(unique_id,24) AS game_id   
FROM (  
        SELECT 
            distinct o.casino_id + g.game_id AS unique_id      
        FROM 
            game g INNER JOIN Bet b
            ON g.game_id = b.game_id
            INNER JOIN CasinoUser u 
            ON b.user_id = u.user_id
            INNER JOIN onewalletcasino o  
            ON u.casino_id = o.casino_id
        WHERE 
            game_start between dateadd(mi, -180, getdate()) 
            and dateadd(mi, -5, getdate())  
            and b.[status] <> 'P'
     ) t 
WHERE  
   unique_id NOT in  
    ( SELECT casino_id + game_id  AS casino_id 
      FROM 
        thirdpartysettlecalled  
      WHERE 
        [status] = 'Y')
ORDER BY casino_id

On the last line I am guessing that [Status] can also be 'N' so simply take out the 'NOT' and replace 'Y' with 'N' thus eliminating the NOT ? — Stuart Blackler, Jul 21 '11 at 09:57
i have been replying to answers,voting up a couple but my rating has not appeared, think I was not completing it correctly but I was taking time to thanks people.. maybe u have a little too much time to write these comments and provide no answer! — Chris Wood, Jul 21 '11 at 10:17
Insulting the people that would help you is not the way to go. — Jacob, Jul 21 '11 at 10:19
@SBlacker, thanks, this seems to be have been staring me in the face... the problem is the table is constantly being updated so results always changing, im going to script and test on static data. If you submit this as an answer, I will accept it.. cheers.. Others, take it easy on new users — Chris Wood, Jul 21 '11 at 10:27
@Chris Wood: we chimps can exchange our Stackoverflow points for food pellets or sexual favors. — Tim, Jul 21 '11 at 10:52
How many rows does this return: SELECT casino_id + game_id AS casino_id FROM thirdpartysettlecalled WHERE [status] = 'Y' — Tim, Jul 21 '11 at 10:55
@Chris Wood: maybe you're not interested in the collaborative/competitive spirit of this website, but SO is not free outsourcing — ascanio, Jul 21 '11 at 12:00

gbn · Accepted Answer · 2011-07-21T13:08:54.527

You have a column concatenation which prevents any use of indexes

Try NOT EXISTS which will support the 2 columns separately

SELECT distinct
     o.casino_id, g.game_id
FROM 
    game g 
    INNER JOIN 
    Bet b ON g.game_id = b.game_id
    INNER JOIN
    CasinoUser u ON b.user_id = u.user_id
    INNER JOIN
    onewalletcasino o  ON u.casino_id = o.casino_id
WHERE 
    game_start between dateadd(mi, -180, getdate()) 
                       and dateadd(mi, -5, getdate())  
    and
    b.[status] <> 'P'
    AND
    NOT EXISTS (SELECT *
           FROM 
              thirdpartysettlecalled   tp
           WHERE 
              tp.[status] = 'Y'
              AND
              tp.casino_id = o.casino_id AND tp.game_id = g.game_id)
ORDER BY
    casino_id

After that, check your indexes or course...

This is a good use of EXCEPT too (ORDER BY goes at end like UNION: thanks to @Damien_The_Unbeliever)

SELECT distinct
     o.casino_id, g.game_id
FROM 
    game g 
    INNER JOIN 
    Bet b ON g.game_id = b.game_id
    INNER JOIN
    CasinoUser u ON b.user_id = u.user_id
    INNER JOIN
    onewalletcasino o  ON u.casino_id = o.casino_id
WHERE 
    game_start between dateadd(mi, -180, getdate()) 
                       and dateadd(mi, -5, getdate())  
    and
    b.[status] <> 'P'

EXCEPT
SELECT tp.casino_id, tp.game_id
FROM thirdpartysettlecalled   tp
WHERE tp.[status] = 'Y'

ORDER BY
    casino_id

Doesn't NOT logic still invalidate the use of indexes with the exist statement? or is that only in the where statement? — Stuart Blackler, Jul 21 '11 at 11:03
Related DBA question: http://dba.stackexchange.com/questions/4009/the-use-of-not-logic-in-relation-to-indexes — Stuart Blackler, Jul 21 '11 at 11:52
(EXCEPT) - ORDER BY goes at the end of the whole thing, as with UNION — Damien_The_Unbeliever, Jul 21 '11 at 13:01

SQL NOT IN possibly performance issues

1 Answers1

Linked