Per Period Metrics Slow Performance in SQL Server

book

Article ID: 209320

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Areas of the Modern UX that use sqlcurve (Per-Period Metrics) are showing slow performance especially if Remaining Availability is added. This includes Staff, Assignments, and Financials. For Remaining Availability, it can take around 5 minutes to load the staff.

STEPS TO REPRODUCE: 

  1. Create a project with a team members
  2. On the Staff Tab, select Remaining Availability in Per-Period Metrics
  3. Set Periods = Months
  4. Start Period: January 2020
  5. End Period: February 2022

Expected Results: Results return in a reasonable amount of time

Actual Results: Page hangs for about 5 minutes saying “Loading data..Please wait”

Workaround: None aside from waiting for the data to return

For other SQL Curve fields, you may see a slow down as more columns (periods and per-period Metrics) are added, though not as significant as that of Remaining Availability. Example, add Total Usage, Actuals, Remaining Allocation, Actuals (without Remaining Availability) in Per-Period Metrics.

The issue doesn't happen for Allocation and Hard Allocation.

Cause

This is caused by: DE59892

Environment

Release : 15.9.1

Component : CA PPM RESOURCE MANAGEMENT

Database: MS SQL Server

Resolution

This issue is under review as: DE59892

Workaround: Allow some time for the results to return or reduce the amount of columns shown in the Per-Period Metrics.