sql not in如何改成join？

[随风201333]

有如下语句， position 几百万数据， pm几亿条数据， 下面语句执行非常慢，几十分钟，但是也用了index seek：
select distinct portfolio_id from [position] where [id] not in (select distinct positionid from pm)
返回 151779 行
如何改成Join？ 我试了试：
select distinct portfolio_id from (select portfolio_id from [position]) t1 left join (
select positionid from pm) t2 on t2.positionid = t1.portfolio_id
where t2.positionid is null
返回 15790 行，不对。
NOT exists 的方法我用了，效果不大，想试试join

[LongRui888]

这样：

select distinct portfolio_id 
from [position] 
left join pm
        on position.[id]= pm.positionid
where pm.positionid is null

[LongRui888]

如果慢可以修改成这样试试： select distinct portfolio_id from [position] left hash join pm on position.[id]= pm.positionid where pm.positionid is null

[LongRui888]

如果还是慢，可以考虑在pm表的positionid字段上建立索引，比如： create index idx_pm_positionid on pm(positionid)

[随风201333]

非常感谢， 发现原来的语句用的nested loop ，第一次很慢比如8分钟，以后再跑都几秒内完成，能够用缓存。 而hash 的比nested loop 快很多， 2分钟。 但是以后再跑，还是2分钟左右。 难道他不能使用缓存？ 另外，positionID 已有index。

[LongRui888]

引用 4 楼 iamsea22 的回复:

非常感谢， 发现原来的语句用的nested loop ，第一次很慢比如8分钟，以后再跑都几秒内完成，能够用缓存。 而hash 的比nested loop 快很多， 2分钟。 但是以后再跑，还是2分钟左右。 难道他不能使用缓存？ 另外，positionID 已有index。

哦，那就用第一种的把，别加hash了，这个hash之所以慢，是因为，他需要把你的那个大的表中的positionID ，全部放到内存，建立hash表，等运行完成后，就清除掉了。 而nested loop，第一次慢，排除是阻塞的话，应该是加载那个pm表的索引的时候慢，等加载了索引后，下次运行就快乐