记一次SQL优化经验分享(达梦数据库)

优化前:

优化后:


执行耗时由0.96变为0.03,效率提升32倍;

优化内容:

  1. 单位反馈人由子查询改为加入到多表查询
  2. T1子表语句,(主表+明细表)多表查询后group by 改为主表+(明细表group数据库原理 by);

原语句:

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