MUX Project Tiles performance slow on MSSQL

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

On Clarity MSSQL 15.8.1, you experience performance issues when clicking the Project Tile page in the Modern User Experience. This is happening for non-admin users. 

 

Here is the query that is causing the problem:

SELECT total_count, project_id

FROM(

SELECTrow_number()OVER(ORDERBY inv.nameasc, inv.project_id) row_num, count(*)OVER(PARTITIONBY total_projects)  total_count, inv.*

FROM(

 

-- get all projets where I am manager

SELECT distinct 'x' AS total_projects, i.id AS project_id, i.code AS code, i.nameASname

FROM inv_investments i

JOIN inv_projects ipON i.id=ip.prid WHERE i.odf_object_code='project'

AND ip.is_template= 0

AND ip.is_program= 0

AND i.is_active=  1  ) inv WHERE inv.project_id in(SELECT SEC.OBJECT_INSTANCE_ID

FROM CMN_SEC_CHK_USER_V0 SEC, CMN_SEC_OBJECTS O

WHERE SEC.OBJECT_ID= O.ID

AND O.OBJECT_CODEIN('PRJ_PROJECT')

AND SEC.PERMISSION_CODE='prProjectViewer'

AND SEC.USER_ID=  5000000) ) inv2 

WHERE inv2.row_num BETWEEN  1 AND  12

Environment

Release : 15.8.1, 15.9

Component : CLARITY PROJECT MANAGEMENT

Resolution

The issue is no longer reproducible on 15.9.1. 

Additional Information

See also: Projects known issues for Clarity