Performance issue on MUX Timesheet page when calculating the pending actuals

book

Article ID: 224661

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Slow performance on the Create Timesheet page when adding multiple assignments. The query that is causing the bottleneck includes a summation of the pending actuals.

The below query gets invoked when accessing the timesheet page which was causing the bottleneck

SELECT   A.PRID, SUM(PENDTE.PRACTSUM) AS ACTUALS
 FROM     PRASSIGNMENT A, PRTIMEENTRY PENDTE, PRTIMESHEET TS+
WHERE    A.PRID IN (SELECT PRASSIGNMENTID
FROM PRTIMEENTRY
WHERE PRTIMESHEETID=$1
AND PRASSIGNMENTID IS NOT NULL)
AND      PENDTE.PRASSIGNMENTID = A.PRID
AND      PENDTE.PRTIMESHEETID = TS.PRIDAND      PENDTE.PRACTSUM > 0
AND      TS.PRSTATUS < 4
GROUP BY A.PRID

STEPS TO REPRODUCE: 

  1. Log in to Clarity Modern User Experience (UX)
  2. Navigate to the Timesheet Workspace
  3. Create a timesheet and add several tasks in the timesheet page
  4.  Perform this task with at least 100+ resources logged in at the same time for timesheeting 

Expected Results: There should not be any performance issue in loading the page. 

Actual Results: The more the number of users, the page loading slows and eventually ends up in an outage. 

Cause

 

 

Resolution

Fixed in 15.9.3 patch 1 and 16.0 as DE62417

The query has been rewritten to better perform in the MUX.