Performance issue (3 second load times) on /Teams/Answers

[morganbr]

This looks like another join issue - too many roundtrips to the SQL server when the page should be doing a single request with a join.

_MS.ProcessedByMetricExtractors: (Name:'Requests', Ver:'1.0') Account Id: Application version: 1.0.0.0 AspNetCoreEnvironment: Production Auth user Id: Browser version: City: Cambridge Client IP address: 0.0.0.0 Cloud role instance: RD0003FFE533C6 Cloud role name: puzzlehunt Country or region: United States Device model: Device type: PC Event time: 4/7/2019, 1:12:18 PM Operating system: Operation Id: f344b7c1-46c7920e0b421245 Operation name: GET /Teams/Answers Parent Id: f344b7c1-46c7920e0b421245 Request Id: |f344b7c1-46c7920e0b421245. Request name: GET /Teams/Answers Request source: Request URL: https://puzzlehunt.azurewebsites.net/1/play/Teams/91/Answers Response code: 200 Response time: 4.19 s Sample Rate: 25 SDK version: aspnet5c:2.3.0 Session Id: Cpbwd Source of synthetic traffic: State or province: Massachusetts Successful request: true Telemetry type: request User Id: EPsyw

View this instance in the Azure portal<\a>

The biggest time gap is immediately after this query (the query itself isn't flagged as long, but there's always a 1 second+ gap after it), so either this query takes a long time to return or there's slow processing happening on the server after it's returned.

SELECT [s].[ID], [s].[PuzzleID], [s].[ResponseID], [s].[SubmissionText], [s].[SubmitterID], [s].[TeamID], [s].[TeamID1], [s].[TimeSubmitted], [s.Puzzle].[ID], [s.Puzzle].[CustomURL], [s.Puzzle].[Description], [s.Puzzle].[EventID], [s.Puzzle].[Group], [s.Puzzle].[HintCoinsForSolve], [s.Puzzle].[HintsAreCumulative], [s.Puzzle].[IsCheatCode], [s.Puzzle].[IsFinalPuzzle], [s.Puzzle].[IsGloballyVisiblePrerequisite], [s.Puzzle].[IsMetaPuzzle], [s.Puzzle].[IsPuzzle], [s.Puzzle].[MaxAnnotationKey], [s.Puzzle].[MinPrerequisiteCount], [s.Puzzle].[MinutesOfEventLockout], [s.Puzzle].[MinutesToAutomaticallySolve], [s.Puzzle].[Name], [s.Puzzle].[OrderInGroup], [s.Puzzle].[PuzzleVersion], [s.Puzzle].[SolveValue], [s.Puzzle].[SupportEmailAlias], [s.Puzzle].[Token], [s.Response].[ID], [s.Response].[IsSolution], [s.Response].[Note], [s.Response].[PuzzleID], [s.Response].[ResponseText], [s.Response].[SubmittedText] FROM [Submissions] AS [s] INNER JOIN [Puzzles] AS [s.Puzzle] ON [s].[PuzzleID] = [s.Puzzle].[ID] LEFT JOIN [Responses] AS [s.Response] ON [s].[ResponseID] = [s.Response].[ID] WHERE ([s].[TeamID] = @__teamId_0) AND ([s.Response].[IsSolution] = 1) ORDER BY [s.Puzzle].[Group]