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文的执行计划就正确了。