转载

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

开发人员遇到一个及其诡异的的SQL性能问题，这段完整SQL语句如下所示：

declare @UserId             INT

declare @PSANo              VARCHAR(200)

declare @ShipMode           VARCHAR(10)

declare @CY_FLAG            VARCHAR(1)

declare @PO                 VARCHAR(20)

declare @BuyerName          VARCHAR(100)

declare @Destination        VARCHAR(1)

declare @FinalDestination   VARCHAR(40)

declare @Factory            VARCHAR(10)

declare @NoticeDateStart    DATETIME

declare @NoticeDateEnd      DATETIME

declare @EELForwarder       VARCHAR(100)

declare @SortExpression     VARCHAR(100)

declare @RowIndex           INT

declare @PageSize           INT

declare @ExistNoticeKey         varchar(200)

DECLARE @NULLDATE DATETIME

SET @NULLDATE=GETDATE()

set @UserId=39

set @PSANo=''

set @ShipMode=''

set @CY_FLAG=''

set @PO=N''

set @BuyerName=N''

set @Destination=N''

set @FinalDestination=N''

set @Factory=''

set @EELForwarder=N''

set @SortExpression=''

set @RowIndex=0

set @PageSize=10

set @ExistNoticeKey=''

    DECLARE @CountSql NVARCHAR(max)

    DECLARE @DataSql NVARCHAR(max)

    declare @next int

    declare @Where_PSANo varchar(400)

    declare @Index_PSANo varchar(40)

    declare @Where_ExcludeNotcekey varchar(400)

    set @Where_PSANo=''

    SET NOCOUNT ON;

    set @next=1

    while @next<=dbo.Get_StrArrayLength(@PSANo,',')

    begin

       set @Index_PSANo = dbo.Get_StrArrayStrOfIndex(@PSANo,',',@next)

       set @Where_PSANo = @Where_PSANo + ' Or notice.PSA_NO LIKE ''%'+@Index_PSANo+'%'''

       set @next=@next+1

end

    set @Where_ExcludeNotcekey=''

    if @ExistNoticeKey!=''

    begin

        set @Where_ExcludeNotcekey=' or notice.NOTICE_KEY not in('+ @ExistNoticeKey+')';

        --select @Where_ExcludePSANo

        --print 'OK'

end

SELECT SUM(ISNULL(FactQty,0)) AS FactQty, NOTICE_KEY INTO #TEMP

FROM

    SELECT  A.NOTICE_KEY,SUM(ISNULL(A.FactQty,0)) FactQty  FROM IES.InvoiceFourLine A GROUP BY A.NOTICE_KEY

    UNION ALL

    SELECT A.NoticeKey AS NOTICE_KEY,SUM(ISNULL(A.FactQty,0)) FactQty FROM IES.InvoiceThreeByrFwdChargeLine A GROUP BY A.NoticeKey

) T GROUP BY NOTICE_KEY

SELECT COUNT(*)

FROM IES.ExportNotice notice --WITH (INDEX(PK_EXPORTNOTICE))

LEFT  JOIN #TEMP t ON notice.NOTICE_KEY = T.NOTICE_KEY

WHERE

notice.FACTORY_CD IN(SELECT SiteId FROM DCL.SecurityUserSiteMapping WHERE UserId=39)

AND (ISNULL(notice.FACT_EXPORT_QTY,0)-ISNULL(T.FactQty,0))>0

AND (ISNULL(@PSANo,'')=''  Or notice.PSA_NO LIKE '%%')

AND (ISNULL(@ExistNoticeKey,'')='' )

AND (ISNULL(@ShipMode,'')='' OR  notice.SHIP_MODE_CD=@ShipMode)

AND (ISNULL(@CY_FLAG,'')='' OR notice.CY_FLAG=@CY_FLAG)

AND (ISNULL(@PO,'')='' OR notice.BUYER_PO_NO LIKE '%'+@PO+'%')

AND (ISNULL(@BuyerName,'')='' OR notice.NAME LIKE '%'+@BuyerName+'%')

AND (ISNULL(@Destination,'')='' OR notice.SZ=@Destination)

AND (ISNULL(@FinalDestination,'')='' OR notice.FINAL_DESTINATION LIKE '%'+@FinalDestination+'%')

AND (ISNULL(@Factory,'')='' OR notice.FACTORY_CD=@Factory)

AND (ISNULL(@EELForwarder,'')='' OR notice.EEL_FORWARDER=@EELForwarder)

AND (ISNULL(@NoticeDateStart,'2000-01-01')='2000-01-01')

---AND ( ISNULL(@NoticeDateEnd,'1999-01-01')='1999-01-01')

DROP TABLE #TEMP

案例的环境为SQL SERVER 2012 Standard Edition (64-bit)，具体版本号为11.0.5058.0 ，另外表IES.ExportNotice的数据记录为2万多。表IES.InvoiceThreeByrFwdChargeLine的记录数为1万多，表IES.InvoiceFourLine的记录只有区区几十条。临时表 #TEMP的记录为1万多条。

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

执行上面SQL语句一般一秒以内完成。但是这段SQL如果将最后注释的条件加上（也就是最后注释的语句取消注释）

SELECT COUNT(*)

FROM IES.ExportNotice notice --WITH (INDEX(PK_EXPORTNOTICE))

LEFT  JOIN #TEMP t ON notice.NOTICE_KEY = T.NOTICE_KEY

WHERE

notice.FACTORY_CD IN(SELECT SiteId FROM DCL.SecurityUserSiteMapping WHERE UserId=39)

AND (ISNULL(notice.FACT_EXPORT_QTY,0)-ISNULL(T.FactQty,0))>0

AND (ISNULL(@PSANo,'')=''  Or notice.PSA_NO LIKE '%%')

AND (ISNULL(@ExistNoticeKey,'')='' )

AND (ISNULL(@ShipMode,'')='' OR  notice.SHIP_MODE_CD=@ShipMode)

AND (ISNULL(@CY_FLAG,'')='' OR notice.CY_FLAG=@CY_FLAG)

AND (ISNULL(@PO,'')='' OR notice.BUYER_PO_NO LIKE '%'+@PO+'%')

AND (ISNULL(@BuyerName,'')='' OR notice.NAME LIKE '%'+@BuyerName+'%')

AND (ISNULL(@Destination,'')='' OR notice.SZ=@Destination)

AND (ISNULL(@FinalDestination,'')='' OR notice.FINAL_DESTINATION LIKE '%'+@FinalDestination+'%')

AND (ISNULL(@Factory,'')='' OR notice.FACTORY_CD=@Factory)

AND (ISNULL(@EELForwarder,'')='' OR notice.EEL_FORWARDER=@EELForwarder)

AND (ISNULL(@NoticeDateStart,'2000-01-01')='2000-01-01')

AND ( ISNULL(@NoticeDateEnd,'1999-01-01')='1999-01-01')

然后执行时发现SQL慢得令人发指，非常的不可以思议。如果按照我们理解，这个条件 ( ISNULL(@NoticeDateEnd,'1999-01-01')='1999-01-01') 仅仅相当于一个 1=1 或1=0的条件，怎么会有如此大的性能差距呢？查看执行计划后，发现加上这样一个条件后，执行计划完全不同了。

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

我姑且将执行性能较好的SQL的执行计划叫做Plan A，执行性能很差的SQL的执行计划叫做Plan B

Plan A

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

Plan B

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

如上所示，Plan B 看似开销都耗费在键查找那一块，但是如果查看具体信息（如下所示），并无特别地方。

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

于是我使用HINT，强制在表IES.ExportNotice上走索引PK_EXPORTNOTICE，结果发现执行时，执行速度依然慢的令人发指。我觉得执行计划有些问题，Cost可能并不正确。

SELECT COUNT(*)

FROM IES.ExportNotice notice WITH (INDEX(PK_EXPORTNOTICE))

LEFT  JOIN #TEMP t ON notice.NOTICE_KEY = T.NOTICE_KEY

WHERE

notice.FACTORY_CD IN(SELECT SiteId FROM DCL.SecurityUserSiteMapping WHERE UserId=39)

AND (ISNULL(notice.FACT_EXPORT_QTY,0)-ISNULL(T.FactQty,0))>0

AND (ISNULL(@PSANo,'')=''  Or notice.PSA_NO LIKE '%%')

AND (ISNULL(@ExistNoticeKey,'')='' )

AND (ISNULL(@ShipMode,'')='' OR  notice.SHIP_MODE_CD=@ShipMode)

AND (ISNULL(@CY_FLAG,'')='' OR notice.CY_FLAG=@CY_FLAG)

AND (ISNULL(@PO,'')='' OR notice.BUYER_PO_NO LIKE '%'+@PO+'%')

AND (ISNULL(@BuyerName,'')='' OR notice.NAME LIKE '%'+@BuyerName+'%')

AND (ISNULL(@Destination,'')='' OR notice.SZ=@Destination)

AND (ISNULL(@FinalDestination,'')='' OR notice.FINAL_DESTINATION LIKE '%'+@FinalDestination+'%')

AND (ISNULL(@Factory,'')='' OR notice.FACTORY_CD=@Factory)

AND (ISNULL(@EELForwarder,'')='' OR notice.EEL_FORWARDER=@EELForwarder)

AND (ISNULL(@NoticeDateStart,'2000-01-01')='2000-01-01')

AND ( ISNULL(@NoticeDateEnd,'1999-01-01')='1999-01-01')

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

于是我将怀疑的地方转移到表连接方式，使用Table HINT，强制下面SQL语句走HASH JOIN，结果SQL一秒钟执行完成。

SELECT COUNT(*)

FROM IES.ExportNotice notice

LEFT HASH JOIN #TEMP t ON notice.NOTICE_KEY = T.NOTICE_KEY

WHERE

notice.FACTORY_CD IN(SELECT SiteId FROM DCL.SecurityUserSiteMapping WHERE UserId=39)

AND (ISNULL(notice.FACT_EXPORT_QTY,0)-ISNULL(T.FactQty,0))>0

AND (ISNULL(@PSANo,'')=''  Or notice.PSA_NO LIKE '%%')

AND (ISNULL(@ExistNoticeKey,'')='' )

AND (ISNULL(@ShipMode,'')='' OR  notice.SHIP_MODE_CD=@ShipMode)

AND (ISNULL(@CY_FLAG,'')='' OR notice.CY_FLAG=@CY_FLAG)

AND (ISNULL(@PO,'')='' OR notice.BUYER_PO_NO LIKE '%'+@PO+'%')

AND (ISNULL(@BuyerName,'')='' OR notice.NAME LIKE '%'+@BuyerName+'%')

AND (ISNULL(@Destination,'')='' OR notice.SZ=@Destination)

AND (ISNULL(@FinalDestination,'')='' OR notice.FINAL_DESTINATION LIKE '%'+@FinalDestination+'%')

AND (ISNULL(@Factory,'')='' OR notice.FACTORY_CD=@Factory)

AND (ISNULL(@EELForwarder,'')='' OR notice.EEL_FORWARDER=@EELForwarder)

AND (ISNULL(@NoticeDateStart,'2000-01-01')='2000-01-01')

AND ( ISNULL(@NoticeDateEnd,'1999-01-01')='1999-01-01')

虽然解决了问题，但是我隐隐觉得这应该是SQL SERVER优化器的某些Bug才导致出现这种特殊的情况。而且执行计划的Cost也完全不准确。让人有点匪夷所思。

正文到此结束

所属分类：编程技术

本文标签： ip 注释 SQL Server db 数据性能问题 tab UI final map 开发 sql key find Select tar App CTO
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流