SQL SERVER 2014 下IF EXITS 居然引起执行计划变更的案例分享

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

  这个问题是在SQL SERVER 2005 升级到SQL SERVER 2014的测试过程中一同事发现的。我觉得有点意思,遂稍微修改一下脚本展示出来,本来想构造这样的一个案例来演示,但是畏惧麻烦,遂直接贴上原表,希望 Leader不要叼我(当然个人觉得真没啥,两张表名而已,真泄露不了啥信息)。

    脚本如下所示,非常简单的一段SQL语句,我将其分为SQL1、SQL2、SQL3.  其实SQL2、SQL3是差不多的,唯一的区别在于多了一个IF EXISTS

DECLARE @Operation_Code CHAR(3) ,
    @FNCardList VARCHAR(1000) ,
    @RollList VARCHAR(1000) ,
    @White VARCHAR(20) ,
    @OneMinute VARCHAR(20) ,
    @Operator VARCHAR(20) ,
    @Is_NoWait BIT ,
    @HoldCards VARCHAR(3000);            
 
 
SELECT  @Operation_Code = '999' ,
        @FNCardList = 'A15309913' ,
        @RollList = 'A15309913';
 
 
--SQL 1
DECLARE @FNCardTable TABLE ( Iden INT, FN_Card CHAR(9) ); 
 
 
INSERT  INTO @FNCardTable
        SELECT  Iden ,
                [No]
        FROM    PUBDB.dbo.udf_ConvertStrToTable(@FNCardList, ',') a;            
 
 
--SQL 2          
SELECT  1
FROM    dbo.fnRepairOperation a WITH ( NOLOCK )
        INNER JOIN @FNCardTable b ON CHARINDEX(b.FN_Card, a.FN_Card) > 0
        INNER JOIN dbo.fnJobTraceHdr c WITH ( NOLOCK ) ON c.FN_Card = b.FN_Card
                                                          AND c.Current_Department = a.Current_Department
WHERE   a.Check_Time IS NULL
        AND a.Is_Ignore = 0;
 
PRINT ( @Operation_Code );     
 
 
--SQL 3      
 
IF EXISTS ( SELECT   1
            FROM    dbo.fnRepairOperation a WITH ( NOLOCK )
                    INNER JOIN @FNCardTable b ON CHARINDEX(b.FN_Card,
                                                        a.FN_Card) > 0
                    INNER JOIN dbo.fnJobTraceHdr c WITH ( NOLOCK ) ON c.FN_Card = b.FN_Card
                                                        AND c.Current_Department = a.Current_Department
            WHERE   a.Check_Time IS NULL
                    AND a.Is_Ignore = 0 )
    BEGIN            
        RAISERROR('返回错误!', 16, 1);            
        RETURN;            
    END

在SQL SERVER 2005的环境中,整个批处理的SQL执行只需要不到1秒的样子。我们也能看到执行计划的COST对比值为0%,99%,1%。

clipboard

在SQL SERVER 2014(SQL Server 2014 - 12.0.2000.8 Standard Edition )中执行时间突然变成了4分41秒。 最 奇怪的是查询计划的COST比值依然为1%,99%,0%。实际测试发现这个COST的比值是不准确的。因为单独执行SQL1、SQL2只需要一秒。但是 执行SQL3就需要4分多钟。(当然SQL SERVER 2005 与SQL SERVER 2014的数据,索引是一致的,细心的人会注意下面提示缺少索引,加上这个索引依然慢的出奇,这个影响因素完全可以忽略)

clipboard[1]

 

SQL 2的实际执行计划如下所示

clipboard[2]

 

SQL 3的实际执行计划如下所示

clipboard[3]

另外,表dbo.fnRepairOperation的记录数有 332553,dbo.fnJobTraceHdr 的记录数为110058。表变量@FNCardTable记录数为1.对比执行计划,我们可以看到两者的Nested Loops的外部表变化了,从表变量@FNCardTable变成了dbo.fnRepairOperation

我们先来看看SQL2执行计划里面的一些详细信息,我们可以看到外边循 环表为@FNCardTable,循环次数为1(Actual Number of Rows 值为1),内部循环表为dbo.fnJobTraceHdr,循环次数为1(Number of Executions为1),符合条件的记录集数据为1条(Actual Number of Rows 值为1)

clipboard[4]

clipboard[5]

那么再来看SQL3, 外部循环表变为dbo.fnRepairOperation,它走表扫描(Table Scan),循环次数为432(Actual Number of Rows),内部循环表为dbo.fnJobTraceHdr, 走索引扫描,总共循环了47545056次,这个值怎么来的呢? 因为内部循环表中符合记录数为110058(表dbo.fnJobTraceHdr的记录数), 110058*432 = 47545056,也就是说总共循环了四千七百多万次。 偶的神啊。难怪如此之慢。起初,我以为是统计信息不准确导致数据库优化器选择了错误的执行计划,于是我更新了这两个表的统计信息,甚至连索引也重建了。结 果还是如此。看来的确是优化器没有选择最优的执行计划。但是没有IF EXITS它又是正常的, 加了IF EXITS后执行计划就变成这个鸟样。说不清是优化器的bug还是算法问题所导致。

clipboard[6]

clipboard[7]

 

那么怎么解决这个问题,可以用联接提示(HASH JOIN HINT)指定SQL语句走HASH JOIN,此时批处理的SQL语句可以1秒出来。另外就是改写该SQL语句的写法。在此不做过多阐述

IF EXISTS ( SELECT   1
            FROM    dbo.fnRepairOperation a WITH ( NOLOCK )
                    INNER JOIN @FNCardTable b ON CHARINDEX(b.FN_Card,
                                                        a.FN_Card) > 0
                    INNER HASH JOIN  dbo.fnJobTraceHdr c WITH ( NOLOCK ) ON c.FN_Card = b.FN_Card
                                                        AND c.Current_Department = a.Current_Department
            WHERE   a.Check_Time IS NULL
                    AND a.Is_Ignore = 0 )
    BEGIN            
        RAISERROR('部分卡中有 班长新增加的工序或 回修工序,请联系一下工艺员和当班班长!', 16, 1);            
        RETURN;            
    END; 

其实这个案例也间接验证了嵌套循环连接,随着数据量的增长,这种方式对性能的消耗将呈现出指数级别的增长。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
8天前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
13天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
1月前
|
数据库 Windows
SqlServer数据恢复—SqlServer数据库所在分区损坏的数据恢复案例
一块硬盘上存放的SqlServer数据库,windows server操作系统+NTFS文件系统。由于误操作导致分区损坏,需要恢复硬盘里的SqlServer数据库数据。
|
4月前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。
|
4月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
125 1
|
5月前
|
关系型数据库 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)")
|
5月前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
136 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
6月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
607 0
|
7月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
94 6
|
7月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
111 9