分析函数优化自关联二_Sql Server

分析函数优化自关联2

数据库环境：SQL SERVER 2005

　　今天抓了一条返回数据比较少，但是执行时间相对较长的SQL。返回421行，执行了50S，

说明SQL的执行效率有提升的空间。

　　我们先来看一下SQL文本

SELECT  a.dept_id ,        c.dept_name ,        a.clerk_id ,        d.clerk_name ,        a.item_id ,        RTRIM(e.item_name) item_name ,        a.dec_sqty ,        b.dec_sum ,        a.fiscal_year ,        a.fiscal_periodFROM    v_ctlm8666_sum a ,        ctlm1003 c ,        ctlm1006 d ,        ctlm1012 e ,        ctlm8201 f ,        ( SELECT    dept_id ,                    clerk_id ,                    SUM(dec_sqty) AS dec_sum          FROM      v_ctlm8666_sum          WHERE     fiscal_year = 2015                    AND fiscal_period <= 7                    AND dept_id LIKE '%'                    AND clerk_id LIKE '%'                    AND dept_id IN ( SELECT dept_id                                     FROM   v_ctlm1010                                     WHERE  clerk_id = '020123' )          GROUP BY  dept_id ,                    clerk_id        ) bWHERE   a.item_id = f.item_id        AND a.dept_id = f.dept_id        AND a.fiscal_year = f.fiscal_year        AND a.dept_id = b.dept_id        AND a.clerk_id = b.clerk_id        AND a.dept_id = c.dept_id        AND a.clerk_id = d.clerk_id        AND a.item_id = e.item_id        AND a.dept_id LIKE '%'        AND a.clerk_id LIKE '%'        AND a.fiscal_year = 2015        AND a.fiscal_period = 7        AND a.dept_id IN ( SELECT   dept_id                           FROM     v_ctlm1010                           WHERE    clerk_id = '020123' )

View Code

　　我们注意到，内联视图b访问的表及过滤条件和外部很相似，且内联视图要实现的功能是查询

本年度1到7月份的销量合计。因此，我们通过分析函数来实现同样的功能，这样，对视图v_ctlm8666_sum

和v_ctlm1010就只访问一次。

　　按照上面的思路，我们将SQL改写如下：

/*将内联视图和外部表相同过滤条件的放到with中*/WITH    x0          AS ( SELECT   a.dept_id ,                        a.clerk_id ,                        a.item_id ,                        a.dec_sqty ,                        SUM(dec_sqty) OVER ( PARTITION BY a.dept_id,                                             a.clerk_id ) AS dec_sum ,                        a.fiscal_year ,                        a.fiscal_period               FROM     v_ctlm8666_sum a               WHERE    fiscal_year = 2015                        AND fiscal_period <= 7                        AND dept_id LIKE '%'                        AND clerk_id LIKE '%'                        AND dept_id IN ( SELECT dept_id                                         FROM   v_ctlm1010                                         WHERE  clerk_id = '020123' )             )/*缩小x0的范围，只取本年度7月份的数据*/    SELECT  a.dept_id ,            c.dept_name ,            a.clerk_id ,            d.clerk_name ,            a.item_id ,            RTRIM(e.item_name) item_name ,            a.dec_sqty ,            a.dec_sum ,            a.fiscal_year ,            a.fiscal_period    FROM    x0 a ,            ctlm1003 c ,            ctlm1006 d ,            ctlm1012 e ,            ctlm8201 f    WHERE   a.item_id = f.item_id            AND a.dept_id = f.dept_id            AND a.fiscal_year = f.fiscal_year            AND a.dept_id = c.dept_id            AND a.clerk_id = d.clerk_id            AND a.item_id = e.item_id            AND a.fiscal_year = 2015            AND a.fiscal_period = 7

View Code

　　我们将改写后SQL的执行下，只执行4S钟就全部出结果了。再核对一下数据，和原先的SQL执行出来的结果一致，

说明通过分析函数改写确实提高了SQL的执行效率。

　　由于在v_ctlm8666_sum的字段fiscal_year、fiscal_period上没有索引，通过在这两个字段上建组合索引，将会对

提升SQL的执行效率有一定帮助。

（本文完）