not exists 中from 後面不同寫法帶來的效率區別
Windows 2003 , SQL Server 2000 SP4 .
下面的語句執行非常慢, 在後面加入option(maxdop 1) 後變得快了很多 。 後來我們又做了測試,嘗試將別名為b的table 顯示的寫在
not exists 後面的子查詢中, 速度也從20多秒減低到1秒以內 。 當然執行計劃也發生了變化 。 具體原因是什麼 ?
SELECT '004' AS MSGFN,'OB20031120' AS AENNR,b.climat AS IDNRK,'L' AS POSTP,CONVERT(INT,b.SOITMNUM) AS POSNR,
b.ordqty AS MENGE_C ,'X' AS SANFE
FROM purchaseordersitem a (nolock) , purchaseordersitemdetail b (nolock)
WHERE a.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
AND a.ordnum='20058439390' and a.itmnum='1' AND a.mesgid= b.mesgid
AND a.ordnum=b.ordnum and a.itmnum=b.itmnum and a.highlevel IS NOT NULL
AND not exists
(select * from purchaseorderscomponent(nolock)
where climat=b.climat
and rdnum='20058439390'
and mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
and itmnum='1'
and ( (ATTRIBNAME='InSystemBox'and ATTRIBVALUE='N')
or (ATTRIBNAME='PartType'and ATTRIBVALUE='OVERPACK')
)
)
ORDER BY POSNR ;
SELECT '004' AS MSGFN,'OB20031120' AS AENNR,b.climat AS IDNRK,'L' AS POSTP,CONVERT(INT,b.SOITMNUM) AS POSNR,
b.ordqty AS MENGE_C ,'X' AS SANFE
FROM purchaseordersitem a (nolock) , purchaseordersitemdetail b (nolock)
WHERE a.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
AND a.ordnum='20058439390' and a.itmnum='1' AND a.mesgid= b.mesgid
AND a.ordnum=b.ordnum and a.itmnum=b.itmnum and a.highlevel IS NOT NULL
AND not exists
(select * from purchaseorderscomponent c(nolock), purchaseordersitemdetail b (nolock)
where c.climat=b.climat
and c.ordnum='20058439390'
and c.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
and c.itmnum='1'
and ((c.ATTRIBNAME='InSystemBox'and c.ATTRIBVALUE='N')
or (c.ATTRIBNAME='PartType'and c.ATTRIBVALUE='OVERPACK')
)
)
ORDER BY POSNR ;
下面的語句執行非常慢, 在後面加入option(maxdop 1) 後變得快了很多 。 後來我們又做了測試,嘗試將別名為b的table 顯示的寫在
not exists 後面的子查詢中, 速度也從20多秒減低到1秒以內 。 當然執行計劃也發生了變化 。 具體原因是什麼 ?
SELECT '004' AS MSGFN,'OB20031120' AS AENNR,b.climat AS IDNRK,'L' AS POSTP,CONVERT(INT,b.SOITMNUM) AS POSNR,
b.ordqty AS MENGE_C ,'X' AS SANFE
FROM purchaseordersitem a (nolock) , purchaseordersitemdetail b (nolock)
WHERE a.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
AND a.ordnum='20058439390' and a.itmnum='1' AND a.mesgid= b.mesgid
AND a.ordnum=b.ordnum and a.itmnum=b.itmnum and a.highlevel IS NOT NULL
AND not exists
(select * from purchaseorderscomponent(nolock)
where climat=b.climat
and rdnum='20058439390'
and mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
and itmnum='1'
and ( (ATTRIBNAME='InSystemBox'and ATTRIBVALUE='N')
or (ATTRIBNAME='PartType'and ATTRIBVALUE='OVERPACK')
)
)
ORDER BY POSNR ;
SELECT '004' AS MSGFN,'OB20031120' AS AENNR,b.climat AS IDNRK,'L' AS POSTP,CONVERT(INT,b.SOITMNUM) AS POSNR,
b.ordqty AS MENGE_C ,'X' AS SANFE
FROM purchaseordersitem a (nolock) , purchaseordersitemdetail b (nolock)
WHERE a.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
AND a.ordnum='20058439390' and a.itmnum='1' AND a.mesgid= b.mesgid
AND a.ordnum=b.ordnum and a.itmnum=b.itmnum and a.highlevel IS NOT NULL
AND not exists
(select * from purchaseorderscomponent c(nolock), purchaseordersitemdetail b (nolock)
where c.climat=b.climat
and c.ordnum='20058439390'
and c.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
and c.itmnum='1'
and ((c.ATTRIBNAME='InSystemBox'and c.ATTRIBVALUE='N')
or (c.ATTRIBNAME='PartType'and c.ATTRIBVALUE='OVERPACK')
)
)
ORDER BY POSNR ;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-681952/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle中in和exists的區別Oracle
- EXISTS、IN、NOT EXISTS、NOT IN的區別(ZT)
- EXISTS、IN、NOT EXISTS、NOT IN用法區別
- (轉)ORACLE 中IN和EXISTS的區別Oracle
- SQL中IN和EXISTS用法的區別SQL
- [精選] SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析SQL
- in 和 exists區別
- by which, in which, from which 語法區別
- NOT IN 與NOT EXISTS的區別何在?
- oracle in與exists 的區別Oracle
- exists和not exists及in和not in的用法與區別
- 子查詢中的IN與EXISTS的區別(轉)
- 對線面試官:SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析面試SQL
- SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差別SQL
- in/exists和not in/not exists執行效率
- include 後面加和" "的區別
- Oracle環境下SQL語句的不同寫法效率對比OracleSQL
- 在關聯子查詢中in與exists的區別
- MySQL過程慎用if not exists寫法MySql
- SQL語句中exists和in的區別SQL
- 大神級回答exists與in的區別
- in和exists的一些區別
- JS 中函式名後面加與不加括號的區別JS函式
- MySQL過程真要慎用if not exists寫法MySql
- 詳解not in與not exists的區別與用法
- mysql 左連結 left join 條件寫在where 後面與 on後面的區別MySql
- 淺談Oracle中exists與in的執行效率問題Oracle
- MySQL exists 優化 in 效率MySql優化
- MYSQL 中 exists 語句執行效率變低MySql
- NOT IN ,NOT EXISTS 區別 11G改變
- MySQL語法中=與:=的區別MySql
- 不同shutdown命令的區別
- from module import 和 import 的區別Import
- CSS中多個class樣式設定的不同寫法CSS
- NOT IN、JOIN、IS NULL、NOT EXISTS效率對比Null
- 不同於Oracle:SEQUENCE的區別Oracle
- import tkinter與from tkinter import *的區別Import
- oracle sql tuning_in與exists的區別_轉摘OracleSQL