×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Florida

我也来一个SQL实例, 如何改写subquery

886xyz(cqcq)
本文发表在 rolia.net 枫下论坛select ...
from T1,T2,T3，T4
where ...
EXISTS(SELECT
1
FROM T16
WHERE (T16.NUM = 34) AND
(EXISTS(SELECT 1
FROM T14
WHERE (T14.VID = T2.VID) AND (T14.IID = T2.IID) AND
T16.CLL IN (T14.CLLCODE, -1))
OR
EXISTS(SELECT 1
FROM T12
WHERE (T12.VID = T2.VID) AND (T12.IID = T2.IID) AND
((T16.CLL = T12.CLLCODE) OR (T16.CLL = -1)))
)
)

Without subquery, it returns 500 rows. T16 has 10 million rows, T12 has 25k rows, T14 has 200k rows.

There is a foreign key relationshipt between T12 and T16, T12 is the child.
Same as T14 and T16, T14 is the child.

Every row in T3 can find a row either in T12 or T14.

-1 is a rare value in T16.CLL. 34 is a frequent value in T16.NUM.

Without query rewrite, it requires 15 minutes to finish, after query rewrite, 16 seconds.更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#3262175@0)
Last Updated: 2006-10-14
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 我也来一个SQL实例, 如何改写subquery -886xyz(cqcq); 2006-10-14 {1028} (#3262175@0)
    本文发表在 rolia.net 枫下论坛select ...
    from T1,T2,T3，T4
    where ...
    EXISTS(SELECT
    1
    FROM T16
    WHERE (T16.NUM = 34) AND
    (EXISTS(SELECT 1
    FROM T14
    WHERE (T14.VID = T2.VID) AND (T14.IID = T2.IID) AND
    T16.CLL IN (T14.CLLCODE, -1))
    OR
    EXISTS(SELECT 1
    FROM T12
    WHERE (T12.VID = T2.VID) AND (T12.IID = T2.IID) AND
    ((T16.CLL = T12.CLLCODE) OR (T16.CLL = -1)))
    )
    )

    Without subquery, it returns 500 rows. T16 has 10 million rows, T12 has 25k rows, T14 has 200k rows.

    There is a foreign key relationshipt between T12 and T16, T12 is the child.
    Same as T14 and T16, T14 is the child.

    Every row in T3 can find a row either in T12 or T14.

    -1 is a rare value in T16.CLL. 34 is a frequent value in T16.NUM.

    Without query rewrite, it requires 15 minutes to finish, after query rewrite, 16 seconds.更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • 太复杂了，看的眼花花。 -newnewcanada(new); 2006-10-20 (#3273368@0)
    • 看起来有点怪…… -newkid(newkid); 2006-10-20 {416} (#3273452@0)
      T1,T2,T3,T4居然没有任何连接条件。不清楚这几个表的大小，但这样的写法是罕见的。注意到你WHERE后面有省略号，难道是公司机密？
      T12,T14 作为CHILD表，居然比PARENT小很多。
      T3和T12,T14有关，可是居然没在原SQL中体现出来。QUERY REWRITE的原则是要逻辑等价，要不就是原来写法有错。
      没有索引的说明，从表的列名也猜不出值的分布情况（比方说，NAME或ID就是很好的索引对象，而GENDER就不是）
      如果你有什么心得体会，不妨公布出来和大家分享。
      • 不好意思，写错了。 -886xyz(cqcq); 2006-10-21 {656} (#3276529@0)
        In “Every row in T3 can find a row either in T12 or T14.” , T3 should be T2. 连接条件写在WHERE里了.

        原来的SQL还有很多条件,但关键是这段subquery写得太烂.一是nested subquery, 二是cartesian join.

        改后的SQL:

        select ...
        from T1,T2,T3,T4
        where ...
        (EXISTS (SELECT 1 FROM T16, T14
        WHERE (T16.NUM = 34) AND (T14.VID = T2.VID) AND (T14.IID = T2.IID) AND T16.CLL IN (T14.CLLCODE, -1))
        OR
        EXISTS (SELECT 1
        FROM T16, T12 WHERE (T16.NUM = 34) AND (T12.VID = T2.VID) AND (T12.IID = T2.IID) AND T16.CLL IN (T12.CLLCODE, -1)))

        实际上, 在上面的SQL里还是有cartesian join. 幸好developer痛定思痛, 把-1也去掉了 (在没违背原设计的情况下). 最后执行时间是0.5秒!
        • 猜测 -looi500(looi); 2006-10-22 {1026} (#3277111@0)
          本文发表在 rolia.net 枫下论坛根据你所讲的, 猜测T16与T12和T14的foreign key是CLL与CLLCODE. 如果对的话, CLL是T16的一个INDEX. 还有VID和 IID是T2, T12和T14上的INDEX. NUM可能是T16的INDEX或不是. 即使是, 这个INDEX也可能不起太大作用因为34 is a frequent value in T16.NUM.

          第一个QUERY的第一层SUBQUERY "EXISTS(SELECT 1 FROM T16 WHERE (T16.NUM = 34)... " 可能造成在ACCESＳ　T1, T2, T3, T4, T12和T14之前ACCESＳT16．这样T２, T12和T14的ＩＮＤＥＸ都不会被用上，　这是造成巨慢的原因．
          第二个QUERY只有一层SUBQUERY. T16 和T12, T14在同一层. 因为T12和T14相对T16要小很多, 所以T2, T12和T14先被ACCESＳ．这样一来T12和T14的INDEX　VID和 IID就会被用上．但因为T16.CLL IN (T14.CLLCODE, -1)，造成T16的INDEX　CLL不会被使用．但因为其它ＴＡＢＬＥ的ＩＮＤＥＸ被使用了，所以速度快很多．
          在第二个ＱＵＥＲＹ上，当把T16.CLL IN (T14.CLLCODE, -1)该成T16.CLL ＝T14.CLLCODE，INDEX　T16.CLL就会被用到．达到最佳效果．

          如果-1是一定需要的话，你可以先做个QUERY　ＳELECT 1 FROM T16　WHERE T16.NUM = 34 AND T16.CLL ＝ -1．如果结果是空，就用优化后的ＱＵＥＲＹ．　如果不是空，就把T16从QUERY里去掉．更多精彩文章及讨论，请光临枫下论坛 rolia.net

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论