Resource Instance Check Rights Query Slow

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Resource Instance Check Rights Query is slow on large datasets where instance rights are used. During the rights check view cmn_sec_chk_user_v0 is being called and execution is seen at around 2-3 second which ideally should be in milliseconds 

Steps to Reproduce: 

Pre-Requisite: Clarity implementation where instance rights are provided on Resource Booking Rights 

1. Login to Clarity with a Project Manager right
2. Navigate to workspace like Projects, Task, Staffing 

Expected Results: The navigation to workspaces should be relative faster with 2-5 seconds 

Actual Results: The navigation to workspaces is faster for admin and slower for non admin 

• Below queries were seen most expensive in 16.2.1 

• select NVL((select 1
    from dual
    where exists (select pv.object_instance_id
     from cmn_sec_chk_user_v0 pv
     where pv.object_id=664
     and pv.user_id=XXXXX
     and pv.permission_code='ResourceHardBook')), 0) has_hard from dual
  
  select NVL((select 1
    from dual
    where exists (select pv.object_instance_id
     from cmn_sec_chk_user_v0 pv
     where pv.object_id=664
     and pv.user_id=XXXX
     and pv.permission_code='ResourceSoftBook')), 0) 

• Below queries were seen most expensive in 16.2.3

• SELECT case when (select 1
                  from dual
                  where 1 = 0)=1 then 1 else (case when exists (SELECT USER_ID
                                  FROM CMN_SEC_CHK_USER_V0
                                  WHERE USER_ID = XX
                                  AND PERMISSION_CODE = 'ResourceHardBook'
                                  AND OBJECT_ID=664) then 1 else 0 end) end as has_hard, case when (select 1
                  from dual
                  where 1 = 0)=1 then 1 else (case when exists (SELECT USER_ID
                                  FROM CMN_SEC_CHK_USER_V0
                                  WHERE USER_ID = XX
                                  AND PERMISSION_CODE = 'ResourceSoftBook'
                                  AND OBJECT_ID=664) then 1 else 0 end) end as has_soft
  FROM DUAL
  

Environment

Clarity Release: 16.2.1, 16.2.2, 16.2.3 

Cause

DE153707

Resolution

DE153707 is fixed in 16.3.0 (Targeted to release November 2024) and also backported to 16.2.3 patch 1