Performance of Modern User Experience (UX) 'My Timesheet' entry page is slow while having Timesheets - Navigate rights
Steps to Reproduce:
Expected Results: The loading of the page should be fast when multiple user try to login and access My Timesheet Page
Actual Results: The performance of the query is fast when one user is accessing, however when more than 50 user try to access My Timesheet at the same time, the query gets slower and query retrieves huge data into shared buffers and was doing sorts on retrieved data
with boundaries as ( select prid, prevr, nextr from ( select prid, prstart, prfinish, prisopen, lead( prfinish, 4, to_date(‘2150-01-01’, ‘YYYY-MM-DD’)::timestamp ) over ( order by prfinish ) as nextr, lag( prfinish, 4, to_date(‘1970-01-01’, ‘YYYY-MM-DD’)::timestamp ) over ( order by prfinish ) as prevr from prtimeperiod order by prfinish desc ) q where 1 = 1 and prstart <= to_date(‘2021-09-01’, ‘YYYY-MM-DD’) limit 1 ), timeperiods as ( select tp.prid, tp.prstart, tp.prfinish, tp.prisopen, b.prid as selected_id from prtimeperiod tp, boundaries b where tp.prfinish between b.prevr and b.nextr ), statuses as ( select ts.prid, ts.prstatus, ts.prtimeperiodid, ts.prresourceid, ts.prmodtime, s.resource_type, CASE WHEN s.resource_type < 2 THEN COALESCE(te_alias.total, 0) / 3600.0 ELSE COALESCE(te_alias.total, 0) END as total, te_alias.total as raw_total from prtimesheet ts left outer join tim_timesheet_act_sum_v te_alias on ts.prid = te_alias.prtimesheetid, srm_resources s where 1 = 1 and ts.prresourceid = s.id and s.id = 5063401 ) select tp.prid id, tp.prstart start_date, tp.prfinish finish_date, tp.prisopen is_active, tp.selected_id, COALESCE(statuses.prstatus, 0) as prstatus, statuses.prid as timesheet_id, statuses.prmodtime, COALESCE(statuses.total, 0) as total, CASE WHEN statuses.raw_total is not null THEN ‘true’ ELSE ‘false’ END as has_entries from timeperiods tp left outer join statuses on tp.prid = statuses.prtimeperiodid where 1 = 1 order by start_date, timesheet_id desc
Release : 15.9.2, 15.9.3
Component : CLARITY TIME MANAGEMENT
Database: PostgreSQL
DE62407
Workaround: Submit timesheets in batches and not all users at once.