优化前:
优化后:
执行耗时由0.96变为0.03,效率提升32倍;
优化内容:
原语句:
SELECT
J.ID AS DBRW,
I.ISSIGN ,
I.SIGNDATE ,
(
SELECT DWKFKRY FROM UF_DBKFKRYB WHERE UF_DBKFKRYB.SJLLR = I.DEALER
) AS FKR,
I.PROJID,
I.DEALER
FROM
GOVERN_TASK J ,
GOVERN_OPERATOR I,
(
SELECT
U.SFSQBJ ,
U.TASKID ,
U.SSBM ,
DATEADD(DD, -1, MAX(UDT1.JHLDRQHUANG)) AS ZHLDRQ,
DATEADD(DD, -1, MAX(UDT1.JHXCLDRQHUANG)) AS XCLD
FROM
UF_DBFK U,
UF_DBFK_DT1 UDT1
WHERE
U.ID=UDT1.MAINID(+)
GROUP BY
U.SFSQBJ,
U.TASKID,
U.SSBM
)
T1
WHERE
J.ID = I.TASKID
AND J.ID =T1.TASKID
AND
(
SELECT SUBCOMPANYID1 FROM HRMRESOURCE H WHERE H.ID= I.DEALER
)
=T1.SSBM
AND J.TASKTYPE=0
优化后语句多表查询sql语句:
SELECT
J.ID AS DBRW ,
I.ISSIGN ,
I.SIGNDATE ,
F.DWKFKRY AS FKR,
I.PROJID ,
I.DEALER ,
T1.LDZT
FROM
GOVERN_TASK J ,
GOVERN_OPERATOR I,
(
SELECT
U.SFSQBJ ,
U.TASKID ,
U.SSBM ,
U.LDZT ,
UDT1.ZHLDRQ,
UDT1.XCLD
FROM
UF_DBFK U,
(
SELECT
DT.MAINID ,
DATEADD(DD, -1, MAX(DT.JHLDRQHUANG)) AS ZHLDRQ,
DATEADD(DD, -1, MAX(DT.JHXCLDRQHUANG)) AS XCLD
FROM
UF_DBFK_DT1 DT
GROUP BY
DT.MAINID
)
UDT1
WHERE
U.ID=UDT1.MAINID(+)
)
T1,
UF_DBKFKRYB F
WHERE
J.ID = I.TASKID
AND J.ID =T1.TASKID
AND
(
SELECT SUBCOMPANYID1 FROM HRMRESOURCE H WHERE H.ID= I.DEALER
)
=T1.SSBM
AND J.TASKTYPE =0
AND F.SJLLR(+) = I.DEALER
发表评论