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

时间:2024-04-24 14:05:13

这个问题是在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%。

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

在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的数据,索引是一致的,细心的人会注意下面提示缺少索引,加上这个索引依然慢的出奇,这个影响因素完全可以忽略)

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

 

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

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

 

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

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

另外,表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)

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

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

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

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

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

那么怎么解决这个问题,可以用联接提示(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; 

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