select OrgId as 公司編碼,OrgName as 公司名稱 from Organise where OrgLev=2 and item_id not in (select OrgidS from WagesPerMonthHis where WagesYear='2010' and WagesMonth= '01' Group by OrgidS,OrgNameS) order by Orgid
語句執(zhí)行要33秒之久,服務器的配置是比較高的:16核心4CPU,24G內存,且內存和CPU在執(zhí)行時都沒有出現(xiàn)瓶頸,開始以為是 (select OrgidS from WagesPerMonthHis where WagesYear='2010' and WagesMonth= '01' Group by OrgidS,OrgNameS) 這條語句執(zhí)行緩慢所致,單獨執(zhí)行這條卻發(fā)現(xiàn)執(zhí)行速度很快,大約不到2秒就出來了,于是癥結出來了,是not in 這個全掃描關鍵詞帶來的性能下降.最直接的是導致頁面失去響應,一個關鍵功能使用不了.
select a.OrgId as 公司編碼,a.OrgName as 公司名稱,a.item_id from Organise a left outer join (select distinct b.OrgIdS from WagesPerMonthHis b where WagesYear='2010' and WagesMonth='01') as b on a.item_id = b.OrgidS where a.OrgLev = 2 and b.OrgIdS is Null order by 公司編碼
