{"action":"closed","issue":{"url":"https://api.github.com/repos/OctopusDeploy/Issues/issues/3025","repository_url":"https://api.github.com/repos/OctopusDeploy/Issues","labels_url":"https://api.github.com/repos/OctopusDeploy/Issues/issues/3025/labels{/name}","comments_url":"https://api.github.com/repos/OctopusDeploy/Issues/issues/3025/comments","events_url":"https://api.github.com/repos/OctopusDeploy/Issues/issues/3025/events","html_url":"https://github.com/OctopusDeploy/Issues/issues/3025","id":196557321,"number":3025,"title":"Slow pagination performance","user":{"login":"thebigmoosey","id":819605,"avatar_url":"https://avatars.githubusercontent.com/u/819605?v=3","gravatar_id":"","url":"https://api.github.com/users/thebigmoosey","html_url":"https://github.com/thebigmoosey","followers_url":"https://api.github.com/users/thebigmoosey/followers","following_url":"https://api.github.com/users/thebigmoosey/following{/other_user}","gists_url":"https://api.github.com/users/thebigmoosey/gists{/gist_id}","starred_url":"https://api.github.com/users/thebigmoosey/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thebigmoosey/subscriptions","organizations_url":"https://api.github.com/users/thebigmoosey/orgs","repos_url":"https://api.github.com/users/thebigmoosey/repos","events_url":"https://api.github.com/users/thebigmoosey/events{/privacy}","received_events_url":"https://api.github.com/users/thebigmoosey/received_events","type":"User","site_admin":false},"labels":[{"id":62440875,"url":"https://api.github.com/repos/OctopusDeploy/Issues/labels/bug","name":"bug","color":"eb6420","default":true},{"id":225395497,"url":"https://api.github.com/repos/OctopusDeploy/Issues/labels/in%20progress","name":"in progress","color":"ededed","default":false}],"state":"closed","locked":false,"assignee":{"login":"thebigmoosey","id":819605,"avatar_url":"https://avatars.githubusercontent.com/u/819605?v=3","gravatar_id":"","url":"https://api.github.com/users/thebigmoosey","html_url":"https://github.com/thebigmoosey","followers_url":"https://api.github.com/users/thebigmoosey/followers","following_url":"https://api.github.com/users/thebigmoosey/following{/other_user}","gists_url":"https://api.github.com/users/thebigmoosey/gists{/gist_id}","starred_url":"https://api.github.com/users/thebigmoosey/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thebigmoosey/subscriptions","organizations_url":"https://api.github.com/users/thebigmoosey/orgs","repos_url":"https://api.github.com/users/thebigmoosey/repos","events_url":"https://api.github.com/users/thebigmoosey/events{/privacy}","received_events_url":"https://api.github.com/users/thebigmoosey/received_events","type":"User","site_admin":false},"assignees":[{"login":"thebigmoosey","id":819605,"avatar_url":"https://avatars.githubusercontent.com/u/819605?v=3","gravatar_id":"","url":"https://api.github.com/users/thebigmoosey","html_url":"https://github.com/thebigmoosey","followers_url":"https://api.github.com/users/thebigmoosey/followers","following_url":"https://api.github.com/users/thebigmoosey/following{/other_user}","gists_url":"https://api.github.com/users/thebigmoosey/gists{/gist_id}","starred_url":"https://api.github.com/users/thebigmoosey/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thebigmoosey/subscriptions","organizations_url":"https://api.github.com/users/thebigmoosey/orgs","repos_url":"https://api.github.com/users/thebigmoosey/repos","events_url":"https://api.github.com/users/thebigmoosey/events{/privacy}","received_events_url":"https://api.github.com/users/thebigmoosey/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2016-12-20T00:41:48Z","updated_at":"2016-12-21T03:21:03Z","closed_at":"2016-12-21T03:21:03Z","body":"### The Problem\r\nPeople were noticing that the audit screen on demo was occasionally timing out (again).\r\n\r\nAfter some investigation, when things got converted to Nevermore, we seem to have lost all the query perf fixes we'd done to the `PaginateQuery` as part of 3.4\r\n\r\nAfter running SQL Profiler on demo, we can see that it's reverted back to the old paging query which used wildcards:\r\n\r\n```exec sp_executesql N'SELECT *\r\nFROM (SELECT ALIAS_Event_0.*, Row_Number() over (ORDER BY ALIAS_Event_0.[Occurred] DESC, ALIAS_Event_0.[Id] DESC) as RowNum FROM (SELECT * FROM dbo.[Event] WHERE ([Occurred] >= @from) AND ([Occurred] <= @to)) ALIAS_Event_0) RS\r\nWHERE RowNum >= @_minrow And RowNum <= @_maxrow\r\nORDER BY RowNum',N'@from datetimeoffset(7),@to datetimeoffset(7),@_minrow int,@_maxrow int',@from='2016-11-20 00:00:00 +10:00',@to='2016-12-20 23:59:59 +10:00',@_minrow=1,@_maxrow=30\r\n```\r\n\r\nWhen you have a table with hundreds of thousands of results in a short period of time (like our event table on demo from all the packages being pushed constantly) that query times out hard (until sql caches the result)\r\n\r\nWe tracked it down to Nevermore having two implementations of `PaginateQuery` and the wrong one being used.\r\n\r\nThe fix is basically to change all the wildcards to specifically use [Id] instead (so SQL only has to search and join on that one column), then nest that query like this:\r\n\r\n```\r\nSELECT *\r\nFROM [table]\r\nWHERE [Id] IN ([our nested subquery that uses [Id] instead of wildcards])\r\n```\r\n\r\n### The Solution\r\nMake sure all paging queries are using the correct implementation of the PaginateQuery and re-do the load testing for paginated queries."}}
{"actor":{"display_login":"thebigmoosey"}}