Post by Xsecrets » Fri Jul 01, 2011 1:24 am

onlinephilately wrote: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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by onlinephilately » Mon Jul 04, 2011 7:27 pm

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?

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am

Post by Xsecrets » Mon Jul 04, 2011 9:28 pm

onlinephilately wrote: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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by ikhlief » Tue Jul 05, 2011 3:51 am

I have the same issue althought my website have couple of hundred of products, and I posted a subject here:
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:
viewtopic.php?f=20&t=34292
viewtopic.php?f=20&t=34241

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

Active Member

Posts

Joined
Wed Nov 24, 2010 9:15 pm

Post by rph » Tue Jul 05, 2011 4:22 am

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

-Ryan


rph
Expert Member

Posts

Joined
Fri Jan 08, 2010 5:05 am
Location - Lincoln, Nebraska

Post by Xsecrets » Tue Jul 05, 2011 5:48 am

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Xsecrets » Wed Jul 06, 2011 3:35 am

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.

Attachments

Hopefully improved categories.


OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by cartpro » Wed Jul 06, 2011 3:52 am

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

Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro

Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart


New member

Posts

Joined
Fri Oct 29, 2010 1:50 am

Post by Xsecrets » Wed Jul 06, 2011 6:18 am

cartpro wrote: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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by onlinephilately » Wed Jul 06, 2011 8:12 pm

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!

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am

Post by Xsecrets » Wed Jul 06, 2011 9:06 pm

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by onlinephilately » Wed Jul 06, 2011 9:10 pm

It works great! Thx a lot!

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

New member

Posts

Joined
Thu Jan 27, 2011 3:14 am

Post by DirkZz » Thu Jul 07, 2011 5:09 pm

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)
Image

New member

Posts

Joined
Thu Jun 16, 2011 3:34 pm

Post by DirkZz » Thu Jul 07, 2011 7:38 pm

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

New member

Posts

Joined
Thu Jun 16, 2011 3:34 pm

Post by Xsecrets » Thu Jul 07, 2011 9:17 pm

DirkZz wrote: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)
Image
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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by DirkZz » Thu Jul 07, 2011 9:21 pm

Xsecrets wrote:
DirkZz wrote: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)
Image
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

New member

Posts

Joined
Thu Jun 16, 2011 3:34 pm

Post by Xsecrets » Thu Jul 07, 2011 10:11 pm

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by DirkZz » Thu Jul 07, 2011 10:21 pm

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

New member

Posts

Joined
Thu Jun 16, 2011 3:34 pm

Post by Xsecrets » Thu Jul 07, 2011 10:39 pm

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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by DirkZz » Thu Jul 07, 2011 10:44 pm

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.

New member

Posts

Joined
Thu Jun 16, 2011 3:34 pm
Who is online

Users browsing this forum: No registered users and 40 guests