Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-12070

Query for updating the removal time for batches performs poorly on MySQL

XMLWordPrintable

      Problem

      The following update query performs poorly on MySQL:

        <update id="updateByteArraysByBatchId"
          parameterType="java.util.Map">
    update ${prefix}ACT_GE_BYTEARRAY
      set REMOVAL_TIME_ = #{removalTime, jdbcType=TIMESTAMP}
      where ID_ IN (
        SELECT JOB_EXCEPTION_STACK_ID_
        FROM ${prefix}ACT_HI_JOB_LOG
        WHERE JOB_DEF_CONFIGURATION_ = #{batchId, jdbcType=VARCHAR}
      )
  </update>

      Solution

      Extend the query with the following WHERE condition:

      AND JOB_EXCEPTION_STACK_ID_ IS NOT NULL

      Reasoning

      Original Query Plan: original-query-plan.json
      Query Plan With NOT NULL condition: query-plan-with-not-null-condition.json

      • The performance of the query with the NOT NULL check has increased by ~ 99 % compared to the original query
      • The NOT NULL condition has the effect that the index ACT_IDX_HI_JOB_LOG_EX_STACK is used which only contains the IDs of Exception Stacktrace Byte Arrays (which is usually null for most of the historic job logs)
      • The original query plan shows that the ACT_IDX_HI_JOB_LOG_JOB_CONF (corresponds idx_ACT_HI_JOB_LOG_JOB_DEF_CONFIGURATION_ in the query plan) is used which only contains JOB_DEF_CONFIGURATION_
      • The property index_condition in the query plan with NOT NULL condition indicates that Index Condition Pushdown Optimization [1] was used which means that the necessary information could be retrieved from the index whereas in the original query plan the whole table needed to be scanned

      Hint

      It could make sense to adjust the query for MySQL only to avoid negative side-effects for other database systems

      [1] https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

        This is the controller panel for Smart Panels app

          1. original-query-plan.json
            2 kB
          2. query-plan-with-not-null-condition.json
            2 kB

              miklas.boskamp Miklas Boskamp
              tassilo.weidner Tassilo Weidner
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: