yosolo
2018-11-07 13:52
采纳率: 50%
浏览 540

一条SQL语句的优化,SQL Server数据库

SQL如下, t3和t4数据量大,查询速度很慢,请教如何优化,提高速度。谢谢

 SELECT
    t1.C1,
    t3.C2,
    t3.C3,
    t3.C4,
    t2.C5,
    t2.C6,
    t2.C_DATE,
    t3.C7,
    t3.C_DATE2
FROM
    T1 t1,
    T2 t2,
    T3 t3,
    T4 t4
WHERE
    t4.FID = t3.FID
AND t3.C_DATE2 <= (
    SELECT
        t5.EDATE
    FROM
        T5 t5
    WHERE
        t5.OH_ID = '1234'
)
AND t3.C_DATE2 >= (
    SELECT
        t5.SDATE
    FROM
        T5 t5
    WHERE
        t5.OH_ID = '1234'
)
AND t3.CODE IN (
    SELECT
        t6.CODE
    FROM
        T6 t6
    WHERE
        t6.RID = (
            SELECT
                t5.BID
            FROM
                T5 t5
            WHERE
                t5.OH_ID = '1234'
        )
)
AND (
    (t4.STATUS IN('10', '60'))
    OR (
        (
            t4.STATUS IN ('40', '50', '70')
        )
        AND t1.HID = t2.HID
        AND t4.PID = t1.PID
        AND t2.C_DATE < (
            SELECT
                t5.SDATE
            FROM
                T5 t5
            WHERE
                t5.OH_ID = '1234'
        )
    )
)
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • lstmsa 2018-11-09 01:27
    已采纳

    以下,看有没有达到你想要的效果。另外,t1、t2在t4非40、50、70时没有捆绑条件,我不知道那样是否有意义,如果没有参考意义,还可以再优化省略掉带出t1、t2。

    SELECT
    t1.C1,
    t3t4.C2,
    t3t4.C3,
    t3t4.C4,
    t2.C5,
    t2.C6,
    t2.C_DATE,
    t3t4.C7,
    t3t4.C_DATE2 FROM
    T1 t1,
    T2 t2,
    (select t3.C2,t3.C3,t3.C4,t3.C_DATE2,t3.C7,t3.C_DATE2,t3.CODE,t4.STATUS,t4.PID from T3 t3 inner join
    T4 t4 on t4.FID = t3.FID where t4.STATUS IN('10', '60', '40', '50', '70')) t3t4,
    (SELECT EDATE,SDATE,BID FROM T5 WHERE OH_ID = '1234') t5 WHERE
    t3t4.C_DATE2 <= t5.EDATE
    AND t3t4.C_DATE2 >= t5.SDATE
    AND t3t4.CODE IN (
    SELECT
    t6.CODE
    FROM
    T6 t6
    WHERE
    t6.RID = t5.BID
    )
    AND (
    (t3t4.STATUS IN('10', '60'))
    OR (
    t1.HID = t2.HID
    AND t3t4.PID = t1.PID
    AND t2.C_DATE < t5.SDATE
    )
    )



    SELECT
    null,
    t3t4.C2,
    t3t4.C3,
    t3t4.C4,
    null,
    null,
    null,
    t3t4.C7,
    t3t4.C_DATE2 FROM
    --T1 t1,
    --T2 t2,
    (select t3.C2,t3.C3,t3.C4,t3.C_DATE2,t3.C7,t3.C_DATE2,t3.CODE,t4.STATUS,t4.PID from T3 t3 inner join
    T4 t4 on t4.FID = t3.FID where t4.STATUS IN('10', '60')) t3t4,
    (SELECT EDATE,SDATE,BID FROM T5 WHERE OH_ID = '1234') t5 WHERE
    t3t4.C_DATE2 <= t5.EDATE
    AND t3t4.C_DATE2 >= t5.SDATE
    AND t3t4.CODE IN (
    SELECT
    t6.CODE
    FROM
    T6 t6
    WHERE
    t6.RID = t5.BID
    )

    UNION

    SELECT
    t1.C1,
    t3t4.C2,
    t3t4.C3,
    t3t4.C4,
    t2.C5,
    t2.C6,
    t2.C_DATE,
    t3t4.C7,
    t3t4.C_DATE2 FROM
    T1 t1,
    T2 t2,
    (select t3.C2,t3.C3,t3.C4,t3.C_DATE2,t3.C7,t3.C_DATE2,t3.CODE,t4.STATUS,t4.PID from T3 t3 inner join
    T4 t4 on t4.FID = t3.FID where t4.STATUS IN('40', '50', '70')) t3t4,
    (SELECT EDATE,SDATE,BID FROM T5 WHERE OH_ID = '1234') t5 WHERE
    t3t4.C_DATE2 <= t5.EDATE
    AND t3t4.C_DATE2 >= t5.SDATE
    AND t3t4.CODE IN (
    SELECT
    t6.CODE
    FROM
    T6 t6
    WHERE
    t6.RID = t5.BID
    )
    AND t1.HID = t2.HID
    AND t3t4.PID = t1.PID
    AND t2.C_DATE < t5.SDATE

    打赏 评论
  • devmiao 2018-11-07 14:10
    打赏 评论
  • ghq-yes 2018-11-08 01:39

    你把条件查询放到了where后面,会导致每条记录都执行查询语句去查询他的条件,这样是拖慢这条sql执行速度的最大原因,把查询条件作为一个结果表放在表后面,会提高这个sql的执行速度。

    打赏 评论

相关推荐 更多相似问题