[#13505] - Speed up of saving new articles
- Fixed in Code Base
- 27 Jan 2017
- Medium
- Build: staging
- # 13505
- Diff
- csthomas:row_number_37
User tests: Successful: Unsuccessful:
Pull Request for Issue #10567.
Related to older implementation at #12839 which requires postgreSQL >= 8.4.2.
Summary of Changes
- Big performance improvement in
JTable::reorder()method with B/C. - It works on sqlite, mysql, postgresql and mssql, (on oracle probably too).
- Do not load any tables to php on "reordering".
- New public method
JDatabaseQuery::selectRowNumber($orderBy, $orderColumnAlias)which add an additional incremental column to SELECT type query. - You can use
selectRowNumberonly once per query. - to reorder articles use multi table UPDATE query with SELECT in subquery.
How it works.
- create SELECT sub-query with
row_numbercolumn. - create UPDATE query with the same where clause.
- join above queries by primary key or keys if exists.
- update ordering to new values from SELECT sub-query.
Details about selectRowNumber()
- on MySQL PostgreSQL >= 8.4.0 and SQL Server SELECT query preserves position of
row_numbercolumn - you can use safely$db->loadRowList(), examples:- ...select('id')->selectRowNumber('ordering ASC', 'row_number') generates result row with keys as 'id, row_number'
- ...selectRowNumber('ordering ASC', 'row_number')->select('id') generates result row with keys as 'row_number,id'
- on postgreSQL less that 8.4.0 and SQLite
row_numberis always on the last column, This is related to index column atloadRow()andloadRowList().
Other changes
- PostgreSQL:
fix update query with join: #13284- mergedadd sql var "CREATE TEMP SEQUENCE ROW_NUMBER" after establish a connection.
- SQL Server:
- fix multi table UPDATE to generate correct query
- In SELECT query move HAVING before ORDER BY
- SQLite:
- add support for multi table UPDATE query by
INSERT OR REPLACE INTO - add SQLite function
ROW_NUMBER(init=null, partitionBy=null)after establish a connection.
- add support for multi table UPDATE query by
- Add unit tests for
selectRowNumberand multi table UPDATE query. - Fix a few other unit test files that generated error after applied my changes.
Testing Instruction
- go to back end to list of articles (com_content).
- set order list by "Ordering asceding"
- select any category and set level 1
- add 3 example articles (articleA, articleB, articleC) in the same category as featured.
- back to articles list and you should see articles on the top of list (articleC, articleB, articleA, ...olders...)
- go to Featured Articles list and set order list by "Ordering asceding"
- you should see on the top of list (articleC, articleB, articleA, ...olders...)
If something went wrong then test it first without PR.
After this PR result should be the same.
[UPDATED]
How to test on postgreSQL
PostgreSQL Query has 2 versions of code which depend on postgresql server version (< 8.4 or >= 8.4).
So it would be good to test 2 options but as usual there is probably nobody with postgresql >= 8.3.18 and < 8.4.
- first do the test on postgresql as usual,
- then simulate older postgresql by changing line:
https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/driver/postgresql.php#L579
from{
to{ return '8.3.18';
and test again.
Documentation Changes Required
Probably.
Introduce a new public method JDatabaseQuery::selectRowNumber().
Main database drivers (mysql, postgresql, sqlsrv and sqlite) can use Update with innerJoin.
SQLite driver has a new sqlite function ROW_NUMBER(init=null, partitionBy=null).
Benchmark (old)
I tested this on MariaDB 10.0.27, php 7.0.8 on my laptop.
After patch reorder is almost 100x faster for 4000 articles in one category. Total articles ~233k.
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | Postgresql Libraries MS SQL Unit Tests |
| Labels |
Added:
?
?
|
||
@csthomas would you consider also adding the JDatabaseQuery selectRowNumber() method and related changes to the Framework database repo
I will but now I want to add it to joomla 3.7.
| Title |
|
||||||
I have added two versions of selectRowNumber(), for postgresql >= 8.4.0 and other for less than 8.4.0.
I have tested this item
With and without PR Result is same ordering like in "Testing instruction" described.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13505.
I have tested this item
Tested on mysql version 5.5.53-0ubuntu0.14.04.1
Before patch
COUNT :1116
TIME DIFF : 5.6671
After patch
COUNT :1120
TIME DIFF : 0.0319
It was successful following the Testing instruction.
But when i do the following, with or without the PR :
- go to back end to list of articles (com_content).
- set order list by "Ordering asceding"
- select any category and set level 1
- add 3 example articles (articleA, articleB, articleC) in the same category as not featured.
- back to articles list and we see articles on the top of list (articleC, articleB, articleA, ...olders...)
- check the articles (articleA, articleB, articleC) and hit "Feature" and we still see the same ordering
- go to Featured Articles list and set order list by "Ordering asceding" we see on the top of list (articleB, articleC, articleA, ...olders...).
Any ideas on this anomaly ?
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13505.
check the articles (articleA, articleB, articleC) and hit "Feature" and we still see the same ordering
this is ok, you add articles to featured but list order is by article ordering (not featured ordering)
go to Featured Articles list and set order list by "Ordering asceding" we see on the top of list (articleB, articleC, articleA, ...olders...).
You add in one time 3 articles to featured, sql will deliver 3 featured with ordering=0 to reorder. IMO It won't preserve any order. it is a set of 3 articles, not ordered list of 3 items.
@waader @alikon @photodude Can you help with test? (mainly postgresql, but it would be nice to test mssql too)
@csthomas I'm currently primarily working on the Windows CI testing with Appveyor
See
joomla-framework/database#72
joomla-framework/database#74
joomla-framework/database#75
#13690
and the Alpha of the PHPCS 2 coding standards autofixers release see
joomla/coding-standards#143
So I haven't had time to test CMS specific PRs (my current local needs to be rebuilt too, especially for any PostgreSQL or MSSQL testing).
If you would be willing to pitch in on those projects, I could allocate time to setup a local for testing here.
There is some additional consideration with PostgreSQL or MSSQL testing, we often only merge those by code review especially when MySQL passes, or if there are no MySQL related changes. Since the change will be held up if try to find someone with a system configuration that meets those edge case requirements and time to test. Final call on a Merge by review would need to be made by the release leader or someone else with commit privileges.
I have tested this item
Tested on psql (PostgreSQL) 9.3.15
Before the patch
COUNT : 4044
TIME DIFF : 36.7341 sec
After the patch
COUNT : 4044
TIME DIFF : 0.1496 sec
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13505.
| Status | Pending | ⇒ | Ready to Commit |
| Labels | |||
RTC
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13505.
rdeutz
- | Status | Ready to Commit | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2017-01-27 18:23:06 |
| Closed_By | ⇒ | rdeutz | |
| Labels |
Added:
?
|
||
With testing data installed frontend gives an "404 Component not found" error. Starting with index.php?option=com_content the page loads. The module helper notices are also present and no modul content is displayed.