not exists 中from 后面不同写法带来的效率区别

最新推荐文章于 2023-02-08 23:03:19 发布

转载最新推荐文章于 2023-02-08 23:03:19 发布 · 102 阅读

本文通过一个具体的SQL查询案例展示了如何通过调整查询方式和使用T-SQL提示来显著提高查询效率。通过对NOT EXISTS子句的不同实现方式对比，说明了明确指定连接条件可以改善执行计划并减少查询时间。

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 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-681952/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-681952/