User tests: Successful: Unsuccessful:
This closes #4107
After 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.
Because 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.
Here are some test results of each variation (this one is listed under "Omar's Version"):
NOTE: 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().
Current Version:
Database queries total: 1238.8 ms
Database queries total: 1082.7 ms
Database queries total: 1134.2 ms
Database queries total: 1051.7 ms
Database queries total: 1167.3 ms
Omar's Version (Remove the GROUP BY clause):
Database queries total: 593.6 ms
Database queries total: 572.2 ms
Database queries total: 592.4 ms
Database queries total: 614.3 ms
Database queries total: 574.2 ms
Nicola's Version (Change the JOIN):
Database queries total: 511.6 ms
Database queries total: 549.0 ms
Database queries total: 530.7 ms
Database queries total: 538.7 ms
Database queries total: 527.3 ms
Marcel's Version (Remove the JOIN):
Database queries total: 517.4 ms
Database queries total: 604.9 ms
Database queries total: 545.6 ms
Database queries total: 550.4 ms
Database queries total: 509.2 ms
Nadeeshan's Version (Decompose into 2 Queries):
Database queries total: 526.6 ms
Database queries total: 556.7 ms
Database queries total: 525.2 ms
Database queries total: 550.8 ms
Database queries total: 546.5 ms
Labels |
Added:
?
|
Category | ⇒ | Libraries SQL |
I have tested this item unsuccessfully on cb9f34f
Tested in /administrator/index.php?option=com_content&view=articles using the Joomla!Debug Console with 68 articles (default testing sample content).
Didn't find any new bugs caused by this patch.
Before patch
49 Queries Logged 292.32 ms
49 Queries Logged 93.25 ms
49 Queries Logged 50.97 ms
49 Queries Logged 97.60 ms
49 Queries Logged 68.06 ms
49 Queries Logged 92.23 ms
After patch
49 Queries Logged 205.16 ms
49 Queries Logged 108.98 ms
49 Queries Logged 96.91 ms
49 Queries Logged 83.46 ms
49 Queries Logged 131.34 ms
49 Queries Logged 93.73 ms
Stats
OS Windows
PHP 5.6.11
MySQLi 5.6.25
Time 12:37
Caching Disabled
GZip Disabled
Articles 68
I've tested unsuccessful using the Debug console.
No issues where found applying the patch.
before patch 72 articles
1 15.61ms
2 18.00ms
3 18.85ms
4 20.31ms
5 36.46ms
after patch 72 articles
1 18,26
2 18,99
3 18,98
4 19,28
5 20,20
PHP Version 7.0.4
DB Type mysqli
DB Version 5.5.5
CMS Version 3.5.2
Status | Pending | ⇒ | Information Required |
Hello @orware
Thank you for your contribution.
The last comment here was on April 15th. We have had 2 unsuccessful tests, Is this issue/pull request still valid?
If no reply is received within 4 weeks we will close this issue.
Thanks for understanding!
I am closing this PR at this time for the reasons previously stated. It can always be re-opened if required
Status | Information Required | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-07-21 17:23:23 |
Closed_By | ⇒ | brianteeman |
Category | Libraries SQL | ⇒ | Libraries |
Confirmed.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/6681.