45,000 queries to load my categories? Horrid performance

I'm setting up a new store with a very broad category heirarchy.

Loading the 1st and second level categories has been VERY problematic. When I click on one category from the top level it takes nearly 2 minutes to load and shows 45k+ queries executed!

How is that possible? Is it preloading all the sub-categories? It's making usage of the store infeasible. Any way to tweak it?

Thanks!!!

What version of Zen Cart?
What addons do you have installed?
How many categories do you have?
Have you tried turning off category counts? Admin->Configuration->My Store->Show Category Counts (It's very intensive calculating children through all levels when you have huge numbers of categories)

It's version 1.3.8. I AM on a shared hosting environment (soon to change) but relatively speaking most response times are good, it's just the first/second level category clicks that seem to be terribly inefficient. Once I get to level 3 of the tree the number of queries goes down to sub 500 and performance is acceptable.

I have turned off Category counts. I also turned on GZIP but I doubt this would make a difference because the page being loaded only has images for 2-3 of the 35 subcategories.

I can't tell you exactly how many categories I have, but as reference if I export using Easypopulate I have 17k category rows. (That will include parents though, so this particular branch probably has over 7000).

Mods installed are:

1. Easypopulate
2. Multi-site
3. Sales report
4. Dual Pricing.
5. Supplier interface.

I'm an experienced systems guy but woefully short on PHP and MySQL knowledge.

Thanks for the quick reply.

Forgive the slightly altered topic of this question ... but ... how exactly are you trying to display 7000 categories on a page?

Give us a link to see your site

DrByte:

Forgive the slightly altered topic of this question ... but ... how exactly are you trying to display 7000 categories on a page?

I misunderstood. I have 17k TOTAL. However the category I am trying to expand has about 35. (with a total of 7000 on this branch at all levels beneath it The breakdowns are:

Level 1 - 5 categories
Level 2 - 35 categories for each level 1
Level 3 - 20 categories for each level 2
Level 4 - 4 categories for each level 3

So when I click on the level 1 category, it does 45k queries to expand to level 2. I cannot post a link to the site unfortunately because it is under Non-Disclosure at the moment. My question really is, why when I am only expanding out to 35 categories is the system doing 45k queries. I can only assume it is reading all categories beneath it.

MotoDelta,

Can not really expound without seeing what and how you are organized...

I have done sites with 100,000+ products and parse times are normally sub-second on a fast optimized server

Under Configuration --> logging you can turn off Store Page Parse Time, Display Page Parse Time and Store Database Queries - but the big user of mysql resources as Dr Byte has said is to turn off Category Counts.

Vger

kobra/DrByte, I've PM'd you the address.

Thanks for the assist!

I thought I had it figured, but didn't.

Do any of your products use attributes? Are any products set to priced-by-attributes?
Do you have any Specials or Salemaker sales active?
Does the performance change if you're logged in as a discounted customer (ie: using the dual-pricing feature you added)?
Do you have the store configured for tax-included pricing? or the normal tax-is-added-afterward mode?

DrByte:

Do any of your products use attributes? Are any products set to priced-by-attributes?
Do you have any Specials or Salemaker sales active?
Does the performance change if you're logged in as a discounted customer (ie: using the dual-pricing feature you added)?
Do you have the store configured for tax-included pricing? or the normal tax-is-added-afterward mode?

No attributes yet.
No sales yet
I'll check on discounted customer.
I used normal tax-added pricing.

I've just whacked the data to load ONLY the categories (no products) because there was an issue with the product-to-category mapping. This won't help the performance (saw this same issue before products were added) but will help me narrow down some queries.

Well, since a big part of building the page that's seen when a given category is selected is the identifying and retrieving of products related thereto, I would expect to see a performance change if you have no products.

True,

But if there are no products in the category I am selected, just more categories, then would it still matter?

Well if the problem is with how your products are linked to the categories and it's getting stuck recalculating things for some reason, then some bottlenecking wouldn't be a surprise.

It seems to be operating quite speedily right now, without the products.

How exactly did you add all your categories and products?

Even with no products loaded I am seeing the bad performance so I don't think it's related to product linking. I am using easypopulate to load data.

If you click on "Racing" category off of home, I get a 2 minute load time, but it has 28 categories in the next level.

The "New" category has 90 categories in the next level and it loads faster.

THe difference? New has 900 total categories in it's "tree". Racing has 7k categories in it's tree.

It appears that the subcategories are being read and not displayed I would think. The "depth" of the category tree seems to impact performance hinting at perhaps some sub-optimal SQL?

It's only the first level expansion of categories that is really giving me performance fits. Everything else is "reasonable" based on my slow host.

MotoDelta:

If you click on "Racing" category off of home, I get a 2 minute load time, but it has 28 categories in the next level.
The page is loading in about 1.5 seconds for me, for that category.

MotoDelta:

The "depth" of the category tree seems to impact performance hinting at perhaps some sub-optimal SQL?
That's quite possible. That part of the code has been completely rewritten for v1.4.0, along with refactoring how products are loaded by using some factory classes ... all equating to huge performance improvements.
Alas, that code isn't available for public consumption yet. But the comment is noted and already addressed.

DrByte:

That's quite possible. That part of the code has been completely rewritten for v1.4.0, along with refactoring how products are loaded by using some factory classes ... all equating to huge performance improvements.
Alas, that code isn't available for public consumption yet. But the comment is noted and already addressed.

I take that back. While the comments about optimization are correct, the SQL that builds the category list is extremely simple. It's a simple join between 2 tables with a where clause indicating the parent id. That should be quick, even on a slow server. An additional lookup is done for each category in that resultset to generate its proper full cPath value, thus generating a few more queries.