SQL Server性能调优杂记4(小心SQLCmd的动态参数方法让你堕入性能问题)

系统上线完,性能问题往往是Warranty和后期维护的一个重要问题。
这些天,客户又来反映,有一个查询非常慢。这个查询用的是主关键字查询,由于主键是聚集索引,而且又做了碎片处理。应该是非常快。但是看到的现象就是很慢(10秒左右,最差有18秒之多)。排除了硬件、资源锁定等问题,还不用到达Database端的Tunning级别。基本判断和SQL文有关,要细看SQL文的执行计划。
首先把SQL文找出来
SELECT      a.AWB_NO,

                a.BWB_NO,

                a.CWB_NO,

                a.ORIGIN,

                a.DEST,

                a.MODIFY_ON,

                a.CREATED_ON,

                a.CONSIGNOR_CUSTOMER_CODE,

                a.CONSIGNOR_CODE,  

                a.CONSIGNOR_NAME,

                a.CONSIGNEE_NAME,

                a.CWB_STATUS,

                a.CWB_TYPE,

                ISNULL(a.PCS, 0) AS PCS,

                a.BWBLIST,

                b.PWEIGHT    

    FROM        TB_CWB AS a

    LEFT JOIN   TB_CWBWEIGHT AS b

    ON            a.CWB_NO = b.CWB_NO

    AND            b.AVAILABLE = 'Y' 

    WHERE       a.AVAILABLE = 'Y'

    AND            (a.CWB_NO = @CWB_NO OR

                                                                          (( @CWB_NO IS NULL)

    AND            (a.AWB_NO = @AWB_NO OR @AWB_NO IS NULL)

    AND            (a.BWB_NO = @BWB_NO OR @BWB_NO IS NULL)

    AND            (a.IE_TYPE = @IE_TYPE OR @IE_TYPE IS NULL)

    AND            (a.CREATED_ON >= @DateFrom OR @DateFrom IS NULL)

    AND            (a.CREATED_ON <=  @DateTo OR @DateTo IS NULL)

    AND            (a.PAYMENT = @PAYMENT OR @PAYMENT IS NULL)

    AND            (a.ORIGIN = @ORIGIN OR @ORIGIN IS NULL)

    AND            (a.DEST = @DEST OR @DEST IS NULL)

    AND            (a.CONSIGNOR_CUSTOMER_CODE = @CONSIGNOR_CUSTOMER_CODE OR @CONSIGNOR_CUSTOMER_CODE IS NULL)

    AND            (a.CONSIGNOR_NAME LIKE '%' + @CONSIGNOR_NAME + '%' OR @CONSIGNOR_NAME IS NULL)

    AND            (a.CONSIGNEE_NAME LIKE '%' + @CONSIGNEE_NAME + '%' OR @CONSIGNEE_NAME IS NULL)

    AND            (a.CWB_TYPE = @CWB_TYPE OR @CWB_TYPE IS NULL)))

虽然冗长,但是基本结构很清晰,就是如果当用主关键字查询,后面一大堆就不起什么作用。系统的速度和聚集索引有关系。
把语句改造一下,

SELECT      a.AWB_NO,

                a.BWB_NO,

                a.CWB_NO,

                a.ORIGIN,

                a.DEST,

                a.MODIFY_ON,

                a.CREATED_ON,

                a.CONSIGNOR_CUSTOMER_CODE,

                a.CONSIGNOR_CODE,  

                a.CONSIGNOR_NAME,

                a.CONSIGNEE_NAME,

                a.CWB_STATUS,

                a.CWB_TYPE,

                ISNULL(a.PCS, 0) AS PCS,

                a.BWBLIST,

                b.PWEIGHT    

    FROM        TB_CWB AS a

    LEFT JOIN   TB_CWBWEIGHT AS b

    ON            a.CWB_NO = b.CWB_NO

    AND            b.AVAILABLE = 'Y' 

    WHERE       a.AVAILABLE = 'Y'

    AND            (a.CWB_NO = '31017768390' OR

                                                                          (( '31010930775' IS NULL)

    AND            (a.AWB_NO = NULL OR NULL IS NULL)

    AND            (a.BWB_NO = NULL OR NULL IS NULL)

    AND            (a.IE_TYPE = NULL OR NULL IS NULL)

    AND            (a.CREATED_ON >= NULL OR NULL IS NULL)

    AND            (a.CREATED_ON <=  NULL OR NULL IS NULL)

    AND            (a.PAYMENT = NULL OR NULL IS NULL)

    AND            (a.ORIGIN = NULL OR NULL IS NULL)

    AND            (a.DEST = NULL OR NULL IS NULL)

    AND            (a.CONSIGNOR_CUSTOMER_CODE = NULL OR NULL IS NULL)

    AND            (a.CONSIGNOR_NAME LIKE '%' + NULL + '%' OR NULL IS NULL)

    AND            (a.CONSIGNEE_NAME LIKE '%' + NULL + '%' OR NULL IS NULL)

    AND            (a.CWB_TYPE = NULL OR NULL IS NULL)))
执行速度飞快。毫秒级别。
那么为什么客户端程序,速度很慢呢(差10个数量级)?
我第一反应,2个的SQL文一定不一样。因为客户端代码采用的是SQLCommand的动态参数写法。在这种情况下,SQL Server会在数据库后台用一个动态执行的存储过程来执行(应该是为了重用执行计划)。
我们来看看SQL Server的执行方法
exec sp_executesql N'

SELECT      a.AWB_NO,
                a.BWB_NO,
                a.CWB_NO,
                a.ORIGIN,
                a.DEST,
                a.MODIFY_ON,
                a.CREATED_ON,
                a.CONSIGNOR_CUSTOMER_CODE,
                a.CONSIGNOR_CODE,  
                a.CONSIGNOR_NAME,
                a.CONSIGNEE_NAME,
                a.CWB_STATUS,
                a.CWB_TYPE,
                ISNULL(a.PCS, 0) AS PCS,
                a.BWBLIST,
                b.PWEIGHT    
    FROM        TB_CWB AS a
    LEFT JOIN   TB_CWBWEIGHT AS b
    ON            a.CWB_NO = b.CWB_NO
    AND            b.AVAILABLE = ''Y'' 
    WHERE       a.AVAILABLE = ''Y''
    AND            (a.CWB_NO = @CWB_NO OR
                                                                          (( @CWB_NO IS NULL)
    AND            (a.AWB_NO = @AWB_NO OR @AWB_NO IS NULL)
    AND            (a.BWB_NO = @BWB_NO OR @BWB_NO IS NULL)
    AND            (a.IE_TYPE = @IE_TYPE OR @IE_TYPE IS NULL)
    AND            (a.CREATED_ON >= @DateFrom OR @DateFrom IS NULL)
    AND            (a.CREATED_ON <=  @DateTo OR @DateTo IS NULL)
    AND            (a.PAYMENT = @PAYMENT OR @PAYMENT IS NULL)
    AND            (a.ORIGIN = @ORIGIN OR @ORIGIN IS NULL)
    AND            (a.DEST = @DEST OR @DEST IS NULL)
    AND            (a.CONSIGNOR_CUSTOMER_CODE = @CONSIGNOR_CUSTOMER_CODE OR @CONSIGNOR_CUSTOMER_CODE IS NULL)
    AND            (a.CONSIGNOR_NAME LIKE ''%'' + @CONSIGNOR_NAME + ''%'' OR @CONSIGNOR_NAME IS NULL)
    AND            (a.CONSIGNEE_NAME LIKE ''%'' + @CONSIGNEE_NAME + ''%'' OR @CONSIGNEE_NAME IS NULL)
    AND            (a.CWB_TYPE = @CWB_TYPE OR @CWB_TYPE IS NULL)))',N'@CWB_NO nvarchar(11),@AWB_NO nvarchar(4000),@BWB_NO nvarchar(4000),@IE_TYPE
nvarchar(4000),@DateFrom nvarchar(4000),@DateTo nvarchar(4000),@CWB_TYPE nvarchar(4000),@PAYMENT nvarchar(4000),@ORIGIN nvarchar(4000),@DEST
nvarchar(4000),@CONSIGNOR_CUSTOMER_CODE nvarchar(4000),@CONSIGNOR_NAME nvarchar(4000),@CONSIGNEE_NAME
nvarchar(4000)',@CWB_NO=N'31017768390',@AWB_NO=NULL,@BWB_NO=NULL,@IE_TYPE=NULL,@DateFrom=NULL,@DateTo=NULL,@CWB_TYPE=NULL,@PAYMENT=NULL,@ORIGIN=NULL,@DEST=NULL,@CONSIGNOR_CUSTOMER_CODE=NULL,@CONSIGNOR_NAME=NULL,@CONSIGNEE_NAME=NULL

这前后2种SQL文,在执行计划上有差异,所以导致一快一慢。后者竟然可以慢到17秒之多(第一产生执行计划的时候),即使马上再执行也有10秒到15秒,改善不大。

看一下SQL文1的执行计划


SQL文2的执行计划

前后最大的差别就是多了中间的Filter.


Filter这一步对于整个性能的影响很明显。而这步处理的工作就是原本我们SQL文意图中要忽略掉的部分。

所以,千万要小心,当用SQLCommand的动态参数为你的应用程序带来共通化方便的同时,也会让你程序带来性能问题的风险。


解决问题的方法,显然不能修改程序代码(换成动态自己产生SQL文),因为这是基础框架代码。
只能把SQL文改写一下
BEGIN
IF NOT(@CWB_NO IS NULL)
SELECT a.AWB_NO, a.BWB_NO,a.CWB_NO,a.ORIGIN,
a.DEST, a.MODIFY_ON, a.CREATED_ON,a.CONSIGNOR_CUSTOMER_CODE,
a.CONSIGNOR_CODE,a.CONSIGNOR_NAME,a.CONSIGNEE_NAME, a.CWB_STATUS,
a.CWB_TYPE,ISNULL(a.PCS, 0) AS PCS,a.BWBLIST,b.PWEIGHT    
FROM  TB_CWB AS a
LEFT JOIN TB_CWBWEIGHT AS b
ON  a.CWB_NO = b.CWB_NO
AND  b.AVAILABLE = ''Y'' 
WHERE  a.AVAILABLE = ''Y''
AND a.CWB_NO = @CWB_NO 
ELSE
SELECT a.AWB_NO, a.BWB_NO,a.CWB_NO,a.ORIGIN,
a.DEST, a.MODIFY_ON, a.CREATED_ON,a.CONSIGNOR_CUSTOMER_CODE,
a.CONSIGNOR_CODE,a.CONSIGNOR_NAME,a.CONSIGNEE_NAME, a.CWB_STATUS,
a.CWB_TYPE,ISNULL(a.PCS, 0) AS PCS,a.BWBLIST,b.PWEIGHT    
FROM  TB_CWB AS a
LEFT JOIN TB_CWBWEIGHT AS b
ON  a.CWB_NO = b.CWB_NO
AND  b.AVAILABLE = ''Y'' 
WHERE  a.AVAILABLE = ''Y''
AND (a.AWB_NO = @AWB_NO OR @AWB_NO IS NULL)
AND  (a.BWB_NO = @BWB_NO OR @BWB_NO IS NULL)
AND  (a.IE_TYPE = @IE_TYPE OR @IE_TYPE IS NULL)
AND  (a.CREATED_ON >= @DateFrom OR @DateFrom IS NULL)
AND (a.CREATED_ON <=  @DateTo OR @DateTo IS NULL)
AND (a.PAYMENT = @PAYMENT OR @PAYMENT IS NULL)
AND (a.ORIGIN = @ORIGIN OR @ORIGIN IS NULL)
AND (a.DEST = @DEST OR @DEST IS NULL)
AND (a.CONSIGNOR_CUSTOMER_CODE = @CONSIGNOR_CUSTOMER_CODE OR @CONSIGNOR_CUSTOMER_CODE IS NULL)
AND (a.CONSIGNOR_NAME LIKE ''%'' + @CONSIGNOR_NAME + ''%'' OR @CONSIGNOR_NAME IS NULL)
AND (a.CONSIGNEE_NAME LIKE ''%'' + @CONSIGNEE_NAME + ''%'' OR @CONSIGNEE_NAME IS NULL)
AND (a.CWB_TYPE = @CWB_TYPE OR @CWB_TYPE IS NULL)
END

结果SQL文的执行计划就正确了。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 1

打赏作者

enhydraboy

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值