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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例    开发人员遇到一个及其诡异的的SQL性能问题,这段完整SQL语句如下所示: declare @UserId             INTdeclare @PSANo              VARCHAR(2...
原文: 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语句一般一秒以内完成。但是这段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的执行计划叫做Plan A,执行性能很差的SQL的执行计划叫做Plan B

Plan A

Plan B

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

于是我使用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')

于是我将怀疑的地方转移到表连接方式,使用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也完全不准确。让人有点匪夷所思。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
3月前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
104 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
15天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
22天前
|
SQL 数据库 UED
SQL性能提升秘籍:5步优化法与10个实战案例
在数据库管理和应用开发中,SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例,帮助构建高效、稳定的数据库应用。
37 3
|
2月前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。
|
22天前
|
SQL 缓存 监控
SQL性能提升指南:五大优化策略与十个实战案例
在数据库性能优化的世界里,SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载,提高应用响应速度,甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤,并结合十个实战案例,为你提供一份详尽的性能提升指南。
42 0
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
64 1
|
2月前
|
SQL 大数据 API
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
大数据-132 - Flink SQL 基本介绍 与 HelloWorld案例
50 0
|
2月前
|
SQL
使用SQL进行内外连接和嵌套查询
使用SQL进行内外连接和嵌套查询
47 0
|
2月前
|
SQL 数据库
SQl查询之单表查询,连接与嵌套查询
SQl查询之单表查询,连接与嵌套查询
34 0
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")