你的位置:首页 > 数据库

[数据库]分析函数优化自关联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的执行效率有一定帮助。

(本文完)