时间戳

首页 > 解决方案 > 为什么完全相同的查询在 prod env 中很快，但在 Dev env 中却很慢？

sql - 为什么完全相同的查询在 prod env 中很快，但在 Dev env 中却很慢？

问题描述

我有一个查询在 prod env 中需要 10-11 秒，但是相同的查询在 Dev env 中返回数据需要很长时间。两种环境中的表和索引都是相同的。（Prod 和 Dev 都在同一个 VM 上）两个查询都返回不同的执行计划，显示不同的索引。

Prod Env 选择 V​​MRCTTA1.VMRIACCNT_MC_EXTR_IDX 索引，但 Dev Env 选择 V​​MRCTTA1.VMRRACCNT_MC_EXTR_P（从主键自动生成）索引 生产计划 dsfsfssf

请帮我解决一下这个。

Here is the query:

--INSERT INTO VMRCTTA1."VMRRMC_CD_SUMM"
SELECT
        ACCNT_CNTRY_CD,
        YTD,
        YEAR,
        MONTH,
        ACCNT_GSSN_CD,
        ACCNT_CD,
        ACCNT_MC_CD,
        COALESCE(REVNU,0)AS REVNU_P,
        COALESCE(QTY,0) AS QTY_PC,
        ACCNT_MC_DIV AS MC_DIV
    FROM
    (
        SELECT   ACCNT_CNTRY_CD,
            'Y' AS YTD,
            2019 AS YEAR,
            2 AS MONTH,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            SUM(COALESCE(ACCNT_NET_REVNU,0)) AS REVNU,
            SUM (COALESCE(ACCNT_QTY,0)) AS QTY,
            CASE WHEN ACCNT_MC_DIV = 'P' THEN 'P' WHEN  ACCNT_MC_DIV = 'T' THEN 'V' END AS ACCNT_MC_DIV
        FROM    VMRCTTA1.VMRRACCNT_MC_EXTR ME--,                VMRCTTA1.VMRRMC_CD_SPS_MAPPNG CM
        WHERE
--        ME.ACCNT_MC_CD = CM.MC_CD
            ACCNT_CNTRY_CD = 531
        AND ( ACCNT_YEAR = 2019 AND ACCNT_PERIOD <= 2 )
        AND  ACCNT_MC_DIV IN ('P','T')
        GROUP BY
            ACCNT_CNTRY_CD,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            ACCNT_MC_DIV
    )AS A

UNION

SELECT
    B.ACCNT_CNTRY_CD,
    B.YTD,
    B.YEAR,
    B.MONTH,
    B.ACCNT_GSSN_CD,
    B.ACCNT_CD,
    B.ACCNT_MC_CD,
    B.REVNU,
    B.QTY,
    B.DIV
FROM
(
    SELECT
        ACCNT_CNTRY_CD,
        YTD,
        YEAR,
        MONTH,
        ACCNT_GSSN_CD,
        ACCNT_CD,
        ACCNT_MC_CD,
        MAX(CASE WHEN ACCNT_MC_DIV ='P' THEN  COALESCE(REVNU,0) END) AS REVNU_P,
        MAX(CASE WHEN ACCNT_MC_DIV ='V' THEN  COALESCE(REVNU,0) END) AS REVNU_V,
        MAX(CASE WHEN  ACCNT_MC_DIV ='P' THEN  COALESCE(QTY,0) END) AS QTY_PC,
        MAX(CASE WHEN  ACCNT_MC_DIV ='V' THEN  COALESCE(QTY,0) END) AS QTY_VAN,
        'T' AS MC_DIV
    FROM
    (
        SELECT   ACCNT_CNTRY_CD,
            'Y' AS YTD,
            2019 AS YEAR,
            2 AS MONTH,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            SUM(COALESCE(ACCNT_NET_REVNU,0)) AS REVNU,
            SUM (COALESCE(ACCNT_QTY,0)) AS QTY,CASE WHEN ACCNT_MC_DIV = 'P' THEN 'P' WHEN  ACCNT_MC_DIV = 'T' THEN 'V' END AS ACCNT_MC_DIV
        FROM    VMRCTTA1.VMRRACCNT_MC_EXTR ME--,                VMRCTTA1.VMRRMC_CD_SPS_MAPPNG CM
        WHERE
--        ME.ACCNT_MC_CD = CM.MC_CD
            ACCNT_CNTRY_CD = 531
        AND ( ACCNT_YEAR = 2019 AND ACCNT_PERIOD <= 2 )
        AND  ACCNT_MC_DIV IN ('P','T')
        GROUP BY
            ACCNT_CNTRY_CD,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            ACCNT_MC_DIV
    )AS A
    GROUP BY
    ACCNT_CNTRY_CD,
    YTD,
    YEAR,
    MONTH,
    ACCNT_GSSN_CD,
    ACCNT_CD,
    ACCNT_MC_CD
) AS A,
TABLE
(
VALUES
   (A.ACCNT_CNTRY_CD,A.YTD,A.YEAR,A.MONTH,A.ACCNT_GSSN_CD,A.ACCNT_CD,A.ACCNT_MC_CD,COALESCE(A.REVNU_V,0)+COALESCE(A.REVNU_P,0),COALESCE(A.QTY_PC,0)+COALESCE(A.QTY_VAN,0),'T')
)
AS      B(ACCNT_CNTRY_CD,YTD,YEAR,MONTH,ACCNT_GSSN_CD,ACCNT_CD,ACCNT_MC_CD,REVNU,QTY,DIV)

标签： sqldb2query-optimization

解决方案

Db2 优化器是基于成本的。除了数据模型之外，还考虑了行数、统计信息（由 runstats 收集）、配置和资源来计算成本。例如，Db2 配置包含有关 CPUSpeed 的信息，这也很重要——因此这些值在您的开发、测试和生产环境之间可能会有所不同。

这些差异可能导致不同的成本估算，可能导致不同的访问计划。

推荐阅读