Process Workflow GEL Script joining on PPA_TRANSCONTROL table performs slowly

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Performance Issue. The process has gel script which has a SQL to join with 'PPA_TRANSCONTROL' table is causing the query to take a lot of time.  Even though this table doesn’t have more than 10k rows, still the query is most time consuming. The query ran faster after creating an index on the 'EXTERNAL_ID' column in the 'PPA_TRANSCONTROL' table. This is a request to add the index as part of product on the PPA_TRANSCONTROL table. 

Steps to Reproduce:

1. Create a BPM Process Workflow with a GEL Script that joins to the PPA_TRANSCONTROL table 
2. Validate the process and execute it 
3. Observe the amount of time the workflow is processed 

Expected Results: Joining to the PPA_TRANSCONTROL table in a process should not cause performance issues. 

Actual Results: A BPM Process Workflow with a GEL Script joining to the PPA_TRANSCONTROL table is performing slower than expected. 

Cause

DE65126 - This is a request to add an index on the EXTERNAL_ID field to the PPA_TRANSCONTROL table to avoid possible performance issues. 

Workaround: Create an index on the 'EXTERNAL_ID' field. 

Environment

Release: 15.9.2, 15.9.3, 16.0.0, 16.0.2 

Resolution

Targeted Fix in Release 16.0.3