After upgrading CA PPM to 15.4, the IDEA create page was taking more than 3 minutes to save. This page used to take 2-3 seconds to save before the upgrade.
The performance issue is happening when the page is not populated with all the required fields.
After performing an action trace on this page, the below query was taking 46 - 50 seconds and it was executed 6 times with a single save.
select * from (select row_number() over ( order by last_name asc) row_num, count(*) over () num_rows, q.* from ( SELECT
, MAIN.LAST_NAME LAST_NAME
, MAIN.FIRST_NAME FIRST_NAME
, MAIN.FULL_NAME FULL_NAME
, MAIN.UNIQUE_NAME UNIQUE_NAME
, MAIN.UNIQUE_CODE UNIQUE_CODE
, MAIN.LAST_UPDATED_DATE LAST_UPDATED_DATE
, MAIN.IS_ACTIVE IS_ACTIVE
, MAIN.USER_NAME USER_NAME
, MAIN.PERSON_TYPE_ID PERSON_TYPE_ID
, MAIN.PERSON_TYPE PERSON_TYPE
, MAIN.USER_STATUS_ID USER_STATUS_ID
, MAIN.USER_STATUS USER_STATUS
, MAIN.LANGUAGE_ID LANGUAGE_ID
, MAIN.LANGUAGE_CODE LANGUAGE_CODE
, R.LAST_NAME LAST_NAME
, R.FIRST_NAME FIRST_NAME
, R.FULL_NAME FULL_NAME
, R.UNIQUE_NAME UNIQUE_NAME
, R.UNIQUE_NAME UNIQUE_CODE
, R.LAST_UPDATED_DATE LAST_UPDATED_DATE
, R.IS_ACTIVE IS_ACTIVE
, U.USER_NAME USER_NAME
, R.PERSON_TYPE PERSON_TYPE_ID
, L.NAME PERSON_TYPE
, U.USER_STATUS_ID USER_STATUS_ID
, S.NAME USER_STATUS
, LANG.ID LANGUAGE_ID
, LANG.LANGUAGE_CODE LANGUAGE_CODE
INNER JOIN CMN_SEC_USERS U
INNER JOIN CMN_LOOKUPS_V L
ON R.person_type = L.id
AND L.language_code = 'en' /* force to english due to DWH performance loads all languages */
INNER JOIN CMN_LOOKUPS_V S
ON U.user_status_id = s.id
AND S.language_code = L.language_code
INNER JOIN CMN_LANGUAGES LANG
ON LANG.LANGUAGE_CODE = L.LANGUAGE_CODE
case when R.user_id is null then 0 else R.user_id end != -99
AND 1=? and 1=1 and 2 = 2 ) q) q where q.row_num < 50001 order by q.row_num
setInt(): 1, 1
With the help of DBA, it was found that he execution plan of this query was not correct. DBA did correct the execution plan of the query and since then the page was responding in a couple of seconds.
If this issue occurs against a PPM SAAS environment, please log an issue with Broadcom Support for this to be reviewed against the database.
Note that the Support engineer may need to confirm that it is this problem, and not a similar one, that has been encountered before processing this request.