Resource Instance Check Rights Query Slow
search cancel

Resource Instance Check Rights Query Slow

book

Article ID: 376859

calendar_today

Updated On:

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