你的位置:首页 > 数据库

[数据库]通过自关联替代开窗函数实现SQL优化


数据库环境:SQL SERVER 2005

  我们平时都是通过开窗析函数来取代自关联,减少表扫描,从而优化SQL。今天,反其道而行,

用自关联改写开窗函数。我们先来看一下SQL。

  原SQL语句

SELECT * FROM  ( SELECT  MIN(a.line_no) OVER ( PARTITION BY a.id_clerk ) AS line_no ,          a.dslyipt_no ,          a.int_year ,          a.int_period ,          a.id_scheme ,          d.name_scheme ,          a.id_schhelp ,          e.name_schhelp ,          a.id_dept ,          b.name_dept ,          a.id_clerk ,          c.name_clerk ,          a.id_slyitem ,          a.name_slyitem ,          a.dec_value     FROM   dslyipt_03 a ,          ctlm1003 b ,          ctlm1007 c ,          ctlm7201 d ,          ctlm7219 e     WHERE   a.id_dept = b.id_dept          AND a.id_clerk = c.id_clerk          AND a.id_scheme = d.id_scheme          AND a.id_schhelp = e.id_schhelp          AND a.id_scheme = e.id_scheme    ) dslyipt_03WHERE  dslyipt_no IN ( '201509000169' )------------------------相关表的数据量----------------------------------------SELECT COUNT(*) FROM dslyipt_03 --2321920SELECT COUNT(*) FROM dslyipt_03 WHERE  dslyipt_no IN ( '201509000169' )--16SELECT COUNT(*) FROM ctlm1003 --125SELECT COUNT(*) FROM ctlm1007 --11986SELECT COUNT(*) FROM ctlm7201 --16SELECT COUNT(*) FROM ctlm7219 --32

View Code

  dslyipt_03表是大表,有2321920条数据,过滤之后只有16条数据,原SQL总共执行了30多秒才出结果,

因此可以判定,SQL还有优化的余地。

  对2KW的表进行开窗,慢是有原因的。那么,我们是否可以先过滤再开窗呢?我们来改一下SQL,改写之后

的SQL如下:

SELECT  MIN(a.line_no) OVER ( PARTITION BY a.id_clerk ) AS line_no ,          a.dslyipt_no ,          a.int_year ,          a.int_period ,          a.id_scheme ,          d.name_scheme ,          a.id_schhelp ,          e.name_schhelp ,          a.id_dept ,          b.name_dept ,          a.id_clerk ,          c.name_clerk ,          a.id_slyitem ,          a.name_slyitem ,          a.dec_value     FROM   dslyipt_03 a ,          ctlm1003 b ,          ctlm1007 c ,          ctlm7201 d ,          ctlm7219 e     WHERE   a.id_dept = b.id_dept          AND a.id_clerk = c.id_clerk          AND a.id_scheme = d.id_scheme          AND a.id_schhelp = e.id_schhelp          AND a.id_scheme = e.id_scheme          AND a.dslyipt_no IN ( '201509000169' )

View Code

  嗖的一下,数据就出来了!但是,再核对一下数据,发现字段line_no的数据对不上,说明改错了。改写

后对line_no开窗的范围变小了,所以数据有问题。

  现在通过自关联来实现开窗函数的功能,自关联改写的SQL如下:

SELECT f.line_no ,    a.dslyipt_no ,    a.int_year ,    a.int_period ,    a.id_scheme ,    d.name_scheme ,    a.id_schhelp ,    e.name_schhelp ,    a.id_dept ,    b.name_dept ,    a.id_clerk ,    c.name_clerk ,    a.id_slyitem ,    a.name_slyitem ,    a.dec_valueFROM  dslyipt_03 a ,    ctlm1003 b ,    ctlm1007 c ,    ctlm7201 d ,    ctlm7219 e ,    ( SELECT  a.id_clerk ,          MIN(a.line_no) AS line_no     FROM   dslyipt_03 a     GROUP BY a.id_clerk    ) fWHERE  a.id_dept = b.id_dept    AND a.id_clerk = c.id_clerk    AND a.id_scheme = d.id_scheme    AND a.id_schhelp = e.id_schhelp    AND a.id_scheme = e.id_scheme    AND a.id_clerk = f.id_clerk    AND a.dslyipt_no IN ( '201509000169' )OPTION(HASH JOIN)

View Code

  在语句后面,我们通过查询提示,建议SQL走哈希连接,1S之内就出结果了,经核对,数据无误!

  我们来对比一下改写前后TIME、IO

  改写前

改写前

  改写后

哈尔滨跟团旅游报价多少哈尔滨旅游线路大全哈尔滨旅游报价价格哈尔滨旅游团购优惠深圳去哈尔滨旅游报价厦门娱乐信息 新西兰传统节日 武夷山:自助游注意事项 新西兰水果蜂蜜奶油酥 中国山东青岛小吃:海菜凉粉 建于明代洪武的安顺文庙(组图) 国内3大性价比最高的避暑胜地:贵州 遵义赤水十丈瀑布群介绍 【腾冲旅游】—惊艳如同邂逅秀外慧中的女子 【腾冲旅游】—腾冲坛子鸡:清肺健胃 满口溢香 【腾冲旅游】—中国最美的十大地质公园:腾冲火山 【腾冲旅游】—腾冲油炸撒撇:油而不腻 江门大西坑公园怎么样? 清远碧水滩漂流要门票吗?门票是多少? 暑假珠海九州水会都有什么好玩的娱乐项目啊? 江门白水带风景区好玩吗?景色美嘛? ADG742BKSZ5-REEL Datasheet ADG742BKSZ5-REEL Datasheet AD7984BRMZ-RL7 Datasheet AD7984BRMZ-RL7 Datasheet ADG787BCBZ-REEL Datasheet ADG787BCBZ-REEL Datasheet 瑞士机票 瑞士机票 瑞士机票 raileurope raileurope raileurope 西班牙签证费用 西班牙签证费用 西班牙签证费用