JR43262: IN PERFORMANCE DATA WAREHOUSE, PERIODIC QUERY CAN SLOW DOWN OVERALL BPM PERFORMANCE

Version 8.0 Refresh Pack 1 for the IBM Business Process Manager products
Version 7.5.1 Fix Pack 1 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• It has been observed in some customer environments that a
  particular query that runs periodically may take a long time to
  run, slowing down overall Business Process Manager performance.
  
  The query is similar to the following:
  
  select t0.TRACKING_POINT_VALUE_ID,t0.TIME_STAMP,t0.
  STEP_NUMBER,t0.SYSTEM_ID,t0.TRACKING_GROUP_ID,t0.
  TRACKING_POINT_ID,t0.TASK_ID,t0.FUNCTIONAL_TASK_ID,t0.
  SNAPSHOT_ID from LSW_TRACKING_POINT_VALUE t0 where
  TRACKING_POINT_ID in
  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
  ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
  ?,?,?,?,
  ?,?,?,?,?
  ,?,?,?) and FUNCTIONAL_TASK_ID = ? and
  TRACKING_POINT_VALUE_ID not in ([1000 tracking point IDs]) and
  TRACKING_POINT_VALUE_ID not in ([1000 tracking point IDs]) and
  ...
  TRACKING_POINT_VALUE_ID not in ([tracking point IDs])
  order by TIME_STAMP
  
  The more tracking point IDs that are included in the "not in"
  lists, the longer the query takes to execute, and the larger
  the possible impact on overall performance.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:  Users of BPM Express, BPM Standard and BPM  *
  *                  Advanced.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: It has been observed in some customer   *
  *                      environments that a particular query    *
  *                      that runs periodically may take a       *
  *                      long time to run, slowing down overall  *
  *                      Business Process Manager performance.   *
  *                      The query is similar to the following:  *
  *                      select                                  *
  *                      t0.TRACKING_POINT_VALUE_ID,t0.TIME_STAM *
  *                      P,t0.                                   *
  *                      STEP_NUMBER,t0.SYSTEM_ID,t0.TRACKING_GR *
  *                      OUP_ID,t0.                              *
  *                      TRACKING_POINT_ID,t0.TASK_ID,t0.FUNCTIO *
  *                      NAL_TASK_ID,t0.                         *
  *                      SNAPSHOT_ID from                        *
  *                      LSW_TRACKING_POINT_VALUE t0 where       *
  *                      TRACKING_POINT_ID in                    *
  *                      (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, *
  *                      ?,?,?,?,?,?,?,                          *
  *                      ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? *
  *                      ,?,?,?,?,?,?,?,?,?,?,?,?,               *
  *                      ?,?,?,?,                                *
  *                      ?,?,?,?,?                               *
  *                      ,?,?,?) and FUNCTIONAL_TASK_ID = ? and  *
  *                      TRACKING_POINT_VALUE_ID not in ([1000   *
  *                      tracking point IDs]) and                *
  *                      TRACKING_POINT_VALUE_ID not in ([1000   *
  *                      tracking point IDs]) and                *
  *                      ...                                     *
  *                      TRACKING_POINT_VALUE_ID not in          *
  *                      ([tracking point IDs])                  *
  *                      order by TIME_STAMP                     *
  *                      The more tracking point IDs that are    *
  *                      included in the "not in" lists, the     *
  *                      longer the query takes to execute, and  *
  *                      the larger the possible impact on       *
  *                      overall performance.                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Excluding a large number of tracking point IDs in the query
  induces load on the database server which can impact overall
  BPM performance.
  

Problem conclusion

• The problem was addressed by performing the exclude logic in
  the performance server when there are more than 1000 tracking
  point IDs to exclude (i. e. no query will be submitted to the
  database server that excludes more than 1000 tracking point
  IDs via "not in" lists; at most there will only be one list in
  a query as supported databases do not allow more than 1000
  values in an "in" list).
  
  Interim fixes for Business Process Manager Version 7.5.0.1,
  Version 7.5.1.0, and Version 8.0.0.0 have been published to Fix
  Central.  Please refer to the separate file that is
  automatically downloaded with each interim fix for
  prerequisite information and installation/uninstallation
  instructions.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  BPM STANDARD

• Fixed component ID

  5725C9500

Applicable component levels

• R750 PSY

     UP