Very slow category handling after 3.8

[sandomatyas]

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]

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]

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.

[Studio 42]

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]

Give me the sinlge query and I can play with it.

[Studio 42]

This is my query

Code Select Expand


$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]

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.

[Milbo]

This is my query

Code Select Expand


$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

[sandomatyas]

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]

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]

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]

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';

[Milbo]

Okey looks like I did an error last time. I rechecked your first query.

Max, it's a completely different approach
...
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

lol, your different approach is not a big deal. It is just the bet, which works faster. "Load all categories at once (big memory load), sort it by php (can be very slow)" or "Load categories per level (more sql requests, can be slow), use php to combine it (faster than doing all the job by php.)"

I just dont see that the new optimisations were even checked. The new code does not use the category_categories table at all! It could be interesting to use a query similar to Studio42s, but then just using the main table and using the optimisation fields like "has_children".

Your second query looks very interesting and reveals some nice ideas. But I dont get it to run. I just dont get any result with the sample data. But I also dont know for what it is necessary, when the first query already loads anything interesting.


category_categories  table is deprecated and just for legacy reasons there. For xample that studio42s code is still working. But it maybe removed the next years.

You can always send me better code. Usually I take it, but when I see, that people do  not even notice that this table is not longer used,...

[Studio 42]

Hum, i think you simply do not tried to check why this is faster.
In back-end, you load all the categories, so (big memory load) is more for virtuemart to load all from cache or not because i only load the info needed (3 fields) ?
It does not sort by PHP check my query, the categories are already sorted by mysql.
I use this query on a shop in back-end to display 13129 categories (no cache) and load in +/- 0.6 secondes but Virtuemart need 4,1 secondes
With cache 0.00010 secondes and virtuemart need 0.2 secondes

Memory used :
before cache : 8161952
after cache : 400
I do not know why only 400 is reported, certainly an internal cache

Virtuemart Memory used :
before cache : 11250872
after cache : 786504

So it's not true that this logic use more time or memory. I have done many test before, to be sure it works fine

[Studio 42]

About second query, this permit to load all parents from a category in 1 time
lvl in the query is to tree level and can be used to find a parent category in my tree generate in the first query