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

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

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')

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')

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')