MSSQL效率优化随记

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

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