【in - exists-join(distinct)】SQL调优改写一例

最新推荐文章于 2022-11-26 19:36:43 发布

原创最新推荐文章于 2022-11-26 19:36:43 发布 · 339 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #调优 #改写

SqlServer 专栏收录该内容

59 篇文章

订阅专栏

本文通过一个实际案例，详细介绍了如何对包含`in - exists-join(distinct)`的SQL进行调优。通过SQL拆分、改写及增加执行计划提示，成功将查询时间从超过10分钟降低到5秒以内，显著提升了查询效率。

相关数据已经脱敏处理

原SQL

MERGE INTO IntoTable t USING (
    SELECT
        x.PID,
        b.SEC_ID,
        b.T_SYB,
        b.E_CD,
        x.END_DATE,
        x.xh 区间级别,
        isnull(x.hj, 0) 区间人数
    FROM
        (
            SELECT
                a.PID,
                a.END_DATE,
                a.xh,
                c.hj
            FROM
                (
                    SELECT
                        PID,
                        END_DATE,
                        row_number () OVER (
                            PARTITION BY PID,
                            END_DATE
                        ORDER BY
                            END_DATE
                        ) xh
                    FROM
                        ScTable a
                    WHERE
                        EXISTS (
                            SELECT
                                *
                            FROM
                                (
                                    SELECT
                                        PID,
                                        END_DATE,
                                        COUNT(*) hj
                                    FROM
                                        ScTable
                                    GROUP BY
                                        PID,
                                        END_DATE
                                    HAVING
                                        COUNT(*) >= 5
                                ) b
                            WHERE
                                a.PID = b.PID
                            AND a.END_DATE = b.END_DATE
                        )
                    AND EXISTS (
                        SELECT
                            *
                        FROM
                            ScTable c
                        WHERE
                            UPDATE_TIME >= CONVERT (VARCHAR(20), GETDATE() - 1, 23)
                        AND R_TYPE = '5'
                        AND a.PID = c.PID
                        AND a.END_DATE = c.END_DATE
                    )
                    AND a.DR = '1'
                ) a
            LEFT JOIN (
                SELECT
                    PID,
                    END_DATE,
                    jb,
                    COUNT(*) hj
                FROM
                    (
                        SELECT
                            PID,
                            END_DATE,
                            mm,
                            CASE
                        WHEN mm >= 0
                        AND mm <= 200000 THEN
                            5
                        WHEN mm > 200000
                        AND mm <= 500000 THEN
                            4
                        WHEN mm > 500000
                        AND mm <= 1000000 THEN
                            3
                        WHEN mm > 1000000
                        AND mm <= 2000000 THEN
                            2
                        WHEN mm > 2000000 THEN
                            1
                        END jb
                        FROM
                            (
                                SELECT
                                    PID,
                                    END_DATE,
                                    CASE
                                WHEN A_REWARD IS NULL THEN
                                    0
                                ELSE
                                    A_REWARD
                                END mm
                                FROM
                                    ScTable
                                WHERE
                                    DR = '1'
                            ) b
                    ) c
                GROUP BY
                    PID,
                    END_DATE,
                    jb
            ) c ON (
                a.PID = c.PID
                AND a.END_DATE = c.end_date
                AND a.xh = c.jb
            )
            WHERE
                xh <= 5
        ) x,
        (
            SELECT
                *
            FROM
                m_sec
            WHERE
                E_CD IN ('xshe', 'xshg')
            AND A_CLASS = 'e'
            AND D_U_FLG = 1
            AND LEFT (T_SYB, 1) NOT IN ('2', '9')
            AND PID IN (
                SELECT
                    PID
                FROM
                    m_sec
                WHERE
                    E_CD IN ('xshe', 'xshg')
                AND A_CLASS = 'e'
                AND D_U_FLG = 1
                GROUP BY
                    PID
                HAVING
                    COUNT(*) > 1
            )
            UNION
                SELECT
                    *
                FROM
                    m_sec
                WHERE
                    E_CD IN ('xshe', 'xshg')
                AND A_CLASS = 'e'
                AND D_U_FLG = 1
                AND PID IN (
                    SELECT
                        PID
                    FROM
                        m_sec
                    WHERE
                        E_CD IN ('xshe', 'xshg')
                    AND A_CLASS = 'e'
                    AND D_U_FLG = 1
                    GROUP BY
                        PID
                    HAVING
                        COUNT(*) = 1
                )
        ) b
    WHERE
        x.PID = b.PID
    AND b.A_CLASS = 'e'
    AND b.E_CD IN ('xshe', 'xshg')
    AND b.D_U_FLG = '1'
    AND b.DELIST_DATE IS NULL
) r ON (
    t.PID = r.PID
    AND t.END_DATE = r.END_DATE
    AND t.I_LEVEL = r.区间级别
)
WHEN matched
AND t.I_NUMBER <> r.区间人数 THEN
    UPDATE
SET t.I_NUMBER = r.区间人数,
 t.update_by = 'PROGRAM 更新',
 t.update_time = getdate (),
 t.QA_FLG = NULL
WHEN NOT matched THEN
    INSERT (
        PID,
        SEC_ID,
        T_SYB,
        E_CD,
        END_DATE,
        I_LEVEL,
        I_NUMBER,
        DR,
        CREATE_BY,
        CREATE_TIME,
        UPDATE_BY,
        UPDATE_TIME
    )
VALUES
    (
        r.PID,
        r.SEC_ID,
        r.T_SYB,
        r.E_CD,
        r.END_DATE,
        r.区间级别,
        r.区间人数,
        '1',
        'PROGRAM 更新',
        getdate (),
        'PROGRAM 更新',
        getdate ()
    );

step 1 SQL拆分，找出存在效率问题的主体

                    SELECT
                        PID,
                        END_DATE,
                        row_number () OVER (
                            PARTITION BY PID,
                            END_DATE
                        ORDER BY
                            END_DATE
                        ) xh
                    FROM
                        ScTable a
                    WHERE
                        EXISTS (
                            SELECT
                                *
                            FROM
                                (
                                    SELECT
                                        PID,
                                        END_DATE,
                                        COUNT(*) hj
                                    FROM
                                        ScTable
                                    GROUP BY
                                        PID,
                                        END_DATE
                                    HAVING
                                        COUNT(*) >= 5
                                ) b
                            WHERE
                                a.PID = b.PID
                            AND a.END_DATE = b.END_DATE
                        )
                    AND EXISTS (
                        SELECT
                            *
                        FROM
                            ScTable c
                        WHERE
                            UPDATE_TIME >= CONVERT (VARCHAR(20), GETDATE() - 1, 23)
                        AND R_TYPE = '5'
                        AND a.PID = c.PID
                        AND a.END_DATE = c.END_DATE
                    )
                    AND a.DR = '1'

step 2 SQL改写

SELECT a.PID,
                               a.END_DATE,
                               row_number ()
                               OVER (PARTITION BY a.PID, a.END_DATE
                                     ORDER BY a.END_DATE)
                                  xh
                          FROM ScTable a
                       JOIN
                       (SELECT DISTINCT PID, END_DATE
                          FROM ScTable
                         WHERE     UPDATE_TIME >=
                                      CONVERT (VARCHAR (20),
                                               GETDATE () - 1,
                                               23)
                               AND R_TYPE = '5') AS c
                          ON (    a.PID = c.PID
                              AND a.END_DATE = c.END_DATE)
                       JOIN (SELECT PID, END_DATE, COUNT (*) hj
                                    FROM ScTable
                                  GROUP BY PID, END_DATE
                                  HAVING COUNT (*) >= 5) AS b
                          ON (    a.PID = b.PID
                              AND a.END_DATE = b.END_DATE)
                 WHERE a.DR = '1'

Step 3 增加hints更改外表关联方式，以保证内表先执行。

--优化后SQL
MERGE INTO IntoTable t
 USING (SELECT x.PID,
               b.SEC_ID,
               b.T_SYB,
               b.E_CD,
               x.END_DATE,
               x.xh 区间级别,
               isnull (x.hj, 0) 区间人数
          FROM (SELECT a.PID,
                       a.END_DATE,
                       a.xh,
                       c.hj
                  FROM (SELECT a.PID,
                               a.END_DATE,
                               row_number ()
                               OVER (PARTITION BY a.PID, a.END_DATE
                                     ORDER BY a.END_DATE)
                                  xh
                          FROM ScTable a
                       JOIN
                       (SELECT DISTINCT PID, END_DATE
                          FROM ScTable
                         WHERE     UPDATE_TIME >=
                                      CONVERT (VARCHAR (20),
                                               GETDATE () - 1,
                                               23)
                               AND R_TYPE = '5') AS c
                          ON (    a.PID = c.PID
                              AND a.END_DATE = c.END_DATE)
                       JOIN (SELECT PID, END_DATE, COUNT (*) hj
                                    FROM ScTable
                                  GROUP BY PID, END_DATE
                                  HAVING COUNT (*) >= 5) AS b
                          ON (    a.PID = b.PID
                              AND a.END_DATE = b.END_DATE)
                 WHERE a.DR = '1') a
                       LEFT HASH JOIN /*Edit*/
                       (SELECT PID,
                               END_DATE,
                               jb,
                               COUNT (*) hj
                          FROM (SELECT PID,
                                       END_DATE,
                                       mm,
                                       CASE
                                          WHEN mm >= 0 AND mm <= 200000
                                          THEN
                                             5
                                          WHEN mm > 200000 AND mm <= 500000
                                          THEN
                                             4
                                          WHEN mm > 500000 AND mm <= 1000000
                                          THEN
                                             3
                                          WHEN mm > 1000000 AND mm <= 2000000
                                          THEN
                                             2
                                          WHEN mm > 2000000
                                          THEN
                                             1
                                       END
                                          jb
                                  FROM (SELECT PID,
                                               END_DATE,
                                               CASE
                                                  WHEN A_REWARD IS NULL
                                                  THEN
                                                     0
                                                  ELSE
                                                     A_REWARD
                                               END
                                                  mm
                                          FROM ScTable
                                         WHERE DR = '1') b) c
                        GROUP BY PID, END_DATE, jb) c
                          ON (    a.PID = c.PID
                              AND a.END_DATE = c.end_date
                              AND a.xh = c.jb)
                 WHERE xh <= 5) x,
               (SELECT *
                  FROM m_sec
                 WHERE     E_CD IN ('xshe', 'xshg')
                       AND A_CLASS = 'e'
                       AND D_U_FLG = 1
                       AND LEFT (T_SYB, 1) NOT IN ('2', '9')
                       AND PID IN
                              (SELECT PID
                                 FROM m_sec
                                WHERE     E_CD IN ('xshe', 'xshg')
                                      AND A_CLASS = 'e'
                                      AND D_U_FLG = 1
                               GROUP BY PID
                               HAVING COUNT (*) > 1)
                UNION
                SELECT *
                  FROM m_sec
                 WHERE     E_CD IN ('xshe', 'xshg')
                       AND A_CLASS = 'e'
                       AND D_U_FLG = 1
                       AND PID IN
                              (SELECT PID
                                 FROM m_sec
                                WHERE     E_CD IN ('xshe', 'xshg')
                                      AND A_CLASS = 'e'
                                      AND D_U_FLG = 1
                               GROUP BY PID
                               HAVING COUNT (*) = 1)) b
         WHERE     x.PID = b.PID
               AND b.A_CLASS = 'e'
               AND b.E_CD IN ('xshe', 'xshg')
               AND b.D_U_FLG = '1'
               AND b.DELIST_DATE IS NULL) s
    ON (    t.PID = s.PID
        AND t.END_DATE = s.END_DATE
        AND t.I_LEVEL = s.区间级别)
WHEN MATCHED AND t.I_NUMBER <> s.区间人数
THEN
   UPDATE SET t.I_NUMBER = s.区间人数,
              t.update_by = 'PROGRAM 更新',
              t.update_time = getdate (),
              t.QA_FLG = NULL
WHEN NOT MATCHED
THEN
   INSERT (PID,
           SEC_ID,
           T_SYB,
           E_CD,
           END_DATE,
           I_LEVEL,
           I_NUMBER,
           DR,
           CREATE_BY,
           CREATE_TIME,
           UPDATE_BY,
           UPDATE_TIME)
   VALUES (s.PID,
           s.SEC_ID,
           s.T_SYB,
           s.E_CD,
           s.END_DATE,
           s.区间级别,
           s.区间人数,
           '1',
           'PROGRAM 更新',
           getdate (),
           'PROGRAM 更新',
           getdate ());

/*
警告: 由于使用了本地联接提示，联接次序得以强制实施。
SQL Server 分析和编译时间: 
   CPU 时间 = 1435 毫秒，占用时间 = 3373 毫秒。
表 'ScTable'。扫描计数 68，逻辑读取 65508 次，物理读取 0 次，预读 19 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'IntoTable'。扫描计数 0，逻辑读取 59778 次，物理读取 90 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'm_sec'。扫描计数 40255，逻辑读取 1286085 次，物理读取 6 次，预读 70 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 5179 毫秒，占用时间 = 4065 毫秒。

(12 行受影响)
*/

执行时间从>10min缩短到5s内。