{"action":"created","issue":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/6681","repository_url":"https://api.github.com/repos/joomla/joomla-cms","labels_url":"https://api.github.com/repos/joomla/joomla-cms/issues/6681/labels{/name}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/issues/6681/comments","events_url":"https://api.github.com/repos/joomla/joomla-cms/issues/6681/events","html_url":"https://github.com/joomla/joomla-cms/pull/6681","id":66695798,"number":6681,"title":"Improve JAccess::getAssetRules() Performance ~50%","user":{"login":"orware","id":931650,"avatar_url":"https://avatars.githubusercontent.com/u/931650?v=3","gravatar_id":"","url":"https://api.github.com/users/orware","html_url":"https://github.com/orware","followers_url":"https://api.github.com/users/orware/followers","following_url":"https://api.github.com/users/orware/following{/other_user}","gists_url":"https://api.github.com/users/orware/gists{/gist_id}","starred_url":"https://api.github.com/users/orware/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/orware/subscriptions","organizations_url":"https://api.github.com/users/orware/orgs","repos_url":"https://api.github.com/users/orware/repos","events_url":"https://api.github.com/users/orware/events{/privacy}","received_events_url":"https://api.github.com/users/orware/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/joomla/joomla-cms/labels/PR-staging","name":"PR-staging","color":"c7def8"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-04-06T20:18:06Z","updated_at":"2016-04-15T12:41:57Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/joomla/joomla-cms/pulls/6681","html_url":"https://github.com/joomla/joomla-cms/pull/6681","diff_url":"https://github.com/joomla/joomla-cms/pull/6681.diff","patch_url":"https://github.com/joomla/joomla-cms/pull/6681.patch"},"body":"This closes #4107\r\n\r\nAfter considerable discussion on the sql-optimisation-wg between @alikon and @sovainfo and comparing 3 different versions of Nadeeshan's SQL Optimization code for JAccess in issue #4107 we decided that the simplest way to improve the performance would be to make the change suggested here: remove the GROUP BY clause for the query.\r\n\r\nBecause of the comment about SQL Server mentioned above that line additional testing was performed in MS SQL and Postgres and no issues related to the change were noted.\r\n\r\nHere are some test results of each variation (this one is listed under \"Omar's Version\"):\r\nNOTE: Tests were run in the Backend Articles List View, Logged in as an Administrator User (not Super Admin) with 100 Articles Listed which resulted in 125 calls to JAccess:getAssetRules().\r\n\r\nCurrent Version:\r\nDatabase queries total: 1238.8 ms\r\nDatabase queries total: 1082.7 ms\r\nDatabase queries total: 1134.2 ms\r\nDatabase queries total: 1051.7 ms\r\nDatabase queries total: 1167.3 ms\r\n\r\nOmar's Version (Remove the GROUP BY clause):\r\nDatabase queries total: 593.6 ms\r\nDatabase queries total: 572.2 ms\r\nDatabase queries total: 592.4 ms\r\nDatabase queries total: 614.3 ms\r\nDatabase queries total: 574.2 ms\r\n\r\nNicola's Version (Change the JOIN):\r\nDatabase queries total: 511.6 ms\r\nDatabase queries total: 549.0 ms\r\nDatabase queries total: 530.7 ms\r\nDatabase queries total: 538.7 ms\r\nDatabase queries total: 527.3 ms\r\n\r\nMarcel's Version (Remove the JOIN):\r\nDatabase queries total: 517.4 ms\r\nDatabase queries total: 604.9 ms\r\nDatabase queries total: 545.6 ms\r\nDatabase queries total: 550.4 ms\r\nDatabase queries total: 509.2 ms\r\n\r\nNadeeshan's Version (Decompose into 2 Queries):\r\nDatabase queries total: 526.6 ms\r\nDatabase queries total: 556.7 ms\r\nDatabase queries total: 525.2 ms\r\nDatabase queries total: 550.8 ms\r\nDatabase queries total: 546.5 ms"},"comment":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/comments/210449050","html_url":"https://github.com/joomla/joomla-cms/pull/6681#issuecomment-210449050","issue_url":"https://api.github.com/repos/joomla/joomla-cms/issues/6681","id":210449050,"user":{"login":"AnneKlapwijk","id":3767167,"avatar_url":"https://avatars.githubusercontent.com/u/3767167?v=3","gravatar_id":"","url":"https://api.github.com/users/AnneKlapwijk","html_url":"https://github.com/AnneKlapwijk","followers_url":"https://api.github.com/users/AnneKlapwijk/followers","following_url":"https://api.github.com/users/AnneKlapwijk/following{/other_user}","gists_url":"https://api.github.com/users/AnneKlapwijk/gists{/gist_id}","starred_url":"https://api.github.com/users/AnneKlapwijk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/AnneKlapwijk/subscriptions","organizations_url":"https://api.github.com/users/AnneKlapwijk/orgs","repos_url":"https://api.github.com/users/AnneKlapwijk/repos","events_url":"https://api.github.com/users/AnneKlapwijk/events{/privacy}","received_events_url":"https://api.github.com/users/AnneKlapwijk/received_events","type":"User","site_admin":false},"created_at":"2016-04-15T12:41:56Z","updated_at":"2016-04-15T12:41:56Z","body":"I have tested this item :red_circle: unsuccessfully on cb9f34fd4116695479474c5e7f2da06ddb8af0a4<br /><br />Tested in /administrator/index.php?option=com_content&view=articles using the Joomla!Debug Console with 68 articles (default testing sample content).\nDidn't find any new bugs caused by this patch.\n\n**Before patch**\n49 Queries Logged 292.32 ms\n49 Queries Logged 93.25 ms\n49 Queries Logged 50.97 ms\n49 Queries Logged 97.60 ms\n49 Queries Logged 68.06 ms\n49 Queries Logged 92.23 ms\n\n**After patch**\n49 Queries Logged 205.16 ms\n49 Queries Logged 108.98 ms\n49 Queries Logged 96.91 ms\n49 Queries Logged 83.46 ms\n49 Queries Logged 131.34 ms\n49 Queries Logged 93.73 ms\n\n**Stats**\n OS Windows\n PHP 5.6.11\n MySQLi 5.6.25\n Time 12:37\n Caching Disabled\n GZip Disabled\n Articles 68<hr /><sub>This comment was created with the <a href=\"https://github.com/joomla/jissues\">J!Tracker Application</a> at <a href=\"https://issues.joomla.org/tracker/joomla-cms/6681\">issues.joomla.org/joomla-cms/6681</a>.</sub>"}}