我正在尝试重构几段旧代码...我已经重构了下面的当前代码并突出显示了NOT IN
语句导致性能问题。我正在尝试重写NOT IN
具有左外连接的部分。
如果可能的话,任何人都可以帮忙,或者建议更好的方法吗?
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
您有一个列串联,它阻止了索引的任何使用
尝试 NOT EXISTS ,它将分别支持 2 列
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
之后,检查您的索引或课程...
这是一个很好的利用EXCEPT http://msdn.microsoft.com/en-us/library/ms188055.aspx也是(ORDER BY 像 UNION 一样放在最后:感谢@Damien_The_Unknowner)
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)