Issue Summary

Server time out when loading Value Stream Cycle time report and adjusting dates

This issue is reproduced on the customer instance where Server error time-out happens (HAR file shows 500 error)when loading the Value Stream Cycle time report and adjusting dates. After selecting the dates, it takes a long time to load and shows an 'Error on server' error. The UI shows a red error "Error on Server". The result is the report does not load fully and keeps spinning and slow.

The issue is seen with only 2 value streams specifically for now in the report by multiple users in the customer instance.  The report takes any time between 2-5 minutes to load. It loads some charts only after a while and shows an internal server error.  It doesn’t load the Cycle time control chart. The issue is seen in multiple browsers.

• On Har, we can see the following error occurring: 

  500 - Internal server error.
  There is a problem with the resource you are looking for, and it cannot be displayed.

• The following line is seen when the error 500 occurs:

  Failed to load resource: the server responded with a status of 500
  AjaxFiles/AjaxValueVisuals?TypeVal=5:1

• HAR also shows 404 error 

Steps to Reproduce

Support is not able to replicate the issue in-house. Following are the steps close to mimic customer steps:

1. Open the Process Flow (Value Stream) Cycle Time report with the option 'What work does my team have in process?'.
2. Set the Top Tier 1 config filter to have Team selected with an agile team 
3. Select a Process Flow in the Process Flow (Value Stream) drop-down menu
4. Change the Data Range (within a year)
5. Wait for the report to load and notice the time-out behavior

Expected Results

The report should load all charts successfully without any error and should not be slow/spinning

Actual Results

The report is not loading all charts and slow. We can see timeout occurring on the page AjaxValueVisuals.asp and seem to be related to Stored Procedure used by this page, when the user selects dates and loads the report.

Per the ASP logs, we can see the following errors in relation to the SQL scripts used, when attempting to load AjaxValueVisuals.asp?TypeVal=5:

{
	"timestamp": "2023-06-16T09:40:54.420000-04:00",
	"version": "10.119.2.29411",
	"log_type": "warning",
	"correlationid": "8eOmmcxWK9kN\/PU7JsgZI4dX0dYz4H5rf1Vi1a75xtc=",
	"logfrom_location": "Website-ASP",
	"customer_db": "AgileCraft_xxxx",
	"user": "16501",
	"url": "https:\/\/xxxx\/AjaxFiles\/AjaxValueVisuals.asp?TypeVal=5",
	"urlverb": "POST",
	"ASP_function": "OOFunctionsSql.LogToSqlTiming",
	"sqlFunction": "SetRSParams",
	"sqlCount": "324",
	"sqlTime": "6026",
	"sql": "EXEC RPM_GET_VS_PROCESS_STEP_TIMELINE_ROL_AVG ?, ?, ?, ?, ?, ?, ?"
}
{
	"timestamp": "2023-06-16T09:42:53.207000-04:00",
	"version": "10.119.2.29411",
	"log_type": "exception",
	"correlationid": "8eOmmcxWK9kN\/PU7JsgZI4dX0dYz4H5rf1Vi1a75xtc=",
	"logfrom_location": "Website-ASP",
	"customer_db": "AgileCraft_xxxxx",
	"user": "16501",
	"url": "https:\/\/xxxx\/AjaxFiles\/AjaxValueVisuals.asp?TypeVal=5",
	"urlverb": "POST",
	"ASP_function": "Error.asp",
	"PageName": "Value Stream Cycle Time",
	"ErrorId": "3718",
	"error_number": "-2147467259",
	"error_description": "Script timed out",
	"error_source": "",
	"error_file": "\/AjaxFiles\/AjaxValueVisuals.asp",
	"error_line": "0",
	"error_column": "-1",
	"error_category": "Active Server Pages",
	"error_aspdescription": "The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.",
	"error_aspcode": "ASP 0113"
} 

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available