JR38996: FEM QUERY OPTIMIZATION

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The performance problem is related to processing of 2 SQLs -
    first one
    getting count of the records from ACTIVITY and PROCESS_INSTANCE
    views,
    and the second getting actual data from the same views:
    
       1. SELECT  COUNT(DISTINCT PI.PIID ) FROM PRDBPEDB.ACTIVITY
    AI,
    PRDBPEDB.PROCESS_INSTANCE PI WHERE (AI.PIID = PI.PIID) AND
    (PI.STATE IN
    (? ,? )OR AI.STATE IN (? )) WITH UR
       2. SELECT DISTINCT PI.PIID , PI.COMPLETED , PI.STATE ,
    PI.TEMPLATE_NAME ,PI.STARTER ,PT.APPLICATION_NAME ,PI.STARTED
    FROM
    PRDBPEDB.ACTIVITY AI, PRDBPEDB.PROCESS_INSTANCE PI,
    PRDBPEDB.PROCESS_TEMPLATE PT WHERE (AI.PIID = PI.PIID AND
    PI.PTID =
    PT.PTID) AND (PI.STATE IN (? ,? )OR AI.STATE IN (? )) ORDER BY
    PI.COMPLETED  DESC FETCH FIRST 1000 ROWS ONLY WITH UR
    
    As the analysis showed, conditions in WHERE clause of the
    queries are
    very selective - they match only about 200 (of 500 thousand)
    records
    from PROCESS_INSTANCE_B_T table, and about 700 (of 22 million)
    records
    from ACTIVITY_INSTANCE_B_T table.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:  Websphere Process Server users who are      *
    *                  using failed event manager with a large     *
    *                  number of Business process failures         *
    ****************************************************************
    * PROBLEM DESCRIPTION: Failed event manager is either very     *
    *                      slow or timesout when querying for a    *
    *                      large number of business process        *
    *                      failures                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The query for business process failures was doing OR
    across 2 tables which was very expensive.
    

Problem conclusion

  • The OR is not required across the 2 tables because the query
    can be split into 2 queries. Each query returns a distinct set
    of results so the results are merged after the query.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR38996

  • Reported component name

    WEB PROC SERVER

  • Reported component ID

    5724L0100

  • Reported release

    610

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-02-28

  • Closed date

    2011-03-14

  • Last modified date

    2011-03-14

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

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

    PM36050

Fix information

  • Fixed component name

    WEB PROC SERVER

  • Fixed component ID

    5724L0100

Applicable component levels

  • R620 PSY

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

WebSphere Process Server

Software version:

6.1

Reference #:

JR38996

Modified date:

2011-03-14

Translate my page

Machine Translation

Content navigation