slow store due to large amount of nested Categories

Hello everyone,

I'm developing an ebookstore here: http://leisureebooks.com
As you can see, there are many storefront customizations.
The store will integrate with a substantial content provider (Ingram Digital) so it will have several hundred thousand ebooks in the catalog when ready (currently there are 200,000 for testing purposes). At the moment we're doing test integrations and ironing out a few problems before going live.
One of these problems is not related to the number of products...instead it's related to the larger number of categories/sub-categories, i.e. there are almost 4,000! Basically, if you want to cater for ALL 'Subjects' as described here:
http://www.bisg.org/what-we-do-0-136-bisac-subject-headings-list-major-subjects.php
it requires close to 4,000 (and from posting on many ebook related forums and surveys, this is what people want).

The problem is the site speed is seriously inhibited by this. It can take 25 seconds for the site to respond when you click the 'DRM' catagory for example. This drops to about 15 seconds if the 'Category Count' is disabled.
2 reasons why this speed issue will not be a runner:

• no customer is going to wait for a page to load
• the search engines will deliver little/no organic traffic to the store
  Therefore, I need to find a way to speed things up or else there is no chance of success.

I have searched the forum and found some hints:

1. disable 'Category Count'

   • I've done this (10 second improvement)

2. turn off the check for the Linked Products

   • As a test, I removed all linked products altogether but I think I need to comment out the code that actually does the checking. Can someone tell me what file this section of code is found in please?

3. Dedicated Server

   • already on a cloud with descent specs (eleven2.com, multi cpu, 4GB memory)

4. Optimizing your scripts and css

   • done nothing here yet...don't know how to

5. Compressing any scripts/css files on the site

   • done nothing here yet...don't know how to

6. query cache

   • not sure what I should do here

7. Changing storage engine in database

   • not sure what I should do here

Can I get some advise on the above please? Are they all still valid? Any hints on how to go about them?
Are there other experiments I should try...or general advice?

Some details:
Zen Cart 1.3.9h
Server OS: Linux
PHP Version: 5.3.6 (Zend: 2.3.0)
PHP Memory Limit: 64M
Database Data Size: 2,631,993 kB
Database Index Size: 169,881 kB
Database: MySQL 5.1.62-cll
HTTP Server: Apache

Thanks in advance,
Richie.

There's a FAQ article (http://www.zen-cart.com/content.php?63-how-do-i-speed-up-my-site) that identifies some switches (like the category count that you referenced) that can be thrown to help you improve your site's performance.

Let me say that I like your site, alot. I've seen a similar header category switcher on another commercial template that I like, and have seen it on a live free zencart template. But your template is the best looking...and also the highest tec, which could cause problems itself. I looked up your template and the demo moves pretty fast, but does not look to have everything that you do.

I ran your homepage through the validator and it reported 746 errors and 281 warnings.

Page speed at gtmetrics is an F, with a load time of 10.4 seconds. Page size is 3.18mb and and total requests are 344. You may be able to see the report here:

http://gtmetrix.com/reports/leisureebooks.com/HhvRGKi4

I've heard that there are very large zencarts, but your's is possibly the largest I've personally seen at 197,600 current products. But I understand that a large zencart should still perform well.

Since you have a very large site, perhaps you may have to think about removing things. Like perhaps the various scripts in your corners, which in my screen are among the last things to load.

Your body, once it appears, seems to load fairly fast.

In my screen, the main hang-up appears to be that header category switcher that we like. Could it be a large part of the problem? Image size associated with it?

BTW, how does your site perform when on the Classic template?

That's about the extent of my ability for now. I'm sure more knowledgeable people will soon come along.

In addition to trying the Classic template to see if a lighter-weight template moves faster, I'd probably briefly turn off the left column, to see if things move faster. After that, maybe even briefly turn off categories. Is the site still slow with no categories/products or left column showing? Might help trouble shoot to see where problem is.

The is the other large zencart of which I am aware:

coolcarpartsonline com

Has some 166k products and scripts in the corner. But it moves pretty well. He's a frequent poster and perhaps may have some thoughts on a larger site.

Thanks for the feedback guys...definitely a few things I need to look into.
I should have mentioned that I'm 100% sure that the size of the category tree is the source of my speed issue.
I ran several tests prior to posting this thread. I systematically removed each element in turn because I needed to know if any of the new features I added to the site were causing the problem. The conclusion of the tests was that when the categories were removed, the pageload time dropped to about 3 seconds.
Apologies for not making this clear in the original post.
Richie.

Understood. Still, does the site move faster when on the Classic template? Or no.

Hi SPH,
I tried to change template but my guys who do the coding must have disabled the ability to change template.
It will be next week before I can ask them to switch it.
Are there any other suggestions?
Will zen-cart always find it difficult to deal with a large amount of categories (4,000 in my case)? Am I looking at a re-coding job perhaps (and is this even possible)?
Thanks for your help and advice so far...much appreciated,
Richie.

Just curious if it speeds up with a lighter template. Knowing that you have so many categories and a slow loading problem, you might look in to stripping the template down, including mods. If the template and any mods are appreciably contributing to the slow loading.

Sure, I will try a lighter template.
I did try several tests by removing most of the other elements on the storefront...and the conclusion was that the categories was by far the biggest offender.
I will try a lighter template when I can (Monday next).

Back in the first post, I mentioned:
2. turn off the check for the Linked Products

• As a test, I removed all linked products altogether but I think I need to comment out the code that actually does the checking. Can someone tell me what file this section of code is found in please?
  Do you know where the code is that I must comment out to stop the 'check for linked products'?
  Thanks.

You appear to have Categories that hold both Categories and Products ...

Categories should hold Categories or Products, but never both ...

This could also be attributing to the issues that you are having ...

Bingo! Ajeh has probably nailed it.

Also, with such a large site, what is your server like? I understand that a bad host or too small a server can bring things down.

Hi ,
I had a couple of products at the same level as categories a couple of weeks back but I think I cleared them out. Perhaps I missed some. Can you give me an example where you spotted this?
I browsed a good few categories in admin and couldn't spot where is was happening (but there are many categories so I probably missed some).

To clarify the way it works (in my words)...assume catA contains catB and catC...where catB contains 5 products and catC contains 10. When I click on catA, all 15 products will be displayed...and when you click further down the tree to catB you will see 5 products only (and 10 when you click on catC etc).
Is this example accurate?

Sorry, forgot to add...I have a cloud server provided by eleven2.
See the CM500 option here: eleven2.com/cloud-servers
Are these guys ok? Is the server spec ok?

When I click on catA, all 15 products will be displayed..<<<

That's a new one for me, don't even know how that would work. In fact, my understanding is that it isn't supposed to.

CatA-Top Level...no products, just subcategories

SubCat B - 5 products
SubCat C - 10 products

Products only show when you are in each subcategory.

Can you possibly (and securely/safely-don't include url/admin) provide a screen shot of your categories in admin showing this?

My Alyce page here has several subcategories, but doesn't show any products until you get into the various subcategories:

http://www.prommart.com/index.php?main_page=index&cPath=122

I was referring to ballyc27 and the http://leisureebooks.com/ that looks like that has mixed Categories and Products in the Categories ...

Hello again,

I have the category structure setup to display all products under the selected category. Then when you click on a sub-category (if one exists), only the products in that category (and lower sub-categories if they exist) will be displayed.
I will PM my access details.

Do you think the server host and spec is ok?

Thanks,
Richie.

I am not quite sure what code changes you have done to show the Products of a Category and all of its subcategories as you move between the categories ... this could be part of the slow down ... usually as you navigate the Categories you are shown the subcategories or the products ...

One thing to ensure is that your server is optimized in the my.cfn for MySQL ... this is not my forte, but not having this configured properly will result in slow queries ...

Go to the Configuration ... Logging ... and turn on your:

Display The Page Parse Time
Display the page parse time on the bottom of each page
You do not need to store the times to display them in the Catalog

and you can see how slow the Query times are for your site ...

You might be able to talk with your hosting provider about access to your slow queries to see where the bottle necks are ...

To speed up your Admin, you could turn off a few checks that are done in the file:
/your_secret_admin/includes/modules/category_product_listing.php

by turning them off with the comment code in RED:

/*
      if (zen_get_products_to_categories($categories->fields['categories_id'], true, 'products_active') == 'true') {
        echo '  ' . zen_image(DIR_WS_IMAGES . 'icon_yellow_on.gif', IMAGE_ICON_LINKED);
      }
*/      

/*      
      if (zen_get_product_is_linked($products->fields['products_id']) == 'true') {
        echo '  ' . zen_image(DIR_WS_IMAGES . 'icon_yellow_on.gif', IMAGE_ICON_LINKED) . '<br>';
      }
*/      

That turns off the checks for Linked Products and will not display the Yellow icon if there are any ... usually this is a visual clue for you and not required ...

Another thing that speeds up the Admin for the Categories/Products is you will see either a green checkmark or a red X next to the Category dropdown on the corner of the screen ...

Click the green checkbox so it changes to the red X and now it does not have to build the list of Categories in the dropdown and will really speed things up ...

ballyc27, pay special attention to Ajeh as you have a member of the zencart team.

What I don't understand is why you don't just use zencart normally re the cat/subcat structure? Why is it necessary to see/load products as one moves through the cats/subcats? As I recall, the only time a zencart should have products in a top level category that also has subcategories is TEMPORARILY when moving cats/subcats around.

Your site is already under pressure from the sheer number of categories and products. Why make things worse? Or was your method an attempt to make things better?

Did you ever try setting things up normally and how did that work?