存储过程中not in 执行太慢，怎么优化大神们
存储过程中not in 执行太慢，如何优化大神们！
SELECT @Total=COUNT(*) FROM (SELECT * from (
SELECT RegisterMachine FROM [XXAccountsDB6].[dbo].AccountsInfo WHERE RegisterMachine
not in
(SELECT * FROM (SELECT A.RegisterMachine FROM (SELECT * FROM [XXAccountsDB6].[dbo].AccountsInfo WHERE RegisterDate between @rgDate-60 and @rgDate)
AS A GROUP BY A.RegisterMachine) AS B)
and RegisterDate between @rgDate and @rgDate+1) AS A GROUP BY A.RegisterMachine) AS B
------解决思路----------------------
看看结果是否一样了。
------解决思路----------------------
额，上边多了个 From
------解决思路----------------------
------解决思路----------------------
GROUP BY A.RegisterMachine 别名给错，A改为 t1
------解决思路----------------------
------解决思路----------------------
看下执行计划，看慢在哪个地方
------解决思路----------------------
改用left join 试试，最好别用not in
------解决思路----------------------
尽量避免使用not in，特别是在数据量大的时候，因为无法使用索引，楼上的方法值得借鉴。在SQL2000里面，万不得已要用not in，用not exists来判断也比not in效率高，2008貌似没啥区别
SELECT @Total=COUNT(*) FROM (SELECT * from (
SELECT RegisterMachine FROM [XXAccountsDB6].[dbo].AccountsInfo WHERE RegisterMachine
not in
(SELECT * FROM (SELECT A.RegisterMachine FROM (SELECT * FROM [XXAccountsDB6].[dbo].AccountsInfo WHERE RegisterDate between @rgDate-60 and @rgDate)
AS A GROUP BY A.RegisterMachine) AS B)
and RegisterDate between @rgDate and @rgDate+1) AS A GROUP BY A.RegisterMachine) AS B
------解决思路----------------------
看看结果是否一样了。
SELECT @Total=COUNT(1) FROM
FROM AccountsInfo
WHERE RegisterMachine not in
(
SELECT
RegisterMachine
FROM AccountsInfo
WHERE RegisterDate between @rgDate-60 and @rgDate
)
and RegisterDate between @rgDate and @rgDate+1
GROUP BY RegisterMachine
------解决思路----------------------
额，上边多了个 From
SELECT @Total=COUNT(1)
FROM AccountsInfo
WHERE RegisterMachine not in
(
SELECT
RegisterMachine
FROM AccountsInfo
WHERE RegisterDate between @rgDate-60 and @rgDate
)
and RegisterDate between @rgDate and @rgDate+1
GROUP BY RegisterMachine
------解决思路----------------------
子查询嵌套太多，看下面的怎样：
SELECT COUNT(DISTINCT RegisterMachine)
FROM [XXAccountsDB6].[dbo].AccountsInfo t1
WHERE RegisterMachine
and RegisterDate between @rgDate and @rgDate+1
AND NOT EXISTS(
SELECT 1 FROM [XXAccountsDB6].[dbo].AccountsInfo t2
where t1.RegisterMachine=t2.RegisterMachine
and t2.RegisterDate between @rgDate-60 and @rgDate
)
GROUP BY A.RegisterMachine
最好有这个组合索引：(RegisterDate,RegisterMachine)
------解决思路----------------------
GROUP BY A.RegisterMachine 别名给错，A改为 t1
------解决思路----------------------
SELECT COUNT(DISTINCT t1.RegisterMachine)
FROM [XXAccountsDB6].[dbo].AccountsInfo t1
WHERE t1.RegisterDate between @rgDate and @rgDate+1
AND NOT EXISTS(
SELECT 1 FROM [XXAccountsDB6].[dbo].AccountsInfo t2
where t1.RegisterMachine=t2.RegisterMachine
and t2.RegisterDate between @rgDate-60 and @rgDate
)
GROUP BY t1.RegisterMachine
------解决思路----------------------
看下执行计划，看慢在哪个地方
------解决思路----------------------
SELECT @Total=COUNT(*) FROM
(
SELECT a.RegisterMachine FROM [XXAccountsDB6].[dbo].AccountsInfo A
left join (SELECT RegisterMachine FROM [XXAccountsDB6].[dbo].AccountsInfo WHERE RegisterDate between @rgDate-60 and @rgDate)b on a.RegisterMachine=b.RegisterMachine
WHERE a.RegisterDate between @rgDate and @rgDate+1 and b.RegisterMachine is null GROUP BY A.RegisterMachine
) AS B
改用left join 试试，最好别用not in
------解决思路----------------------
尽量避免使用not in，特别是在数据量大的时候，因为无法使用索引，楼上的方法值得借鉴。在SQL2000里面，万不得已要用not in，用not exists来判断也比not in效率高，2008貌似没啥区别