Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

rebuild_tree performance issue - Takes too much time #83

Open
akhil018 opened this issue Dec 12, 2023 · 1 comment
Open

rebuild_tree performance issue - Takes too much time #83

akhil018 opened this issue Dec 12, 2023 · 1 comment

Comments

@akhil018
Copy link

The rebuild_tree operation in SQLAlchemy-MPTT is experiencing performance problems and taking an unexpectedly long time to complete, particularly when dealing with large datasets. This issue significantly impacts the usability and efficiency of the library.

Expected Behavior:
The rebuild_treeoperation should complete within a reasonable time frame, even as the size of the dataset grows.

Actual Behavior:
The rebuild_tree operation is taking an unreasonably long time, especially noticeable when dealing with larger datasets, adversely affecting the overall performance of the library.

@evgenybf
Copy link

evgenybf commented Jan 10, 2025

@akhil018 You didn't specify the size of the dataset when it starts to be unreasonably slow. I think it would be useful.

Note, that there is no magic here: Roughly,, on insert you have to update all parent nodes and the nodes whose id is larger than the id of the new node. In the worst case, it can be all records in the table.

There is good article on how it works:
https://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

rebuld_tree() may take even longer if the tree is not allowed to be loaded to the memory.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants