H2数据库两个派生表的内部联接需要47秒 Download


我正在通过服务器连接使用H2数据库。数据库中的每个记录都有一个INTEGER APPLICATION_CODE字段，一个VARCHAR LOG_ENTRY_TIME字段和一个INTEGER STATUS_CODE字段。我需要找出每月每个APPLICATION_CODE最常出现的STATUS_CODE。如果两个计数相等，我只想返回其中一个。以下SQL可以运行，但是执行需要47秒。

编辑：将INNER JOIN更改为RIGHT JOIN可以将执行时间减少到47秒，这虽然更好，但仍然太长。我已经更新了问题信息，以使用RIGHT JOIN。

SELECT  MAX(STATUS_COUNT_TABLE.STATUS) AS MONTH_STATUS,
        STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE,
        FORMATDATETIME(DATEADD(MONTH, -1 * STATUS_COUNT_TABLE.MONTH_RANGE, CURRENT_DATE),'MMM-yyyy') AS MONTH_DATE

FROM    (
        SELECT  INNER_STATUS_COUNT_TABLE.STATUS AS STATUS, 
                MAX(INNER_STATUS_COUNT_TABLE.STATUS_COUNT) AS STATUS_COUNT, 
                INNER_STATUS_COUNT_TABLE.MONTH_RANGE AS MONTH_RANGE, 
                INNER_STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE
        FROM   ( 
                SELECT  
                    STATUS_CODE AS STATUS,
                    APPLICATION_CODE,
                    COUNT(*) AS STATUS_COUNT,
                    DATEDIFF(MONTH, LOG_ENTRY_TIME, CURRENT_DATE) MONTH_RANGE
                FROM MMIT_DASHBRD_APLCTN_LOG 
                WHERE LOG_ENTRY_TIME  >= DATEADD(MONTH, -12, CURRENT_DATE)
                GROUP BY STATUS_CODE, MONTH_RANGE, APPLICATION_CODE
                ) AS INNER_STATUS_COUNT_TABLE 
        GROUP BY  INNER_STATUS_COUNT_TABLE.MONTH_RANGE, INNER_STATUS_COUNT_TABLE.APPLICATION_CODE, INNER_STATUS_COUNT_TABLE.STATUS
    ) AS STATUS_COUNT_TABLE
    RIGHT JOIN  (
                    SELECT  MAX(INNER_STATUS_COUNT_TABLE.STATUS_COUNT) AS STATUS_COUNT,  
                            INNER_STATUS_COUNT_TABLE.MONTH_RANGE AS MONTH_RANGE,    
                            INNER_STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE
                    FROM   ( 
                            SELECT  
                                STATUS_CODE AS STATUS,
                                APPLICATION_CODE,
                                COUNT(*) AS STATUS_COUNT,
                                DATEDIFF(MONTH, LOG_ENTRY_TIME, CURRENT_DATE) MONTH_RANGE
                            FROM MMIT_DASHBRD_APLCTN_LOG 
                            WHERE LOG_ENTRY_TIME  >= DATEADD(MONTH, -12, CURRENT_DATE)
                            GROUP BY STATUS_CODE, MONTH_RANGE, APPLICATION_CODE
                            ) AS INNER_STATUS_COUNT_TABLE 
                    GROUP BY  INNER_STATUS_COUNT_TABLE.MONTH_RANGE, INNER_STATUS_COUNT_TABLE.APPLICATION_CODE
                ) AS MAX_STATUS_COUNT_TABLE
    ON STATUS_COUNT_TABLE.STATUS_COUNT = MAX_STATUS_COUNT_TABLE.STATUS_COUNT 
        AND STATUS_COUNT_TABLE.MONTH_RANGE = MAX_STATUS_COUNT_TABLE.MONTH_RANGE
        AND STATUS_COUNT_TABLE.APPLICATION_CODE = MAX_STATUS_COUNT_TABLE.APPLICATION_CODE
GROUP BY STATUS_COUNT_TABLE.MONTH_RANGE, STATUS_COUNT_TABLE.APPLICATION_CODE

我要连接的两个派生表在单独运行它们时都花费不到一秒钟的时间来执行。我读到了可以使用EXPLAIN命令查找瓶颈的地方，并获得了以下提示，但我不知道如何使用结果来查找瓶颈。

SELECT
MAX(STATUS_COUNT_TABLE.STATUS) AS MONTH_STATUS,
STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE,
FORMATDATETIME(DATEADD('MONTH', (-1 * STATUS_COUNT_TABLE.MONTH_RANGE), CURRENT_DATE()), 'MMM-yyyy') AS MONTH_DATE
FROM (
SELECT
    MAX(INNER_STATUS_COUNT_TABLE.STATUS_COUNT) AS STATUS_COUNT,
    INNER_STATUS_COUNT_TABLE.MONTH_RANGE AS MONTH_RANGE,
    INNER_STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE
FROM (
    SELECT
        STATUS_CODE AS STATUS,
        APPLICATION_CODE,
        COUNT(*) AS STATUS_COUNT,
        DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()) AS MONTH_RANGE
    FROM PUBLIC.MMIT_DASHBRD_APLCTN_LOG
    WHERE LOG_ENTRY_TIME >= DATEADD('MONTH', -12, CURRENT_DATE())
    GROUP BY STATUS_CODE, DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()), APPLICATION_CODE
) INNER_STATUS_COUNT_TABLE
GROUP BY INNER_STATUS_COUNT_TABLE.MONTH_RANGE, INNER_STATUS_COUNT_TABLE.APPLICATION_CODE
) MAX_STATUS_COUNT_TABLE
/* SELECT
    MAX(INNER_STATUS_COUNT_TABLE.STATUS_COUNT) AS STATUS_COUNT,
    INNER_STATUS_COUNT_TABLE.MONTH_RANGE AS MONTH_RANGE,
    INNER_STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE
FROM (
    SELECT
        STATUS_CODE AS STATUS,
        APPLICATION_CODE,
        COUNT(*) AS STATUS_COUNT,
        DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()) AS MONTH_RANGE
    FROM PUBLIC.MMIT_DASHBRD_APLCTN_LOG
    WHERE LOG_ENTRY_TIME >= DATEADD('MONTH', -12, CURRENT_DATE())
    GROUP BY STATUS_CODE, DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()), APPLICATION_CODE
) INNER_STATUS_COUNT_TABLE
    /++ SELECT
        STATUS_CODE AS STATUS,
        APPLICATION_CODE,
        COUNT(*) AS STATUS_COUNT,
        DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()) AS MONTH_RANGE
    FROM PUBLIC.MMIT_DASHBRD_APLCTN_LOG
        /++ PUBLIC.MMIT_DASHBRD_APLCTN_LOG.tableScan ++/
    WHERE LOG_ENTRY_TIME >= DATEADD('MONTH', -12, CURRENT_DATE())
    GROUP BY STATUS_CODE, DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()), APPLICATION_CODE
     ++/
GROUP BY INNER_STATUS_COUNT_TABLE.MONTH_RANGE, INNER_STATUS_COUNT_TABLE.APPLICATION_CODE
 */
LEFT OUTER JOIN (
SELECT
    INNER_STATUS_COUNT_TABLE.STATUS AS STATUS,
    MAX(INNER_STATUS_COUNT_TABLE.STATUS_COUNT) AS STATUS_COUNT,
    INNER_STATUS_COUNT_TABLE.MONTH_RANGE AS MONTH_RANGE,
    INNER_STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE
FROM (
    SELECT
        STATUS_CODE AS STATUS,
        APPLICATION_CODE,
        COUNT(*) AS STATUS_COUNT,
        DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()) AS MONTH_RANGE
    FROM PUBLIC.MMIT_DASHBRD_APLCTN_LOG
    WHERE LOG_ENTRY_TIME >= DATEADD('MONTH', -12, CURRENT_DATE())
    GROUP BY STATUS_CODE, DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()), APPLICATION_CODE
) INNER_STATUS_COUNT_TABLE
GROUP BY INNER_STATUS_COUNT_TABLE.MONTH_RANGE, INNER_STATUS_COUNT_TABLE.APPLICATION_CODE, INNER_STATUS_COUNT_TABLE.STATUS
) STATUS_COUNT_TABLE
/* SELECT
    INNER_STATUS_COUNT_TABLE.STATUS AS STATUS,
    MAX(INNER_STATUS_COUNT_TABLE.STATUS_COUNT) AS STATUS_COUNT,
    INNER_STATUS_COUNT_TABLE.MONTH_RANGE AS MONTH_RANGE,
    INNER_STATUS_COUNT_TABLE.APPLICATION_CODE AS APPLICATION_CODE
FROM (
    SELECT
        STATUS_CODE AS STATUS,
        APPLICATION_CODE,
        COUNT(*) AS STATUS_COUNT,
        DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()) AS MONTH_RANGE
    FROM PUBLIC.MMIT_DASHBRD_APLCTN_LOG
    WHERE LOG_ENTRY_TIME >= DATEADD('MONTH', -12, CURRENT_DATE())
    GROUP BY STATUS_CODE, DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()), APPLICATION_CODE
) INNER_STATUS_COUNT_TABLE
    /++ SELECT
        STATUS_CODE AS STATUS,
        APPLICATION_CODE,
        COUNT(*) AS STATUS_COUNT,
        DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()) AS MONTH_RANGE
    FROM PUBLIC.MMIT_DASHBRD_APLCTN_LOG
        /++ PUBLIC.MMIT_DASHBRD_APLCTN_LOG.tableScan ++/
    WHERE LOG_ENTRY_TIME >= DATEADD('MONTH', -12, CURRENT_DATE())
    GROUP BY STATUS_CODE, DATEDIFF('MONTH', LOG_ENTRY_TIME, CURRENT_DATE()), APPLICATION_CODE
     ++/
GROUP BY INNER_STATUS_COUNT_TABLE.MONTH_RANGE, INNER_STATUS_COUNT_TABLE.APPLICATION_CODE, INNER_STATUS_COUNT_TABLE.STATUS
 */
ON (STATUS_COUNT_TABLE.APPLICATION_CODE = MAX_STATUS_COUNT_TABLE.APPLICATION_CODE)
AND ((STATUS_COUNT_TABLE.STATUS_COUNT = MAX_STATUS_COUNT_TABLE.STATUS_COUNT)
AND (STATUS_COUNT_TABLE.MONTH_RANGE = MAX_STATUS_COUNT_TABLE.MONTH_RANGE))
GROUP BY STATUS_COUNT_TABLE.MONTH_RANGE, STATUS_COUNT_TABLE.APPLICATION_CODE
ORDER BY 2, =STATUS_COUNT_TABLE.MONTH_RANGE

当我分别运行第一个派生表时，我会得到101条看起来像这样的记录。

enter image description here

分别运行第二个派生表时，我得到65条记录，如下所示。

enter image description here

0 个答案:

没有答案



Similar searches