IBM Support

JR51642: /REST/BPM/WLE/V1/PROCESSAPPS API HAS POOR PERFORMANCE DUE TO A COMPLEX QUERY THAT IS EXECUTED

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • /rest/bpm/wle/v1/processApps API has poor performance due to a
    complex query that is executed multiple times.  The individual
    query can take 5 to 10 seconds to run and it is executed
    multiple times to complete the task.
    
    The details about the REST API can be found in the infocenter:
    http://www-01.ibm.com/support/knowledgecenter/SSFPJS_8.5.5/com.i
    bm.wbpm.ref.doc/rest/bpmrest/rest_bpm_wle_v1_processapps_get.htm
    
    The query gets the details for all snapshots and process apps in
    the system. I also gets details about the instance counts even
    though they are not part of the REST API output. The query is
    executed multiple times even though it gets all of the data with
    one query.
    
    Frequently the query will slow down due to the number of
    instances in the system.
    
    The following warning message is printed to the SystemOut.log
    file if the call takes more than 60 seconds.
    
     The elapsed time for the following REST request was 65.51
     seconds, which exceeds the warning threshold of 60 seconds:
     http://www.myhost.com:9080/rest/bpm/wle/v1/processApps
    
    
    You may see that multiple EJB calls are made to
    getInstalledSnapshots which contains a slow query.
    .
    .--------------------------------------------------
       This is the query that is run
    select  projects.name as projectName,
             projects.short_name as projectShortName,
             snaps.snapshot_id as snapshotID,
             snaps.branch_id as branchID,
             snaps.name as snapshotName,
             snaps.is_active as isActive,
             snaps.is_default as isDefault,
             snaps.is_installed as isInstalled,
             snaps.is_archived as isArchived,
             branchs.name as branchName,
             snaps.created_on as snapshotDeployedOn,
             bpds.name as bpdName,
             (select count(*) from lsw_bpd_instance i where
    i.execution_status in (1,6) and i.snapshot_id=snaps.snapshot_id
    and
    i.cached_bpd_version_id=bpds.cached_bpd_version_id  and i.tip !=
    'T') as
    instancesRunning,
          (select count(*) from lsw_bpd_instance i where
    i.execution_status=3 and i.snapshot_id=snaps.snapshot_id and
    i.cached_bpd_version_id=bpds.cached_bpd_version_id and i.tip !=
    'T') as
    instancesFailed,
          (select count(*) from lsw_bpd_instance i, lsw_bpd bpd
    where
    i.execution_status in (1,3,6) and
    i.snapshot_id=snaps.snapshot_id and
    i.cached_bpd_version_id=bpd.version_id and i.tip != 'T') as
    snapshotInstancesRunning,
          projects.project_id as projectID
        from lsw_snapshot snaps
          inner join lsw_project projects ON snaps.project_id =
    projects.project_id
          inner join lsw_branch branchs ON snaps.branch_id =
    branchs.branch_id
          left outer join lsw_favorite bpds ON (snaps.snapshot_id =
    bpds.snapshot_id and bpds.item_type = 25 and
    bpds.exposed_process_type =
    0)
        where
          snaps.name is not null
          and projects.is_toolkit='F'
                            and (bpds.tip != 'T' or bpds.tip is
    null)
    
                    order by snapshotDeployedOn DESC, projectName
    ASC,
    snapshotName ASC, bpdName asc
    

Local fix

  • You can use the TWProcessApp javascript object to get similar
    data. You may also want to analyze any slow queries that are
    occurring in your database at the time and add needed indexes.
    The needed indexes could vary depending on your usage pattern.
    

Problem summary

  • This problem will be fixed in next release.
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    JR51642

  • Reported component name

    BPM ADVANCED

  • Reported component ID

    5725C9400

  • Reported release

    850

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-10-29

  • Closed date

    2014-11-04

  • Last modified date

    2015-06-03

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

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

Fix information

Applicable component levels

  • R855 PSY

       UP



Document information

More support for: IBM Business Process Manager Advanced

Software version: 8.5

Reference #: JR51642

Modified date: 03 June 2015