SQL NOT IN, possibly performance issues

Question

I'm trying to reorganize some old code snippets ... I edited the current snippet below and highlighted the NOT IN statement, which caused performance problems. I am trying to rewrite the NOT IN section using a left outer join.

Can someone 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 

Answer 1

You have column concatenation that prevents any use of indexes

Try NOT EXIST, which will support two 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 indices or course ...

This is a good use of EXCEPT (ORDER BY ends as UNION: thanks @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