MSSQL效率优化随记

1.尽量不要在where中进行计算，尤其是需要遍历的情况
原来代码，执行大约40秒

INSERT INTO @TransferCode ( PROXY_NO, TING_NO, WTotal, BTotal )
            SELECT    H.PROXY_NO, H.TING_NO, 
                    SUM(CASE WHEN H.CARDTYPE='W' THEN CASE WHEN (B.DDATE='' AND B.DTIME='') THEN 0 WHEN B.BCHIPEX='10' THEN -B.BCHIP ELSE B.BCHIP END ELSE 0 END),
                    SUM(CASE WHEN H.CARDTYPE='B' THEN CASE WHEN (B.DDATE='' AND B.DTIME='') THEN 0 WHEN B.BCHIPEX='10' THEN -B.BCHIP ELSE B.BCHIP END ELSE 0 END)
            FROM    dbo.CM_ROCARDB AS B
            LEFT JOIN dbo.CM_ROCARDH AS H ON B.BARCODE = H.BARCODE
            LEFT JOIN dbo.CM_PROXY AS P ON P.PROXY_NO=H.PROXY_NO
            WHERE    (@TingNo='' OR H.TING_NO=@TingNo)
                AND (@ProxyNo=''  OR (H.PROXY_NO LIKE @ProxyNo+'%' OR P.PROXY_NAME LIKE @ProxyNo+'%'))
                AND (@HuKou='' OR (EXISTS(SELECT 1 FROM dbo.CM_PROXY WHERE HUKOU_NO=@HuKou AND PROXY_NO=H.PROXY_NO)))
                AND    dbo.GetTransCodeEndYM(B.BARCODE, B.BodyNo)=@YearMonth
            GROUP BY H.PROXY_NO, H.TING_NO
            ORDER BY H.PROXY_NO, H.TING_NO

主要是费时的在dbo.GetTransCodeEndYM(B.BARCODE, B.BodyNo)=@YearMonth函数这段，其中B.BARCODE, B.BodyNo为
Primary Key
优化的代码，执行大约1秒

INSERT INTO @TransferCode ( PROXY_NO, TING_NO, WTotal, BTotal )
            SELECT    PROXY_NO, TING_NO, 
                    SUM(CASE WHEN CARDTYPE='W' THEN CASE WHEN (DDATE='' AND DTIME='') THEN 0 WHEN BCHIPEX='10' THEN -BCHIP ELSE BCHIP END ELSE 0 END),
                    SUM(CASE WHEN CARDTYPE='B' THEN CASE WHEN (DDATE='' AND DTIME='') THEN 0 WHEN BCHIPEX='10' THEN -BCHIP ELSE BCHIP END ELSE 0 END)
            FROM (
            SELECT    H.PROXY_NO, H.TING_NO, H.CARDTYPE, B.DDATE, B.DTIME, B.BCHIPEX, B.BCHIP,
                    dbo.GetTransCodeEndYM(B.BARCODE, B.BodyNo) AS YearMonth
            FROM    dbo.CM_ROCARDB AS B
            LEFT JOIN dbo.CM_ROCARDH AS H ON B.BARCODE = H.BARCODE
            LEFT JOIN dbo.CM_PROXY AS P ON P.PROXY_NO=H.PROXY_NO
            WHERE    (@TingNo='' OR H.TING_NO=@TingNo)
                AND (@ProxyNo=''  OR (H.PROXY_NO LIKE @ProxyNo+'%' OR P.PROXY_NAME LIKE @ProxyNo+'%'))
                AND (@HuKou='' OR (EXISTS(SELECT 1 FROM dbo.CM_PROXY WHERE HUKOU_NO=@HuKou AND PROXY_NO=H.PROXY_NO)))
            ) AS A
            WHERE YearMonth=@YearMonth    
            GROUP BY PROXY_NO, TING_NO
            ORDER BY PROXY_NO, TING_NO

将步骤调整为先执行过滤数据，函数计算放在select中，这样将过滤数据后再进行计算，然后再通过过滤年月查询
注：测试时只查询一个proxyno,为空时估计更明显

转载于:https://www.cnblogs.com/ywkpl/p/3571357.html