一条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个回答

以下,看有没有达到你想要的效果。另外,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

weixin_43630445
杜少陵 回复lstmsa: SELECT null, t3t4.C2, t3t4.C3, t3t4.C4, null, null, null, t3t4.C7, t3t4.C_DATE2 --查出来结果第1,5,6,7列都成null了, 感觉还是哪里不对.
一年多之前 回复
lstmsa
lstmsa 回复weixin_43630445: t4的status值在10和60时,t1t2在我看来没有意义,它们根本没有纳入判断条件当中而造成机械性堆叠,修改了让t4 status为10、60时t1t2注释掉,并加入union合并,你再试一下
一年多之前 回复
weixin_43630445
杜少陵 回复lstmsa: 有索引的,这句执行没问题.
一年多之前 回复
lstmsa
lstmsa 回复weixin_43630445: t3,t4的FID有无索引?t4的status呢?你可以先执行一下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')这句,我估计是这里的问题
一年多之前 回复
weixin_43630445
杜少陵 谢谢。执行了下还是慢, 看不出来是哪个条件的问题, 感觉还是有笛卡尔积存在..
一年多之前 回复

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

weixin_43630445
杜少陵 把查询条件作为一个结果表放在表后面,有点不太理解,请详细说明下,谢谢
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问