Slow Performance On Portlet(Capacity Overview and Role Capacity)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Steps to Reproduce:

1.Go to Home Menu > Resource Management > Capacity Overview > Investment Demand > select OBS

2. Review the timing.

3. The query being ran is:

select odf_q.* , (select top 1 full_name from ( SELECT u.id AS user_id,

u.user_name AS user_name,

u.user_name AS UNIQUE_CODE,

r.id AS resource_id,

r.unique_name AS unique_name,

r.first_name AS first_name,

r.last_name AS last_name,

r.full_name AS full_name,

r.unique_name AS resourceID,

r.person_type AS person_type_id,

l.name AS person_type,

u.user_status_id AS user_status_id,

s.name AS user_status

FROM srm_resources r,

cmn_sec_users u,

cmn_lookups_v l,

cmn_lookups_v s

WHERE u.id = r.user_id

AND 1=1 and 1=1 and 1=1 AND r.IS_HIDDEN = 0

AND r.person_type = l.id

AND l.language_code='en'

AND l.lookup_type='SRM_RESOURCE_TYPE'

AND u.user_status_id = s.id

AND u.IS_HIDDEN = 0

AND s.language_code= 'en'

AND s.lookup_type='SEC_USER_STATUS'

AND 1=1) q_manager_id where q_manager_id.user_id = odf_q.manager_id) manager_id_caption from ( select row_number() over ( order by name asc, odf_pk) odf_row_num, count(*) over (partition by pmd_analytical_partition_by) odf_num_rows , odf_cols.* from ( select 'x' pmd_analytical_partition_by, inv_investments.id odf_pk , inv_investments.schedule_start as schedule_start , (CASE WHEN INV_INVESTMENTS.STATUS = 1 THEN 1 WHEN INV_INVESTMENTS.STATUS = 5 THEN 1 WHEN INV_INVESTMENTS.STATUS = 8 THEN 1 ELSE 0 END) as is_approved , inv_investments.MANAGER_ID as manager_id , inv_investments.schedule_finish as schedule_finish , inv_investments.name COLLATE Latin1_General_CI_AS_KS as name from inv_investments inv_investments JOIN srm_resources CREATED ON CREATED.user_id=inv_investments.created_by JOIN srm_resources UPDATED ON UPDATED.user_id=inv_investments.last_updated_by JOIN odf_ca_inv odf_ca_inv ON odf_ca_inv.id=inv_investments.id where 1 = 1 AND ISNULL((SELECT IS_TEMPLATE FROM INV_PROJECTS WHERE INV_PROJECTS.PRID = INV_INVESTMENTS.ID), 0) = 0and inv_investments.ID in ( select pt.prprojectid from PRJ_OBS_UNITS OBS, PRJ_OBS_UNITS_FLAT FLAT, SRM_RESOURCES res1, prteam pt

WHERE FLAT.BRANCH_UNIT_ID = OBS.ID

AND OBS.ID = 5068248 AND pt.prresourceid = res1.id AND (( pt.RSF_OBSUNIT_ID IN (SELECT UNIT_ID FROM PRJ_OBS_UNITS_FLAT UF WHERE BRANCH_UNIT_ID = 5068248) AND pt.RSF_OBSUNIT_ID = FLAT.UNIT_ID) OR (pt.RSF_OBSUNIT_ID IS NULL AND

(

res1.ID IN

(

SELECT

DISTINCT OA.RECORD_ID

FROM PRJ_OBS_ASSOCIATIONS OA, PRJ_OBS_UNITS_FLAT UF

WHERE OA.TABLE_NAME = 'SRM_RESOURCES'

AND OA.UNIT_ID = UF.UNIT_ID

AND UF.BRANCH_UNIT_ID = xxxxxxx

AND OA.UNIT_ID = FLAT.UNIT_ID

)

))))

and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and inv_investments.IS_ACTIVE = 1 ) odf_cols ) odf_q where odf_q.odf_row_num < 50001 order by odf_q.odf_row_num

Expected Result: Filtered result should display in seconds

Actual Result: Performance is slow. It takes up to 10 minutes to complete

Resolution

The query will need to by analyzed for a better execution plan by the DBA team.