Author Topic: Very slow category handling after 3.8  (Read 1931 times)

sandomatyas

  • Jr. Member
  • **
  • Posts: 327
Very slow category handling after 3.8
« on: September 03, 2020, 15:56:55 pm »
I explained the problem here: http://forum.virtuemart.net/index.php?topic=145164.msg515325#msg515325
The problem is with rekurseCategories function in VirtueMartModelCategory, it generates a huge amount of queries to build the category tree instead getting all of the categories with a single query and build the tree only with PHP functions.

sandomatyas

  • Jr. Member
  • **
  • Posts: 327
Re: Very slow category handling after 3.8
« Reply #1 on: August 12, 2021, 13:39:21 pm »
Any guess with that?

The issue again:
"There is a site with ~15.000 categories. As far as I can see VM uses huge amout of database queries when loads the tree recursively and it takes time, ~20-30 secs when it's not in the cache. Could you please consider optimizing this part of the code?"

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10124
  • VM3.9 Eagle Owl
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: Very slow category handling after 3.8
« Reply #2 on: October 04, 2021, 21:10:12 pm »
https://dev.virtuemart.net/attachments/1290/com_virtuemart.3.8.9.10553_package_or_extract.zip

Or make a suggestion yourself. But the new cache is really a lot different, try it.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4486
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Very slow category handling after 3.8
« Reply #3 on: October 05, 2021, 11:03:43 am »
I use 1 query to get all categories in my virtuemart category pro module and PHP to rebuild the tree.
It's really faster and can be cached See https://pro.st42.fr/taxonomy
Note that the link is not really fast, this is a test displaying 3 times another ways the categories and need more then 1 seconde because the render

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10124
  • VM3.9 Eagle Owl
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: Very slow category handling after 3.8
« Reply #4 on: October 05, 2021, 14:14:45 pm »
Give me the sinlge query and I can play with it.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4486
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Very slow category handling after 3.8
« Reply #5 on: October 06, 2021, 09:27:46 am »

This is my query
Code: [Select]
$q ='SELECT cc.category_parent_id as pc, c.virtuemart_category_id as id';
if($count) $q .=',count(p.virtuemart_product_id) as total';
if($FB === false) $q .=', l.category_name as name, l.slug';
else $q .= ', IFNULL(l.category_name,l2.category_name) as name, IFNULL(l.slug,l2.slug) as slug';
$q .= ' FROM #__virtuemart_categories as c
LEFT JOIN #__virtuemart_category_categories as cc ON cc.category_child_id = c.virtuemart_category_id
LEFT JOIN  #__virtuemart_categories_'.VMLANG.' as l ON l.virtuemart_category_id = c.virtuemart_category_id';
if($FB) $q .= ' LEFT JOIN  #__virtuemart_categories_'.VmConfig::$jDefLang.' as l2 ON l2.virtuemart_category_id = c.virtuemart_category_id';
if($count) $q .=' LEFT JOIN  #__virtuemart_product_categories as pc ON pc.virtuemart_category_id = c.virtuemart_category_id
LEFT JOIN  #__virtuemart_products as p ON p.virtuemart_product_id = pc.virtuemart_product_id AND p.published = 1
';
$q .= ' WHERE c.published = 1 GROUP BY c.virtuemart_category_id order by pc,cc.ordering,name ASC';// and category_parent_id ='.$this->db->quote($root_id);
$db->setQuery($q);
But with only the query, i do not think you can understand how to write the loop and HTML render

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10124
  • VM3.9 Eagle Owl
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: Very slow category handling after 3.8
« Reply #6 on: October 11, 2021, 23:15:32 pm »
Any guess with that?

The issue again:
"There is a site with ~15.000 categories. As far as I can see VM uses huge amout of database queries when loads the tree recursively and it takes time, ~20-30 secs when it's not in the cache. Could you please consider optimizing this part of the code?"
btw do you use the optimise sql category setting? because that decreased the time heavily already.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10124
  • VM3.9 Eagle Owl
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: Very slow category handling after 3.8
« Reply #7 on: October 11, 2021, 23:25:48 pm »

This is my query
Code: [Select]
$q ='SELECT cc.category_parent_id as pc, c.virtuemart_category_id as id';
if($count) $q .=',count(p.virtuemart_product_id) as total';
if($FB === false) $q .=', l.category_name as name, l.slug';
else $q .= ', IFNULL(l.category_name,l2.category_name) as name, IFNULL(l.slug,l2.slug) as slug';
$q .= ' FROM #__virtuemart_categories as c
LEFT JOIN #__virtuemart_category_categories as cc ON cc.category_child_id = c.virtuemart_category_id
LEFT JOIN  #__virtuemart_categories_'.VMLANG.' as l ON l.virtuemart_category_id = c.virtuemart_category_id';
if($FB) $q .= ' LEFT JOIN  #__virtuemart_categories_'.VmConfig::$jDefLang.' as l2 ON l2.virtuemart_category_id = c.virtuemart_category_id';
if($count) $q .=' LEFT JOIN  #__virtuemart_product_categories as pc ON pc.virtuemart_category_id = c.virtuemart_category_id
LEFT JOIN  #__virtuemart_products as p ON p.virtuemart_product_id = pc.virtuemart_product_id AND p.published = 1
';
$q .= ' WHERE c.published = 1 GROUP BY c.virtuemart_category_id order by pc,cc.ordering,name ASC';// and category_parent_id ='.$this->db->quote($root_id);
$db->setQuery($q);
But with only the query, i do not think you can understand how to write the loop and HTML render

This is not looping. it gives me one level. Please use the sql optimisation and the new cache of the last vm3.9
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

sandomatyas

  • Jr. Member
  • **
  • Posts: 327
Re: Very slow category handling after 3.8
« Reply #8 on: October 14, 2021, 07:50:05 am »
Quote
Please use the sql optimisation and the new cache of the last vm3.9
you mean 3.8.9?
I can't see 3.9 here: http://dev.virtuemart.net/projects/virtuemart/files

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4486
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Very slow category handling after 3.8
« Reply #9 on: October 14, 2021, 19:23:58 pm »
Max, it's a completely different approach
The query get all categories in 1 time and after you loop by parent to do the categories tree by parent_id reference.
I have tested many ways to get the tree and this is really the fastest.
As I said in the previous post, you can't figure it out, if you don't have my module to study all the code

sandomatyas

  • Jr. Member
  • **
  • Posts: 327
Re: Very slow category handling after 3.8
« Reply #10 on: October 15, 2021, 14:49:52 pm »
Max, it's a completely different approach
The query get all categories in 1 time and after you loop by parent to do the categories tree by parent_id reference.
I have tested many ways to get the tree and this is really the fastest.
As I said in the previous post, you can't figure it out, if you don't have my module to study all the code


+1 for this ...  I also builded my own script but a bit buggy and doesn't handle all of the use cases well (ordering, filtering, etc), but I agree that this would be the fastest aproach. One big query to fetch all of the categories and build the category tree only with PHP, without more SQL queries.

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4486
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Very slow category handling after 3.8
« Reply #11 on: October 18, 2021, 09:45:22 am »
And this query get all parent categories IDs in 1 sql
             // get all active child / parent_id
            $q ='SELECT T2.category_child_id
               FROM (
                  SELECT
                     @r AS _id,
                     (SELECT @r := category_parent_id FROM #__virtuemart_category_categories WHERE category_child_id = _id) AS category_parent_id,
                     @l := @l + 1 AS lvl
                  FROM
                     (SELECT @r := '.(int)$id.', @l := 0) vars,
                     #__virtuemart_category_categories m
                  WHERE @r <> 0) T1
               JOIN #__virtuemart_category_categories T2
               ON T1._id = T2.category_child_id
               ORDER BY T1.lvl DESC';