News:

Support the VirtueMart project and become a member

Main Menu

Very slow category handling after 3.8

Started by sandomatyas, September 03, 2020, 15:56:55 PM

Previous topic - Next topic

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

#2
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

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.
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


This is my query
$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

Quote from: sandomatyas 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?"
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

Quote from: Studio 42 on October 06, 2021, 09:27:46 AM

This is my query
$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

QuotePlease 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

Quote from: Studio 42 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


+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

#12
Okey looks like I did an error last time. I rechecked your first query.
Quote from: Studio 42 on October 14, 2021, 19:23:58 PM
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,...
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

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