slow store due to large amount of nested Categories

[ballyc27]

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

[lat9]

There's a FAQ article (http://www.zen-cart.com/content.php?...eed-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.

[SPH]

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.

[SPH]

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.

[SPH]

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.

[ballyc27]

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.