Performance of Modern UX Timesheet Grid Layout Slow - PostgreSQL only

book

Article ID: 223110

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

DESCRIPTION FOR RELEASE NOTES:

SUMMARY: Performance of modern ux timesheet grid is slow when updated by columns is added in the grid view 

STEPS TO REPRODUCE: 

  1. Login to Clarity Modern UX 
  2. Navigate to Timesheet Workspace
  3. On the timesheet grid, navigate to columns and add updated by column
  4. Filter for timesheet data 

Expected Results: The dataset fetched on the grid should be fast 

Actual Results: The dataset fetched takes longer time when updated by columns is added to the grid. Below query executed in the grid view and column PRMODBY is seen as part of select query

select odf_q.*
from (
  select row_number() over (
    order by resource_full_name COLLATE "en-x-icu",
     tp_start_date,
     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,
     prtimesheet.prid odf_pk,
     COALESCE(PRTIMESHEET.PRTIMEPERIODID, PRTIMEPERIOD.PRID) as timeperiodid,
     COALESCE(PRTIMESHEET.PRISADJUSTMENT, 0) as prisadjustment,
     prtimesheet.PRUID COLLATE "en-x-icu" as pruid,
     prtimesheet.PRMODTIME as prmodtime,
     SRM_RESOURCES.FULL_NAME COLLATE "en-x-icu" as resource_full_name,
     prtimesheet.PRMODBY as prmodby,
     (
      select full_name
      from (
        SELECT u.id AS user_id,
         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.LAST_UPDATED_DATE AS LAST_UPDATED_DATE
        FROM srm_resources r,
         cmn_sec_users u,
         cmn_lookups s
        WHERE u.id = r.user_id
         AND 1 = 1
         and 1 = 1
         and 1 = 1
         AND u.user_status_id = s.id
         AND u.IS_HIDDEN = 0
         AND case
          when r.user_id is null then 0
          else r.user_id
         end != -99
         AND s.lookup_type = 'SEC_USER_STATUS'
         AND s.lookup_code IN ('ACTIVE', 'LOCK')
       ) q_prmodby
      where q_prmodby.user_id::text = prtimesheet.PRMODBY::text
      limit 1
     ) prmodby_caption, COALESCE(PRTIMESHEET.PRRESOURCEID, SRM_RESOURCES.ID) as resourceid,
     COALESCE(PRTIMESHEET.PRSTATUS, 0) as prstatus,
     (
      select NAME
      from cmn_lookups_v
      where language_code = 'en'
       and lookup_type = 'TIMESHEET_STATUS'
       and LOOKUP_ENUM::bigint = COALESCE(PRTIMESHEET.PRSTATUS, 0)::bigint
      limit 1
     ) as prstatus_caption,
     PRTIMEPERIOD.PRSTART as tp_start_date,
     (
      CASE
       WHEN SRM_RESOURCES.RESOURCE_TYPE < 2 THEN COALESCE(TE_ACTSUM.TOTAL, 0) / 3600.0 / (
        CASE
         WHEN (
          CASE
           WHEN (
            SELECT count(*)
            FROM CMN_OPTION_VALUES V,
             CMN_OPTIONS O
            WHERE V.OPTION_ID = O.ID
             AND O.IS_ACTIVE = 1
             AND O.OPTION_CODE = 'NKT.GEN.DISPLAY_HOURLY'
             AND V.USER_ID = SRM_RESOURCES.ID
           ) = 0 THEN (
            SELECT V.VALUE
            FROM CMN_OPTION_VALUES V,
             CMN_OPTIONS O
            WHERE V.OPTION_ID = O.ID
             AND O.IS_ACTIVE = 1
             AND O.OPTION_CODE = 'NKT.GEN.DISPLAY_HOURLY'
             AND V.USER_ID IS NULL
           )
           ELSE (
            SELECT V.VALUE
            FROM CMN_OPTION_VALUES V,
             CMN_OPTIONS O
            WHERE V.OPTION_ID = O.ID
             AND O.IS_ACTIVE = 1
             AND O.OPTION_CODE = 'NKT.GEN.DISPLAY_HOURLY'
             AND V.USER_ID = SRM_RESOURCES.ID
           )
          END
         ) = 'false' THEN (
          COALESCE (
           (
            SELECT c.hours_per_day
            FROM prcalendar c,
             prsite s
            WHERE c.prid = s.prcalendarid
           ),
           1
          )
         )
         ELSE 1
        END
       )
       ELSE COALESCE(TE_ACTSUM.TOTAL, 0)
      END
     ) as v_total,
     MANAGER.FULL_NAME COLLATE "en-x-icu" as res_manager_name,
     SRM_RESOURCES.UNIQUE_NAME COLLATE "en-x-icu" as unique_name,
     (
      select CASE
        WHEN count(note.prid) > 0 THEN 1
        ELSE 0
       END
      from prnote note
      where prtablename = 'PRTimeSheet'
       and prrecordid = PRTIMESHEET.PRID
     ) as has_notes
    from (
      SELECT DISTINCT PR.prid pr_id,
       TP.prid tp_id,
       PR.prisopen PRJRESOPEN
      FROM prtimeperiod TP,
       prj_resources PR
      WHERE -1 <> 1
       AND PR.prtrackmode <> 0
       AND PR.prisrole <> 1
       AND TP.prisopen <> 0
     ) RESTP
     LEFT OUTER JOIN prtimesheet prtimesheet ON (
      RESTP.tp_id = COALESCE(prtimesheet.prtimeperiodid, 0)
      AND RESTP.pr_id = COALESCE(prtimesheet.prresourceid, 0)
     )
     LEFT OUTER JOIN prcalendar PR_CAL ON COALESCE(PRTIMESHEET.PRRESOURCEID, 0) = PR_CAL.PRRESOURCEID
     LEFT OUTER JOIN tim_timesheet_act_sum_v TE_ACTSUM ON COALESCE(PRTIMESHEET.PRID, 0) = TE_ACTSUM.PRTIMESHEETID,
     prtimeperiod prtimeperiod,
     srm_resources srm_resources
     LEFT OUTER JOIN SRM_RESOURCES MANAGER ON MANAGER.USER_ID = SRM_RESOURCES.MANAGER_ID
    where 1 = 1
     and RESTP.tp_id = prtimeperiod.prid
     and srm_resources.id = RESTP.pr_id
     AND (
      srm_resources.date_of_hire IS NULL
      OR srm_resources.date_of_hire < prtimeperiod.prfinish
     )
     AND (
      srm_resources.date_of_termination IS NULL
      OR prtimeperiod.prstart < srm_resources.date_of_termination
     )
     AND (
      RESTP.prjresopen <> 0
      OR COALESCE(prtimesheet.prid, 0) <> 0
     )
     AND srm_resources.is_active = 1
     AND COALESCE(prtimesheet.prtimeperiodid, 0) = 0
     and 1 = 1
     and 1 = 1
     and 1 = 1
     and PRTIMEPERIOD.PRISOPEN = 1
    UNION ALL
    select 'x' pmd_analytical_partition_by,
     prtimesheet.prid odf_pk,
     COALESCE(PRTIMESHEET.PRTIMEPERIODID, PRTIMEPERIOD.PRID) as timeperiodid,
     COALESCE(PRTIMESHEET.PRISADJUSTMENT, 0) as prisadjustment,
     prtimesheet.PRUID COLLATE "en-x-icu" as pruid,
     prtimesheet.PRMODTIME as prmodtime,
     SRM_RESOURCES.FULL_NAME COLLATE "en-x-icu" as resource_full_name,
     prtimesheet.PRMODBY as prmodby,
     (
      select full_name
      from (
        SELECT u.id AS user_id,
         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.LAST_UPDATED_DATE AS LAST_UPDATED_DATE
        FROM srm_resources r,
         cmn_sec_users u,
         cmn_lookups s
        WHERE u.id = r.user_id
         AND 1 = 1
         and 1 = 1
         and 1 = 1
         AND u.user_status_id = s.id
         AND u.IS_HIDDEN = 0
         AND case
          when r.user_id is null then 0
          else r.user_id
         end != -99
         AND s.lookup_type = 'SEC_USER_STATUS'
         AND s.lookup_code IN ('ACTIVE', 'LOCK')
       ) q_prmodby
      where q_prmodby.user_id::text = prtimesheet.PRMODBY::text
      limit 1
     ) prmodby_caption, COALESCE(PRTIMESHEET.PRRESOURCEID, SRM_RESOURCES.ID) as resourceid,
     COALESCE(PRTIMESHEET.PRSTATUS, 0) as prstatus,
     (
      select NAME
      from cmn_lookups_v
      where language_code = 'en'
       and lookup_type = 'TIMESHEET_STATUS'
       and LOOKUP_ENUM::bigint = COALESCE(PRTIMESHEET.PRSTATUS, 0)::bigint
      limit 1
     ) as prstatus_caption,
     PRTIMEPERIOD.PRSTART as tp_start_date,
     (
      CASE
       WHEN SRM_RESOURCES.RESOURCE_TYPE < 2 THEN COALESCE(TE_ACTSUM.TOTAL, 0) / 3600.0 / (
        CASE
         WHEN (
          CASE
           WHEN (
            SELECT count(*)
            FROM CMN_OPTION_VALUES V,
             CMN_OPTIONS O
            WHERE V.OPTION_ID = O.ID
             AND O.IS_ACTIVE = 1
             AND O.OPTION_CODE = 'NKT.GEN.DISPLAY_HOURLY'
             AND V.USER_ID = SRM_RESOURCES.ID
           ) = 0 THEN (
            SELECT V.VALUE
            FROM CMN_OPTION_VALUES V,
             CMN_OPTIONS O
            WHERE V.OPTION_ID = O.ID
             AND O.IS_ACTIVE = 1
             AND O.OPTION_CODE = 'NKT.GEN.DISPLAY_HOURLY'
             AND V.USER_ID IS NULL
           )
           ELSE (
            SELECT V.VALUE
            FROM CMN_OPTION_VALUES V,
             CMN_OPTIONS O
            WHERE V.OPTION_ID = O.ID
             AND O.IS_ACTIVE = 1
             AND O.OPTION_CODE = 'NKT.GEN.DISPLAY_HOURLY'
             AND V.USER_ID = SRM_RESOURCES.ID
           )
          END
         ) = 'false' THEN (
          COALESCE (
           (
            SELECT c.hours_per_day
            FROM prcalendar c,
             prsite s
            WHERE c.prid = s.prcalendarid
           ),
           1
          )
         )
         ELSE 1
        END
       )
       ELSE COALESCE(TE_ACTSUM.TOTAL, 0)
      END
     ) as v_total,
     MANAGER.FULL_NAME COLLATE "en-x-icu" as res_manager_name,
     SRM_RESOURCES.UNIQUE_NAME COLLATE "en-x-icu" as unique_name,
     (
      select CASE
        WHEN count(note.prid) > 0 THEN 1
        ELSE 0
       END
      from prnote note
      where prtablename = 'PRTimeSheet'
       and prrecordid = PRTIMESHEET.PRID
     ) as has_notes
    from prtimesheet prtimesheet
     LEFT OUTER JOIN prcalendar PR_CAL ON COALESCE(PRTIMESHEET.PRRESOURCEID, 0) = PR_CAL.PRRESOURCEID
     LEFT OUTER JOIN tim_timesheet_act_sum_v TE_ACTSUM ON COALESCE(PRTIMESHEET.PRID, 0) = TE_ACTSUM.PRTIMESHEETID,
     prtimeperiod prtimeperiod,
     srm_resources srm_resources
     LEFT OUTER JOIN SRM_RESOURCES MANAGER ON MANAGER.USER_ID = SRM_RESOURCES.MANAGER_ID
    where 1 = 1
     and prtimesheet.prtimeperiodid = prtimeperiod.prid
     and prtimesheet.prresourceid = srm_resources.id
     and 1 = 1
     and 1 = 1
     and 1 = 1
     and PRTIMEPERIOD.PRISOPEN = 1
   ) odf_cols
 ) odf_q
where odf_q.odf_row_num between 1 and 100
order by odf_q.odf_row_num

Cause

This is a defect DE62405 and in review with our engineering team 

Environment

Release : 15.9.2 15.9.3

Component : CLARITY TIME MANAGEMENT

Resolution

The defect is fixed in 15.9.3 Patch 1 

Till the defect is fixed, please use the below workaround

Workaround:

  • Remove the updated by column from the timesheet grid 

OR

  • Remove the timesheet grid module using the blueprint functionality

Attachments