[#20943] - performace improvement for moveByReference, rebuild, create and delete nested tree records
- Closed
- 25 Mar 2022
- Medium
- Build: staging
- # 20943
- Diff
- shandak:moveByReferenceImprovement
- Success Hound No violations found. Woof! Details
- Success continuous-integration/travis-ci/pr The Travis CI build passed Details
- Success continuous-integration/drone/pr Build is passing Details
- Success continuous-integration/appveyor/pr AppVeyor build succeeded Details
User tests: Successful: Unsuccessful:
Summary of Changes
Every nested tree node movement or rebuild usually executes quite a long time. Especially for the supertable #__assets.
New moveByReference changes will increase performance by changing only leaves which between a new and an old node positions and does it only in one query(only one update query). We don't need to compress the tree several times anymore. New changes only work with data which actually required for changes.
I got an enormous time execution difference inside my project with this changes.
Before it was executed by 587 seconds
and the same action with my changes was executed only by 18 seconds.
The query has a format like:
UPDATE `#__reditem_categories` SET lft = CASE WHEN lft BETWEEN 2 AND 3 THEN lft+4 WHEN lft BETWEEN 2 AND 7 THEN lft-2 ELSE lft END , rgt = CASE WHEN rgt BETWEEN 2 AND 3 THEN rgt+4 WHEN rgt BETWEEN 2 AND 7 THEN rgt-2 ELSE rgt END WHERE (lft BETWEEN 2 AND 7 OR rgt BETWEEN 2 AND 7)
New rebuild changes are also improving the process dramatically. It was tested with a broken #__asset table with more than 1 million records. I got the next results:
For full broken tree
before changes 671 sec
after my changes 39 sec
New delete and create changes just improve a process a bit. I've changed them for completely avoid an old approach related with _getTreeRepositionData. I completely removed it.
Testing Instructions
Every movement, rebuild, create and delete of nested tree entities(like category) must be as usual.
Expected result
Performance improvement for huge nested tree tables must be substantially fast than right now.
Actual result
Every nested tree node movement, rebuild usually executes quite a long time.
Documentation Changes Required
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | Libraries |
| Labels |
Added:
?
|
||
| Title |
|
||||||
| Title |
|
||||||
| Category | Libraries | ⇒ | Libraries Unit Tests |
Thank you @Hackwar for your's feedback.
With regard to back compatibility, you told about removed _getTreeRepositionData yeah? If so I can turn it back and mark as deprecated. Will it be enough to release the code before 4.0? It seems it must be fully compatible with the current one.
And yeah, I'll rebuild 'new ordering of items' in the next PR.
| Labels |
Added:
?
|
||
This can be very good improvement, I will try to find a time to test it.
| Labels |
Added:
Conflicting Files
|
||
@richard67 conflicts solved
| Labels |
Added:
?
?
Removed: ? |
||
Sorry that it toke so long to respond. Performance improvements are always welcome in Joomla. But it needs proper testing, especially here as the changes do affect many extensions. As @Hackwar already mentioned, can you please rebase the pr on the branch 4.2-dev? In the meantime I'm closing the pr. When ready please reopen so we can get it tested. Thanks for your help making Joomla better.
| Status | Pending | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2022-03-25 16:44:52 |
| Closed_By | ⇒ | laoneo |
Thank you for working on the nested sets code. I fear that this will not be really backwards compatible with the changed method names and thus would aim this directly at the 4.0 branch to be safe there.
While you are at it, could you also look into the code to store new ordering of items? Last time I looked, that code did some nasty stuff by simply deleting all rgt and lft values for the items to order, setting the order column to the values and then executing a rebuild(), instead of executing the right nested sets queries directly.