{"action":"opened","number":1905,"pull_request":{"url":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/1905","id":96399158,"html_url":"https://github.com/SparkDevNetwork/Rock/pull/1905","diff_url":"https://github.com/SparkDevNetwork/Rock/pull/1905.diff","patch_url":"https://github.com/SparkDevNetwork/Rock/pull/1905.patch","issue_url":"https://api.github.com/repos/SparkDevNetwork/Rock/issues/1905","number":1905,"state":"open","locked":false,"title":"Added an optimization to GetMappedLocationsByGeofences","user":{"login":"trydyingtolive","id":495787,"avatar_url":"https://avatars.githubusercontent.com/u/495787?v=3","gravatar_id":"","url":"https://api.github.com/users/trydyingtolive","html_url":"https://github.com/trydyingtolive","followers_url":"https://api.github.com/users/trydyingtolive/followers","following_url":"https://api.github.com/users/trydyingtolive/following{/other_user}","gists_url":"https://api.github.com/users/trydyingtolive/gists{/gist_id}","starred_url":"https://api.github.com/users/trydyingtolive/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/trydyingtolive/subscriptions","organizations_url":"https://api.github.com/users/trydyingtolive/orgs","repos_url":"https://api.github.com/users/trydyingtolive/repos","events_url":"https://api.github.com/users/trydyingtolive/events{/privacy}","received_events_url":"https://api.github.com/users/trydyingtolive/received_events","type":"User","site_admin":false},"body":"# Contributor Agreement\r\nYes\r\n\r\n# Context\r\nWe have just shy of 200,000 group locations in our database. We are looking into connecting people in their neighborhoods and we noticed that searching for all people in a small geofence takes about 30 seconds.  \r\n\r\n# Goal\r\nWe want to find all of the groups within an area quickly.\r\n\r\n# Strategy\r\n We suspected that SQL Server 2016 was doing a full table search on all of the GroupLocation.\r\nLocations. There is a spacial index on the geopoint column, but for some reason SQL Server isn't using that index. Upon investigation we discovered that the pagination of the grid (line 951 of Grid.cs) causes SQL Server to not use the index. That is when Entity Framework adds in \"TOP 50\" to the SQL it causes a full table scan. Normally we would add an index hint to SQL, but EF does not support hints. \r\n\r\nThe strategy to make SQL Server use the indexes is to get the Ids of all intersecting locations, and then get all of the entities afterward. This changes searching for groups within a neighborhood from about 30 seconds on our database to trivial time. There is a check to see if the first query returns more than 10,000 Ids. If so it falls back on the original method, as too many Ids will cause problems and isn't much faster than a full table scan anyway. \r\n\r\n# Possible Implications\r\nSearching in an area that contains over 10,000 group locations will be slower because of the first search, but it wont be fast anyway because of the large number.\r\n\r\n\r\n","created_at":"2016-12-03T14:22:50Z","updated_at":"2016-12-03T14:22:50Z","closed_at":null,"merged_at":null,"merge_commit_sha":null,"assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/1905/commits","review_comments_url":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/1905/comments","review_comment_url":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/comments{/number}","comments_url":"https://api.github.com/repos/SparkDevNetwork/Rock/issues/1905/comments","statuses_url":"https://api.github.com/repos/SparkDevNetwork/Rock/statuses/a84eefa4aaa6af016819128355ec6260d2c00470","head":{"label":"secc:feature-ml-geofenceintersectsspeedup","ref":"feature-ml-geofenceintersectsspeedup","sha":"a84eefa4aaa6af016819128355ec6260d2c00470","user":{"login":"secc","id":7047802,"avatar_url":"https://avatars.githubusercontent.com/u/7047802?v=3","gravatar_id":"","url":"https://api.github.com/users/secc","html_url":"https://github.com/secc","followers_url":"https://api.github.com/users/secc/followers","following_url":"https://api.github.com/users/secc/following{/other_user}","gists_url":"https://api.github.com/users/secc/gists{/gist_id}","starred_url":"https://api.github.com/users/secc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/secc/subscriptions","organizations_url":"https://api.github.com/users/secc/orgs","repos_url":"https://api.github.com/users/secc/repos","events_url":"https://api.github.com/users/secc/events{/privacy}","received_events_url":"https://api.github.com/users/secc/received_events","type":"Organization","site_admin":false},"repo":{"id":54781038,"name":"Rock","full_name":"secc/Rock","owner":{"login":"secc","id":7047802,"avatar_url":"https://avatars.githubusercontent.com/u/7047802?v=3","gravatar_id":"","url":"https://api.github.com/users/secc","html_url":"https://github.com/secc","followers_url":"https://api.github.com/users/secc/followers","following_url":"https://api.github.com/users/secc/following{/other_user}","gists_url":"https://api.github.com/users/secc/gists{/gist_id}","starred_url":"https://api.github.com/users/secc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/secc/subscriptions","organizations_url":"https://api.github.com/users/secc/orgs","repos_url":"https://api.github.com/users/secc/repos","events_url":"https://api.github.com/users/secc/events{/privacy}","received_events_url":"https://api.github.com/users/secc/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/secc/Rock","description":"An open source CMS, Relationship Management System (RMS) and Church Management System (ChMS) all rolled into one.","fork":true,"url":"https://api.github.com/repos/secc/Rock","forks_url":"https://api.github.com/repos/secc/Rock/forks","keys_url":"https://api.github.com/repos/secc/Rock/keys{/key_id}","collaborators_url":"https://api.github.com/repos/secc/Rock/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/secc/Rock/teams","hooks_url":"https://api.github.com/repos/secc/Rock/hooks","issue_events_url":"https://api.github.com/repos/secc/Rock/issues/events{/number}","events_url":"https://api.github.com/repos/secc/Rock/events","assignees_url":"https://api.github.com/repos/secc/Rock/assignees{/user}","branches_url":"https://api.github.com/repos/secc/Rock/branches{/branch}","tags_url":"https://api.github.com/repos/secc/Rock/tags","blobs_url":"https://api.github.com/repos/secc/Rock/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/secc/Rock/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/secc/Rock/git/refs{/sha}","trees_url":"https://api.github.com/repos/secc/Rock/git/trees{/sha}","statuses_url":"https://api.github.com/repos/secc/Rock/statuses/{sha}","languages_url":"https://api.github.com/repos/secc/Rock/languages","stargazers_url":"https://api.github.com/repos/secc/Rock/stargazers","contributors_url":"https://api.github.com/repos/secc/Rock/contributors","subscribers_url":"https://api.github.com/repos/secc/Rock/subscribers","subscription_url":"https://api.github.com/repos/secc/Rock/subscription","commits_url":"https://api.github.com/repos/secc/Rock/commits{/sha}","git_commits_url":"https://api.github.com/repos/secc/Rock/git/commits{/sha}","comments_url":"https://api.github.com/repos/secc/Rock/comments{/number}","issue_comment_url":"https://api.github.com/repos/secc/Rock/issues/comments{/number}","contents_url":"https://api.github.com/repos/secc/Rock/contents/{+path}","compare_url":"https://api.github.com/repos/secc/Rock/compare/{base}...{head}","merges_url":"https://api.github.com/repos/secc/Rock/merges","archive_url":"https://api.github.com/repos/secc/Rock/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/secc/Rock/downloads","issues_url":"https://api.github.com/repos/secc/Rock/issues{/number}","pulls_url":"https://api.github.com/repos/secc/Rock/pulls{/number}","milestones_url":"https://api.github.com/repos/secc/Rock/milestones{/number}","notifications_url":"https://api.github.com/repos/secc/Rock/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/secc/Rock/labels{/name}","releases_url":"https://api.github.com/repos/secc/Rock/releases{/id}","deployments_url":"https://api.github.com/repos/secc/Rock/deployments","created_at":"2016-03-26T13:47:19Z","updated_at":"2016-03-26T13:47:47Z","pushed_at":"2016-12-03T03:02:26Z","git_url":"git://github.com/secc/Rock.git","ssh_url":"git@github.com:secc/Rock.git","clone_url":"https://github.com/secc/Rock.git","svn_url":"https://github.com/secc/Rock","homepage":"http://www.rockrms.com","size":1071587,"stargazers_count":0,"watchers_count":0,"language":"C#","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"develop"}},"base":{"label":"SparkDevNetwork:develop","ref":"develop","sha":"cdde7a2c8033744a35021175e240af32e9145b20","user":{"login":"SparkDevNetwork","id":548191,"avatar_url":"https://avatars.githubusercontent.com/u/548191?v=3","gravatar_id":"","url":"https://api.github.com/users/SparkDevNetwork","html_url":"https://github.com/SparkDevNetwork","followers_url":"https://api.github.com/users/SparkDevNetwork/followers","following_url":"https://api.github.com/users/SparkDevNetwork/following{/other_user}","gists_url":"https://api.github.com/users/SparkDevNetwork/gists{/gist_id}","starred_url":"https://api.github.com/users/SparkDevNetwork/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SparkDevNetwork/subscriptions","organizations_url":"https://api.github.com/users/SparkDevNetwork/orgs","repos_url":"https://api.github.com/users/SparkDevNetwork/repos","events_url":"https://api.github.com/users/SparkDevNetwork/events{/privacy}","received_events_url":"https://api.github.com/users/SparkDevNetwork/received_events","type":"Organization","site_admin":false},"repo":{"id":1614137,"name":"Rock","full_name":"SparkDevNetwork/Rock","owner":{"login":"SparkDevNetwork","id":548191,"avatar_url":"https://avatars.githubusercontent.com/u/548191?v=3","gravatar_id":"","url":"https://api.github.com/users/SparkDevNetwork","html_url":"https://github.com/SparkDevNetwork","followers_url":"https://api.github.com/users/SparkDevNetwork/followers","following_url":"https://api.github.com/users/SparkDevNetwork/following{/other_user}","gists_url":"https://api.github.com/users/SparkDevNetwork/gists{/gist_id}","starred_url":"https://api.github.com/users/SparkDevNetwork/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SparkDevNetwork/subscriptions","organizations_url":"https://api.github.com/users/SparkDevNetwork/orgs","repos_url":"https://api.github.com/users/SparkDevNetwork/repos","events_url":"https://api.github.com/users/SparkDevNetwork/events{/privacy}","received_events_url":"https://api.github.com/users/SparkDevNetwork/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/SparkDevNetwork/Rock","description":"An open source CMS, Relationship Management System (RMS) and Church Management System (ChMS) all rolled into one.","fork":false,"url":"https://api.github.com/repos/SparkDevNetwork/Rock","forks_url":"https://api.github.com/repos/SparkDevNetwork/Rock/forks","keys_url":"https://api.github.com/repos/SparkDevNetwork/Rock/keys{/key_id}","collaborators_url":"https://api.github.com/repos/SparkDevNetwork/Rock/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/SparkDevNetwork/Rock/teams","hooks_url":"https://api.github.com/repos/SparkDevNetwork/Rock/hooks","issue_events_url":"https://api.github.com/repos/SparkDevNetwork/Rock/issues/events{/number}","events_url":"https://api.github.com/repos/SparkDevNetwork/Rock/events","assignees_url":"https://api.github.com/repos/SparkDevNetwork/Rock/assignees{/user}","branches_url":"https://api.github.com/repos/SparkDevNetwork/Rock/branches{/branch}","tags_url":"https://api.github.com/repos/SparkDevNetwork/Rock/tags","blobs_url":"https://api.github.com/repos/SparkDevNetwork/Rock/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/SparkDevNetwork/Rock/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/SparkDevNetwork/Rock/git/refs{/sha}","trees_url":"https://api.github.com/repos/SparkDevNetwork/Rock/git/trees{/sha}","statuses_url":"https://api.github.com/repos/SparkDevNetwork/Rock/statuses/{sha}","languages_url":"https://api.github.com/repos/SparkDevNetwork/Rock/languages","stargazers_url":"https://api.github.com/repos/SparkDevNetwork/Rock/stargazers","contributors_url":"https://api.github.com/repos/SparkDevNetwork/Rock/contributors","subscribers_url":"https://api.github.com/repos/SparkDevNetwork/Rock/subscribers","subscription_url":"https://api.github.com/repos/SparkDevNetwork/Rock/subscription","commits_url":"https://api.github.com/repos/SparkDevNetwork/Rock/commits{/sha}","git_commits_url":"https://api.github.com/repos/SparkDevNetwork/Rock/git/commits{/sha}","comments_url":"https://api.github.com/repos/SparkDevNetwork/Rock/comments{/number}","issue_comment_url":"https://api.github.com/repos/SparkDevNetwork/Rock/issues/comments{/number}","contents_url":"https://api.github.com/repos/SparkDevNetwork/Rock/contents/{+path}","compare_url":"https://api.github.com/repos/SparkDevNetwork/Rock/compare/{base}...{head}","merges_url":"https://api.github.com/repos/SparkDevNetwork/Rock/merges","archive_url":"https://api.github.com/repos/SparkDevNetwork/Rock/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/SparkDevNetwork/Rock/downloads","issues_url":"https://api.github.com/repos/SparkDevNetwork/Rock/issues{/number}","pulls_url":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls{/number}","milestones_url":"https://api.github.com/repos/SparkDevNetwork/Rock/milestones{/number}","notifications_url":"https://api.github.com/repos/SparkDevNetwork/Rock/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/SparkDevNetwork/Rock/labels{/name}","releases_url":"https://api.github.com/repos/SparkDevNetwork/Rock/releases{/id}","deployments_url":"https://api.github.com/repos/SparkDevNetwork/Rock/deployments","created_at":"2011-04-14T13:11:46Z","updated_at":"2016-12-01T14:39:14Z","pushed_at":"2016-12-03T02:08:02Z","git_url":"git://github.com/SparkDevNetwork/Rock.git","ssh_url":"git@github.com:SparkDevNetwork/Rock.git","clone_url":"https://github.com/SparkDevNetwork/Rock.git","svn_url":"https://github.com/SparkDevNetwork/Rock","homepage":"http://www.rockrms.com","size":1269347,"stargazers_count":184,"watchers_count":184,"language":"C#","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":132,"mirror_url":null,"open_issues_count":192,"forks":132,"open_issues":192,"watchers":184,"default_branch":"develop"}},"_links":{"self":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/1905"},"html":{"href":"https://github.com/SparkDevNetwork/Rock/pull/1905"},"issue":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/issues/1905"},"comments":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/issues/1905/comments"},"review_comments":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/1905/comments"},"review_comment":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/pulls/1905/commits"},"statuses":{"href":"https://api.github.com/repos/SparkDevNetwork/Rock/statuses/a84eefa4aaa6af016819128355ec6260d2c00470"}},"merged":false,"mergeable":null,"mergeable_state":"unknown","merged_by":null,"comments":0,"review_comments":0,"commits":1,"additions":33,"deletions":9,"changed_files":1}}
{"actor":{"display_login":"trydyingtolive"}}