Performance: Staffing grid requests resource availability curve and is role fields when not in summary mode

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Performance of Staffing Grid is slow when resource availability curve and is role fields when not in summary mode

Steps to Reproduce:

1. Login to MUX with appropriate rights and and navigate to the staffing workspace --> Staff tab
2. Remove all TSVs from the grid if it's showing any
3. Group by resource in the grid 

Expected Results: Availcurve and is_role from the resource are not part of the queries returning data

Actual Results: Query in the SQL trace shows Availcurve being summed and is role being returned, sample query for reference 

      <statement requestId="90b4f6a9-714a-4a82-8de5-f13a43d312e4" elapsed="86.000" elapsedSincePriorNode="0.000" elapsedAfterLastNode="1.000" start="12:46:01:633" finish="12:46:01:719" memoryDelta="2623k">

   <execute requestId="90b4f6a9-714a-4a82-8de5-f13a43d312e4" id="STMT-1982521413" elapsed="82.000" elapsedSincePriorNode="3.000" start="12:46:01:636" finish="12:46:01:718" memoryDelta="0k">

          <![CDATA[Select count(1) as grped_obj_insts_count____aggr , max(COALESCE(prresourceid_caption,' ')) as prresourceid_caption, sum(COALESCE(resource____avail_curve__tsscalar,0)) as resource____avail_curve__tsscalar, sum(COALESCE(resource____prisrole,0)) as resource____prisrole____aggr_sum ,prresourceid as prresourceid ,  'x' as pmd_analytical_partition_by , row_number() over ( order by prresourceid) odf_row_num , count(*) over (partition by pmd_analytical_partition_by ) odf_num_rows from ( select odf_q.*  , (select NAME from cmn_lookups_v where language_code = ? and lookup_type = ? and LOOKUP_ENUM::bigint = odf_q.prbooking::bigint limit 1) as prbooking_caption  , (select name from ( SELECT INV.ID AS ID

          ,INV.CODE AS CODE

          ,INV.NAME AS NAME            

          ,CA.ODF_OBJECT_CODE AS OBJECT_CODE

          ,CLS.NAME AS TYPE_NAME

          ,CA.LAST_UPDATED_DATE AS LAST_UPDATED_DATE

          ,CLS.LANGUAGE_CODE AS LANGUAGE_CODE

          ,LANG.ID AS LANGUAGE_ID

          FROM INV_INVESTMENTS INV JOIN ODF_CA_INV CA ON CA.ID = INV.ID

          JOIN ODF_CLASS_V CLS ON CLS.CODE = CA.ODF_OBJECT_CODE

          JOIN CMN_LANGUAGES LANG ON CLS.LANGUAGE_CODE = ?

              AND CLS.LANGUAGE_CODE = LANG.LANGUAGE_CODE

          LEFT OUTER JOIN INV_PROJECTS PRJ ON INV.ID = PRJ.PRID

          WHERE INV.IS_CUSTOM=0

           

           AND 1=? and 1=1 and 1=1) q_prprojectid where q_prprojectid.id = odf_q.prprojectid limit 1 ) prprojectid_caption  , (select prname from ( SELECT  PRJ_RES.PRID AS PRID,

PRJ_RES.PRUID AS UNIQUE_CODE,

SRM_RES.FULL_NAME AS PRNAME,

SRM_RES.LAST_UPDATED_DATE AS LAST_UPDATED_DATE

        FROM    PRJ_RESOURCES PRJ_RES

              , SRM_RESOURCES SRM_RES

        WHERE PRJ_RES.PRISROLE != 0

        AND PRJ_RES.PRID = SRM_RES.ID

        AND 1=1

        AND 1=? and 1=1 and 1=1) q_prroleid where q_prroleid.prid = odf_q.prroleid limit 1 ) prroleid_caption  , (select full_name from ( SELECT SRM_RESOURCES.ID AS ID,

                    SRM_RESOURCES.LAST_NAME AS LAST_NAME,

                    SRM_RESOURCES.FIRST_NAME AS FIRST_NAME,

                    SRM_RESOURCES.FULL_NAME AS FULL_NAME,

                    SRM_RESOURCES.UNIQUE_NAME AS UNIQUE_NAME,

                    SRM_RESOURCES.UNIQUE_NAME AS UNIQUE_CODE,

                    PRJ_RESOURCES.PRPRIMARYROLEID AS PRPRIMARYROLEID,

                    ROLES.UNIQUE_NAME AS ROLE_UNIQUE_NAME,

                    ROLES.FULL_NAME AS ROLE_NAME,

SRM_RESOURCES.LAST_UPDATED_DATE AS LAST_UPDATED_DATE

                    FROM SRM_RESOURCES JOIN PRJ_RESOURCES ON SRM_RESOURCES.ID=PRJ_RESOURCES.PRID

                      LEFT OUTER JOIN SRM_RESOURCES ROLES ON ROLES.ID = PRJ_RESOURCES.PRPRIMARYROLEID

                    WHERE 1=1

                    AND 1=? and 1=1 and 1=1) q_prresourceid where q_prresourceid.id = odf_q.prresourceid limit 1 ) prresourceid_caption  , (select investment_type_name from ( SELECT   o.code AS investment_type_code,

         c.name AS investment_type_name,

c.last_updated_date AS last_updated_date,

lang.id AS language_id,

lang.language_code AS language_code

FROM     odf_objects o,

         cmn_captions_nls c,

         odf_object_extensions oe,

cmn_languages lang

WHERE    c.pk_id = o.ID

AND      1=? and 1=1 and 1=1 AND      c.table_name = 'ODF_OBJECTS'

AND      c.language_code = ?

AND      oe.extension_code = 'inv'

AND      lang.language_code = c.language_code

AND      lang.language_code = ?

AND      o.is_customizable = 1

AND      oe.object_code = o.code

Environment

Clarity Version 16.1.x, 16.2.x

Cause

DE80897

Resolution

DE80897, above query is optimized and fixed in 16.2.3 (targeted August 2024) and backported to 16.2.2 patch 1