Staff Grid with Per Period Metrics slow on SQL Server and Group By

Products

• Clarity FedRAMP
• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

On MSSQL in MUX Staffing Grid - Staff, if rows are grouped with Group By the query takes 80-90 sec to return back, it's slow, compared to when Group by is not used. The view contains per period metrics.

Environment

All Supported Clarity Releases with MSSQL

Resolution

1. Test a specific Staffing View and note the response time
2. Connect to the Clarity server and run the commands one by one:
   • In Clarity 16.2.0 and higher
     admin toggle-feature DE71070_SQLCURVE_TOP_1 1 
   • Additionally, in Clarity 16.4.1 and higher
     admin toggle-feature F59069_SQLCURVE_QUERY_START_FINISH 1 
3. Once completed, restart all services
4. Now connect again and test the exact same view again. You may have to reopen it twice as the first time would be the caching after restart
5. If things look good, test any per period metrics in MUX, run Load DWH, ensure values and performance look good

Additional Information

Feature Toggle Information: 

• DE71070_SQLCURVE_TOP_1
  Wraps each auto-generated curve function call to return a single row: MSSQL SELECT TOP 1 * FROM NK_SUM_SLICES_ROW_FCT(...)
• F59069_SQLCURVE_QUERY_START_FINISH
  Adds a date-window filter to the curve WHERE. The dates are substituted per period at execution time