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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL SERVER 2014 下IF EXITS 居然引起执行计划变更的案例分享，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4336字，纯文字阅读大概需要7分钟。

内容图文

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

 

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

 

SQL 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）

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

 

那么怎么解决这个问题，可以用联接提示(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 SERVER 2014 下IF EXITS 居然引起执行计划变更的案例分享

标签：

本文系统来源：http://www.cnblogs.com/lonelyxmas/p/5056181.html

内容总结

以上是互联网集市为您收集整理的SQL SERVER 2014 下IF EXITS 居然引起执行计划变更的案例分享全部内容，希望文章能够帮你解决SQL SERVER 2014 下IF EXITS 居然引起执行计划变更的案例分享所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---