[JIRAALIGN-1081] "Value Stream Process Step Cycle Time" report takes over 60 seconds to load

Issue Summary

Some customers are reporting a Performance Issue with the "Value Stream Process Step Cycle Time" report, where it takes over a minute to load the page.

Steps to Reproduce

Note: Not all customers are seeing this issue across the board

• Select a Program in tier 1

• Navigate to the Process Step Cycle Time Report

• Select a Value Stream

Expected Results

The Process Step Cycle Time Report loads all graphs within a few seconds.

Actual Results

The Report loads the first bar graph Value Stream Process Step Cycle Time, but, the rest of the page 'spins' for over a minute before it finally loads.

Using Chrome Developer Tools, we see several 504 Gateway Timeouts when attempting to load AjaxValueVisuals?TypeVal=2 thru AjaxValueVisuals?TypeVal=4. These calls last over a minute each.

ASP logs from an affected instance using xxxxx.agilecraft.com source="C:
log
xxxxxx-ASP-20200123.log":, where 'xxxxx' is the redacted name from an actual customer affected by this.

1/23/20
1:36:38.338 PM

Timeouts begin about a minute after the above. The queries in question seem to be RPM_GET_VS_PROCESS_STEPS_WIP_TIMELINE.sql and RPM_GET_VS_PROCESS_STEP_TIMELINE_ROL_AVG, both of which are called in the AjaxValueVisuals.asp code, which is where we are getting bogged down :

1/23/20
1:37:09.157 PM	
{ [-]
   ASP_function: OOFunctionsSql.SetRSParamsCursor
   correlationid: GpFRnybFdV+H402dbXGGcA1A9wI3CoCZoUGMCZTOmqM=
   customer_db: xxxxxx
   error_description: Query timeout expired
   error_id: 10120
   error_number: -2147217871
   error_source: Microsoft SQL Server Native Client 11.0
   log_type: exception
   logfrom_location: Website-ASP
   sql: EXEC RPM_GET_VS_PROCESS_STEPS_WIP_TIMELINE ?, ?, ?
   timestamp: 2020-01-23T14:37:09.157000-05:00
   url: https://xxxxx.agilecraft.com:443/AjaxFiles/AjaxValueVisuals.asp?TypeVal=4
   urlverb: POST
   user: 367
   version: 10.62.0.1240
}


1/23/20
1:37:39.278 PM	
{ [-]
   ASP_function: OOFunctionsSql.SetRSParamsCursor
   correlationid: t8xXb81HwQmfR85kKNmWUOeDokV+GEA52ESvsakLBio=
   customer_db: xxxxxxx
   error_description: Query timeout expired
   error_id: 10121
   error_number: -2147217871
   error_source: Microsoft SQL Server Native Client 11.0
   log_type: exception
   logfrom_location: Website-ASP
   sql: EXEC RPM_GET_VS_PROCESS_STEP_TIMELINE_ROL_AVG ?, ?, ?
   timestamp: 2020-01-23T14:37:39.278000-05:00
   url: https://xxxxxxx.agilecraft.com:443/AjaxFiles/AjaxValueVisuals.asp?TypeVal=5
   urlverb: POST
   user: 367
   version: 10.62.0.1240
}

Workaround

Currently there is no known workaround for this behavior, aside from entering a narrower date range on the report page to reduce the amount of data being retrieved.