SQL NOT IN 可能存在性能问题 [英] SQL NOT IN possibly performance issues

问题描述

我正在尝试重构几段旧代码...我重构了下面的当前代码段，并突出显示了导致性能问题的 NOT IN 语句.我试图用左外连接重写 NOT IN 部分.

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 

推荐答案

你有一个列连接，可以防止任何索引的使用

You have a column concatenation which prevents any use of indexes

尝试 NOT EXISTS，它将分别支持 2 列

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...

这也是 EXCEPT 的一个很好的用法(ORDER BY最后像 UNION:感谢@Damien_The_Unbeliever)

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

这篇关于SQL NOT IN 可能存在性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！