[JIRAALIGN-1502] Status Reports page takes minutes to load or times out with a 504 Error

{"timestamp":"2020-05-15T14:20:24.520000-04:00","version":"10.70.0.4730","log_type":"warning","correlationid":"Vw0YY9xPAiYxW7y0XFNfgXry5Q0WoJncipdUH\/gz\/Ao=","logfrom_location":"Website-ASP","customer_db":"XXXXXX","user":"109","url":"https:\/\/XXXXXX.agilecraft.com:443\/StatusReports2.asp?ViewT=1&IDPass=1","urlverb":"GET","ASP_function":"OOFunctionsSql.LogToSqlTiming","sqlFunction":"SetRSParams","sqlCount":"955","sqlTime":"28148","sql":"EXEC RPM_GET_STATUS_REPORTS_DATA ' ItemType, HealthSchedule, HealthPeople, HealthBudget, HealthQuality, HealthRisk, FirstName, lastName, EstimateDate, Capitalized, Rank, MMF, ExternalKey, processstep, Split, FeatureGroupID, Actual, Estimate,  CapExActual, CapExLoaded, CompanyID, Name, ProgramID, ProgName, GroupID, StateId, DeliveryState, HealthBudgetDesc, HealthScheduleDesc, HealthPeopleDesc, HealthQualityDesc, HealthRiskDesc, GroupName, targetDate, methodtype,  startDate, OpEx, CapEx, OpExE, CapExE, Value, Budget, HealthID, SavedAt, SavedBy, strategy, DivisionName, ThemeID, Theme, Driver, DriverName, State, IRate, InitialInv, SpendToDate, EstimateAtCompletion, EfficiencyDividend, RevenueAssurance, ROI, FYears, F1, F2, F3, F4, F5,  F6, F7, F8, F9, F10, BlendedHourlyRate, dest_rank, Narration, RateType, FQuarters, FQ1, FQ2, FQ3, FQ4, FQ5, FQ6, FQ7, FQ8, FQ9, FQ10, FMonths, FM1, FM2, FM3, FM4, FM5, FM6, FM7, FM8, FM9, FM10, ForecastType, Active ', 'Select  ItemType, HealthSchedule, HealthPeople, HealthBudget, HealthQuality, HealthRisk, FirstName, lastName, EstimateDate, Capitalized, Rank, MMF, ExternalKey, processstep, Split, FeatureGroupID, Actual, Estimate,  CapExActual, CapExLoaded, CompanyID, Name, ProgramID, ProgName, GroupID, StateId, DeliveryState, HealthBudgetDesc, HealthScheduleDesc, HealthPeopleDesc, HealthQualityDesc, HealthRiskDesc, GroupName, targetDate, methodtype,  startDate, OpEx, CapEx, OpExE, CapExE, Value, Budget, HealthID, SavedAt, SavedBy, strategy, DivisionName, ThemeID, Theme, Driver, DriverName, State, IRate, InitialInv, SpendToDate, EstimateAtCompletion, EfficiencyDividend, RevenueAssurance, ROI, FYears, F1, F2, F3, F4, F5,  F6, F7, F8, F9, F10, BlendedHourlyRate, dest_rank, Narration, RateType, FQuarters, FQ1, FQ2, FQ3, FQ4, FQ5, FQ6, FQ7, FQ8, FQ9, FQ10, FMonths, FM1, FM2, FM3, FM4, FM5, FM6, FM7, FM8, FM9, FM10, ForecastType, Active  from ( Select DISTINCT  1 as ItemType, tblEpicHealthSet.HealthSchedule, tblEpicHealthSet.HealthPeople, tblEpicHealthSet.HealthBudget, tblEpicHealthSet.HealthQuality, tblEpicHealthSet.HealthRisk, sysmemberpermssion.FirstName, sysmemberpermssion.lastName, tblAgileFeatureGroup.EstimateDate, tblAgileFeatureGroup.Capitalized, tblAgileFeatureGroup.Rank, tblAgileFeatureGroup.MMF, tblAgileFeatureGroup.Driver, tblAgileMultiDD.ValueDesc as DriverName, tblAgileFeatureGroup.ExternalKey, tblLifeCycleState.processstep, tblAgileFeatureGroup.Split, tblAgileFeatureGroup.FeatureGroupID,  0 as Actual,  0 as Estimate,  0 as CapExActual,  0 as CapExLoaded,  tblAgileProgram.CompanyID, tblAgileProgram.Name, tblAgileProgram.ProgramID, tblIterationReportGroup.GroupName as ProgName, tblIterationReportGroup.GroupID, tblAgileFeatureGroup.State AS StateId, NULL AS DeliveryState,  tblEpicHealthSet.HealthBudgetDesc, tblEpicHealthSet.HealthScheduleDesc, tblEpicHealthSet.HealthPeopleDesc, tblEpicHealthSet.HealthQualityDesc, tblEpicHealthSet.HealthRiskDesc, tblAgileFeatureGroup.GroupName, tblAgileFeatureGroup.targetDate, tblAgileFeatureGroup.methodtype, tblAgileFeatureGroup.startDate, tblAgileFeatureGroup.OpEx, tblAgileFeatureGroup.CapEx, tblAgileFeatureGroup.OpExE, tblAgileFeatureGroup.CapExE, tblAgileFeatureGroup.Value,    ISNULL(tblAgileFeatureGroup.Budget, 0) as Budget,  tblAgileFeatureGroupHealth.HealthID, tblAgileFeatureGroupHealth.SavedAt, tblAgileFeatureGroupHealth.SavedBy, tblStrategyItem.Description as strategy, tblDivision.DivisionName,  tblAgileTheme.ThemeID, tblAgileTheme.Theme, tblAgileState.StateDesc2 as State, tblAgileFeatureGroup.IRate, tblAgileFeatureGroup.InitialInv, tblAgileFeatureGroup.SpendToDate, tblAgileFeatureGroup.EstimateAtCompletion, tblAgileFeatureGroup.EfficiencyDividend, tblAgileFeatureGroup.RevenueAssurance, tblAgileFeatureGroup.ROI, tblAgileFeatureGroup.FYears, tblAgileFeatureGroup.F1, tblAgileFeatureGroup.F2, tblAgileFeatureGroup.F3, tblAgileFeatureGroup.F4, tblAgileFeatureGroup.F5, tblAgileFeatureGroup.F6, tblAgileFeatureGroup.F7, tblAgileFeatureGroup.F8, tblAgileFeatureGroup.F9, tblAgileFeatureGroup.F10,\ttblAgileFeatureGroup.BlendedHourlyRate  ,RANK() OVER (PARTITION BY tblAgileFeatureGroup.FeatureGroupID ORDER BY tblAgileFeatureGroupHealth.SavedAt DESC) dest_rank, tNarration.Narration, tSetup.CostType as RateType, tblItemForecastFinancials.FQuarters, tblItemForecastFinancials.FQ1, tblItemForecastFinancials.FQ2, tblItemForecastFinancials.FQ3, tblItemForecastFinancials.FQ4, tblItemForecastFinancials.FQ5, tblItemForecastFinancials.FQ6, tblItemForecastFinancials.FQ7, tblItemForecastFinancials.FQ8, tblItemForecastFinancials.FQ9, tblItemForecastFinancials.FQ10, tblItemForecastFinancials.FMonths, tblItemForecastFinancials.FM1, tblItemForecastFinancials.FM2, tblItemForecastFinancials.FM3, tblItemForecastFinancials.FM4, tblItemForecastFinancials.FM5, tblItemForecastFinancials.FM6, tblItemForecastFinancials.FM7, tblItemForecastFinancials.FM8, tblItemForecastFinancials.FM9, tblItemForecastFinancials.FM10, tiff.ForecastType, tiff.Active\t FROM tblAgileProgram INNER Join  tblIterationReportGroup ON tblAgileProgram.ProgramID = tblIterationReportGroup.PortfolioID INNER Join tblDivision ON tblAgileProgram.CompanyID = tblDivision.DivisionID Left OUTER Join  tblAgileFeatureGroup ON tblIterationReportGroup.GroupID = tblAgileFeatureGroup.IterationGroup LEFT OUTER JOIN  tblAgileMultiDD ON tblAgileFeatureGroup.Driver = tblAgileMultiDD.FormulaValue AND tblAgileMultiDD.Type = 29 Left OUTER JOIN  tblAgileEpicReleaseRel ON tblAgileFeatureGroup.FeatureGroupID = tblAgileEpicReleaseRel.EpicID  Left OUTER JOIN  tblLifeCycleState ON tblAgileFeatureGroup.LifeCycleState = tblLifeCycleState.LifeCycleStateID   LEFT OUTER Join  sysmemberpermssion ON tblAgileFeatureGroup.Owner = sysmemberpermssion.UID Left OUTER Join  tblAgileTheme ON tblAgileFeatureGroup.ThemeID = tblAgileTheme.ThemeID Left OUTER Join  tblStrategyItem ON tblAgileTheme.StrategyItemID = tblStrategyItem.StrategyItemID  Left OUTER Join  tblAgileFeatureGroupHealth On tblAgileFeatureGroupHealth.EpicID = tblAgileFeatureGroup.FeatureGroupID Left OUTER Join  tblEpicHealthSet On tblEpicHealthSet.EpicID = tblAgileFeatureGroup.FeatureGroupID  Left OUTER Join tblAgileState On ISNULL(tblAgileFeatureGroup.State,1) = tblAgileState.ID  LEFT OUTER JOIN tblRelease On tblAgileEpicReleaseRel.ReleaseID = tblRelease.ReleaseID  LEFT OUTER JOIN (select AutoTrimValue as CostType, PortfolioID from tblSetupPortfolio where SetupID = 83)tSetup On tSetup.PortfolioID = tblIterationReportGroup.PortfolioID  LEFT OUTER JOIN (select * from tblNarration where TypeID = 1) tNarration on tNarration.ItemID = tblAgileFeatureGroup.FeatureGroupID  LEFT OUTER JOIN tblItemForecastFinancials on tblItemForecastFinancials.ItemID = tblAgileFeatureGroup.FeatureGroupID AND tblItemForecastFinancials.ItemType = 2  LEFT OUTER JOIN (Select ForecastType, Active from tblLinkNPVForecastFinancials) tiff on tiff.Active = 1 WHERE (tblAgileFeatureGroup.ParentID is null)  and (tblAgileProgram.ProgramID = 1 )   and tblAgileEpicReleaseRel.ReleaseID IN (13,14)  and (tblAgileProgram.ProgramID IN (6,61,56,63,62,1,49) )    and (tblIterationReportGroup.GroupID IN (78,121,122,190,165,268,299,57,184,91,62,169,150,151,189,290,282,58,96,263,262,107,108,374,375,373,360,109,81,59,146,195,164,113,111,187,69,425,422,427,428,80,162,191,159,205,278,192,492,140,149,118,277,203,72,185,196,120,124,74,250,581,371,325,137,337,338,336,341,342,339,340,174,175,176,170,172,171,179,197,178,245,88,63,186,266,260,85,114,82,86,83,256,154,64,287,138,25,281,60,276,252,160,104,152,103,361,283,249,119,291,247,183,264,90,182,93,92,97,204,255,188,94,84,244,289,155,261,279,251,67,246,133,309,259,288,267,248,193,194,158,148,153,147,216,105,106,257,70,76,79,125,65,66,73,61,77,101,68,655,491,647,442,443,444,445,446,448,454,563,565,533,570,531,682,297,298,210,37,304,303,17,45,27,40,18,605,452,396,218,44,641,455,10,366,271,200,53,285,95,313,310,330,15,612,381,48,651,207,308,649,525,46,47,225,41,50,42,28,331,353,351,26,211,56,424,423,426,429,215,16,616,13,398,393,316,302,223,221,11,711,534,224,219,270,214,35,305,579,306,324,208,344,345,343,348,349,346,347,643,274,254,296,301,295,300,253,23,198,212,55,284,356,29,654,39,332,363,364,365,535,611,213,294,6,243,329,652,359,222,379,49,357,217,209,382,206,52,394,420,201,54,390,51,199,639,9,293,710,272,352,653,377,280,383,714,43,19,311,273,312,307,12,650,376,315,380,372,327,370,241,242,543,387) )    and (tblAgileEpicReleaseRel.ReleaseID IN (45,35,19,26,47,60,18,13,59,42,58,3,56,17,6,61,54,50,53,7,51,52,55,49,15,27,12,4,9,43,2,48,46,14,41,5,8,10,11,44,29,16,57,28) OR tblAgileEpicReleaseRel.ReleaseID IS NULL)    group by tblEpicHealthSet.HealthSchedule, tblEpicHealthSet.HealthPeople, tblEpicHealthSet.HealthBudget, tblEpicHealthSet.HealthQuality, tblEpicHealthSet.HealthRisk, sysmemberpermssion.FirstName, sysmemberpermssion.lastName, tblAgileFeatureGroup.EstimateDate, tblAgileFeatureGroup.Capitalized, tblAgileFeatureGroup.Rank, tblAgileFeatureGroup.MMF, tblAgileMultiDD.ValueDesc, tblAgileFeatureGroup.Driver, tblAgileFeatureGroup.ExternalKey, tblLifeCycleState.processstep, tblAgileFeatureGroup.Split, tblAgileFeatureGroup.FeatureGroupID,  tblAgileProgram.CompanyID, tblAgileProgram.Name, tblAgileProgram.ProgramID, tblIterationReportGroup.GroupName, tblIterationReportGroup.GroupID, tblAgileFeatureGroup.State, tblEpicHealthSet.HealthBudgetDesc, tblEpicHealthSet.HealthScheduleDesc,  tblEpicHealthSet.HealthPeopleDesc, tblEpicHealthSet.HealthQualityDesc, tblEpicHealthSet.HealthRiskDesc, tblAgileFeatureGroup.GroupName, tblAgileFeatureGroup.targetDate, tb