Description:

When you click on the Subproject list it takes several minutes to return results, even though there are one or two subprojects linked.

The subprojects link on the project properties calls the PMD subprojects Flat two times, retrieving every subproject in the system twice regardless of how many subprojects may be on the current project. There are no parameters fed to this PMD to limit the cost of the queries. Example on an environment with 416k subproject entries, this results in 30+ seconds of activity to display the subprojects flat list in the UI.

SELECT SP.PRREFPROJECTID AS ID, T.PRPROJECTID AS PARENT_ID, SP.PRREFPROJECTID as CHILD_ID, SP.PRISREADONLY
FROM PRTASK T, PRSUBPROJECT SP
WHERE T.PRID = SP.PRTASKID
AND SP.PRREFTASKID IS NULL

The above query is called twice.

Steps to Reproduce:

1. Enable SQL Trace Level 7 for a user
2. Login to Clarity as this user
3. Navigate to a project
4. Click ‘Subprojects’ link
5. Logoff and examine the SQL Trace Log file

Expected Result: Projects with few or no subprojects will display this page very quickly in environments with large numbers of rows in PRSUBPROJECT
Actual Result: For every project regardless of subproject count, this page will be exceptionally slow to load in environments with large numbers of rows in PRSUBPROJECT

Solution:

WORKAROUND:
None.

STATUS/RESOLUTION:
Resolved in Clarity 12.1.0 Generic Patch. Reference TEC542313
Resolved in Clarity 12.1.3 Generic Patch. Reference TEC570813
Resolved in Clarity 13.1.0
“Solutions provided by CA Technologies”