1.5.0.5 very slow

[Xsecrets]

It now works with the products count but the category structure is gone.

I thought that the product count was the slowing factor. So why isn't the category structure work now you think?

because my rewrite change the entire way that things were done. I replaced the category queries so that I could pull the product counts along with them instead of having to do 1000's of extra queries, the problem is that if you do that for the whole tree you loose the structure. If you don't you don't actually get all the products for each category just the ones assigned to that category not the subs.

There is no possible way to do it exclusively with the query as I have rewritten them, but I think there might be a way with only one additional query plus quite a bit of extra programming logic, but it still may be faster than all the product count queries. I'll try to get the vqmod updated with it when I get a chance, though I still don't have it all worked out in my head so it may still not be possible. Overall I think it was a very poor decision to change things up and do the product counts honestly.

[onlinephilately]

I have been think a little about this issue. Personally for my needs on the website I would like to have the product count and thought that it might be possible to store a count of the categories in a DB table. This DB table could then be called when the categories are displayed on each page.

Do you think this would be a viable way to go?

[Xsecrets]

I have been think a little about this issue. Personally for my needs on the website I would like to have the product count and thought that it might be possible to store a count of the categories in a DB table. This DB table could then be called when the categories are displayed on each page.

Do you think this would be a viable way to go?

If you feel like coding that all up then be my guest.

[ikhlief]

I have the same issue althought my website have couple of hundred of products, and I posted a subject here:
http://forum.opencart.com/viewtopic.php?f=19&t=34262

althought all of them say it is fast, but I feel it is still slow compared to the old V1.4.9.5!!! It sometimes freeze for more than 30sec. before the page respond.

A few more threads is posted here by others on the same issue:
http://forum.opencart.com/viewtopic.php?f=20&t=34292
http://forum.opencart.com/viewtopic.php?f=20&t=34241

I think it's time for considering this big problem by the developers.
Many thanks for your efforts

[rph]

Seems fine to me though you do have some fancybox images which are missing.

[Xsecrets]

yeah I browsed around that site a bit, and never saw anywhere near a 30 sec delay. I'd say the longest I saw was maybe just over 2 sec for the worst page load time I saw. I don't think you have anything to worry about.

[Xsecrets]

Ok I've changed the way I approached it all. Here's a new vqmod. It basically does the same thing as the default install, but it rolls it all into the category model function and enables caching, so if someone wants to give this one a try I would love to hear if it helps. This one should not mess with the display at all.

[cartpro]

How difficult it is to address in the core? We have this issue since from day one and we couldn't get this small fix in the core.

One suggestion, if you cache the html output of the category rendering that will improve the performance a lot. Even if you cache individual categories to the disk that will be lot of file open/close operations which slow you down. if you cache the html, it will be one IO call instead of 6000 calls (for 6000 categories).

[Xsecrets]

How difficult it is to address in the core? We have this issue since from day one and we couldn't get this small fix in the core.

One suggestion, if you cache the html output of the category rendering that will improve the performance a lot. Even if you cache individual categories to the disk that will be lot of file open/close operations which slow you down. if you cache the html, it will be one IO call instead of 6000 calls (for 6000 categories).

well the solution I have in place in the vqmod above falls somewhere in the middle. It caches the array so it should improve things quite a bit, while still leaving the html available for modification by designers and developers without having to dig into the model files.

[onlinephilately]

Just tested it on my site and the first load is slow but then when you start browsing around it is as fast as if I take away the product count.

I will test it a little more and see what the performance is but so far so good...

Great job!

[Xsecrets]

yeah like I said I did have to go back to a very similar way of running the queries, so the first load is going to be very similar to the original, but after that I think the caching should be very effective the way I've done it.

[onlinephilately]

It works great! Thx a lot!

Would you know how make the categories with an accordion function in v1.5?

[DirkZz]

If the count product query would be like this:

Code: Select all

SELECT COUNT(p.product_id) AS total,p2c.category_id FROM product p
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_to_category p2c ON (p2c.product_id=p.product_id)
WHERE pd.language_id = '1'
AND p.status = '1'
AND p.date_available <= NOW()
AND p2s.store_id = '0'
GROUP BY p2c.category_id

You wouldn't have to loop it right? And it gives you all the required results.
Am I missing something? Or is it really that easy? ( I spend less then 1 minute changing the query ).

Result example:
Showing rows 0 - 29 ( 59 total, Query took 0.0953 sec)

[DirkZz]

I've added this to my code and it seems to have improved the speed.

[Xsecrets]

If the count product query would be like this:

Code: Select all

SELECT COUNT(p.product_id) AS total,p2c.category_id FROM product p
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_to_category p2c ON (p2c.product_id=p.product_id)
WHERE pd.language_id = '1'
AND p.status = '1'
AND p.date_available <= NOW()
AND p2s.store_id = '0'
GROUP BY p2c.category_id

You wouldn't have to loop it right? And it gives you all the required results.
Am I missing something? Or is it really that easy? ( I spend less then 1 minute changing the query ).

Result example:
Showing rows 0 - 29 ( 59 total, Query took 0.0953 sec)

Wrong. That's what my first vqmod up there did. The problem is that you cannot do categories + subcategories that way. It simply won't return anything, for categories that don't have products, so if all your products are in the bottom level categories then it won't return anything unless you are putting in the subcategories too then it returns them along with the other ones and jumbles everything up.

[DirkZz]

If the count product query would be like this:

Code: Select all

SELECT COUNT(p.product_id) AS total,p2c.category_id FROM product p
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_to_category p2c ON (p2c.product_id=p.product_id)
WHERE pd.language_id = '1'
AND p.status = '1'
AND p.date_available <= NOW()
AND p2s.store_id = '0'
GROUP BY p2c.category_id

You wouldn't have to loop it right? And it gives you all the required results.
Am I missing something? Or is it really that easy? ( I spend less then 1 minute changing the query ).

Result example:
Showing rows 0 - 29 ( 59 total, Query took 0.0953 sec)

Wrong. That's what my first vqmod up there did. The problem is that you cannot do categories + subcategories that way. It simply won't return anything, for categories that don't have products, so if all your products are in the bottom level categories then it won't return anything unless you are putting in the subcategories too then it returns them along with the other ones and jumbles everything up.

Already got it working, and the numbers are correct.
If you have more then 4 categories then this way is (much) faster.
with 4 it is about even.

edit:
or isnt this what you are talking about?
Left-modded: right-normal
http://i.imgur.com/pPBsw.png

[Xsecrets]

with your sql you get how many products are in each category. The way the cart works. Lets just say that the first level category is Desktops and there are no products in that category directly. Then there is a category below that which is pc again no products in it. then a third level category which is Dell and there are 10 products. With your query desktops and pc categories will disappear completely and Dell is now the top level category. with the 10 products. With the default setup Desktops may have 150 products in it cause it counts all subcategories and their subcategories. then PC might have 75 for the same reason then you would have Dell with 10 which as a third level category doesn't even show up in the default setup.

[DirkZz]

I see your point.
But 1.5.0.5 has the same problem? Im not using the latest svn releases, because they are f-ed up.

http://imgur.com/zfD5Q

[Xsecrets]

no none of the 1.5 releases have that problem because he does the product count in a loop and the first thing he does is a recursive lookup to get all sub category id's then he uses that list to do the count, so you do get a true count of all products in the category and all subcategories. Unfortunately this behavior simply cannot be recreated using group by.

[DirkZz]

The product count is ok, but the menu doesn't add an extra sub item.
I didn't understand you at first, thanks for pointing me to this problem, ill see if I can think of something otherwise, too bad

EDIT:
Oc doesnt see the level-3 category as a subcategory of the level-2. Thats the problem.