Uploaded image for project: 'Jira Align'
  1. Jira Align
  2. JIRAALIGN-1502

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

XMLWordPrintable

    • 4
    • Severity 2 - Major

      Issue Summary

      Customers with large datasets have reported a performance issue with the Status Reports either taking upwards of several minutes to load or timing out completely with a 504 Error.

      Performance issue seems to be centered around the amount of time it takes to complete the call to StatusReports2

      Steps to Reproduce

      1) Log in and Navigate to the Status Report Page
      2) Click Program to view a list of programs
      3) Click on a Program to start generating the report

      On bimodalinternal.agilecraft.com, some slowness can be observed while selecting "AgileCraft Core" as the program in tier 1, then navigating to the Status report and clicking on the Program to load that Status report.

      Expected Results

      The Report should not take more than a minute to load, if that.

      Actual Results

      Page takes several minutes to load or we end up with a 504 timeout error

      The problem seems to be centered around the call we make in StatusReports2 to* EXEC RPM_GET_STATUS_REPORTS_DATA*

      Backend logging example:

      {"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
      

      Even on bimodalinternal when attempting to load the Status Report for the AgileCraft Core program (which is significantly smaller), we are seeing a total load time of 15 seconds.

      Dev tools console from a problematic customer instance:

      Workaround

      Currently there is no known workaround for this behavior.

            kforeman@atlassian.com Kyle Foreman
            kbaxley Kent Baxley
            Votes:
            2 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: