Author Topic: build categories tree very slow (30 seconds for 8000 categories).  (Read 476 times)

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
what is the purpose of this line in \storefront\controller\blocks\category.php???
$output = array_merge($output,$this->_buildCategoryTree($all_categories,$category['category_id'], $category['path']));
I have 8000 categories (2 languages which is double, then 16000 records), this line wastes 30 seconds to build the categories tree.
Can I cancel this line? If I cancel it, will abantecart builds the categories tree correctly? Any affect on abantecart system?
If it is not allowed to cancel it, how can I improve the performance for rebuilding the categories tree?
Thank you.
« Last Edit: April 15, 2018, 03:49:03 AM by smallfish »

Offline abantecart

  • Administrator
  • Hero Member
  • *****
  • Posts: 4088
  • Karma: +244/-9
    • View Profile
    • Ideal Open Source Ecommerce Solution
Re: build categories tree very slow (30 seconds for 8000 categories).
« Reply #1 on: April 15, 2018, 09:43:19 AM »
This is a display block for categories. Manipulation of this code will only affect the display.

Check if you have product count in categories enabled. This will slow query down.
We need your help to build better free open source eCommerce platform for everyone.
See how you can help

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: build categories tree very slow (30 seconds for 8000 categories).
« Reply #2 on: April 15, 2018, 10:22:02 AM »
This is a display block for categories. Manipulation of this code will only affect the display.

Check if you have product count in categories enabled. This will slow query down.
In fact, I didn't find where to disable the product count in backend admin. However, I have modified the sql statement in the script to ignore the product count and categories_to_store.
So that I can make sure that the mentioned program line wastes a lot of time to build the categories tree. It calls itself many times to build the whole categories tree.
Finally, I have forgotten to mention that there is no products in the database. I just have created 8000 categories only. (4 main categories, each has 20-100 2nd level sub categories and each 2nd sub category has 2 to 10 3rd level sub-categories., each 3rd sub-category has 2 to 10 4th level sub-categories.)
Can you rewrite or redesign the categories table to make it build the categories tree fast?
Thank you!
« Last Edit: April 15, 2018, 08:37:56 PM by smallfish »

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 1753
  • Karma: +240/-10
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: build categories tree very slow (30 seconds for 8000 categories).
« Reply #3 on: April 16, 2018, 06:25:18 AM »
imho we cannot to make sql better. Every category have parent_id and tree will builds recursively anyway. But probably  in case when  you have a lot of categories you should improve template of categories in way when you ask only one branch of parent category. I mean ajax call for expand button click.
In this case main tree will contain only root categories. And then after click you asks children.
Otherwise you will have server performance problems.
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline abantecart

  • Administrator
  • Hero Member
  • *****
  • Posts: 4088
  • Karma: +244/-9
    • View Profile
    • Ideal Open Source Ecommerce Solution
Re: build categories tree very slow (30 seconds for 8000 categories).
« Reply #4 on: April 16, 2018, 07:21:55 AM »
Category model and SQL can be improved for larger categories count. We will add this to TODO list for version 2.0
We need your help to build better free open source eCommerce platform for everyone.
See how you can help

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: build categories tree very slow (30 seconds for 8000 categories).
« Reply #5 on: April 17, 2018, 11:21:53 PM »
Category model and SQL can be improved for larger categories count. We will add this to TODO list for version 2.0
Good to know that you will improve it. It seems that recursive function is not good for building large categories tree. The category table may be needed to re-design for for better performance.