45,000 queries to load my categories? Horrid performance

[MotoDelta]

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!!!

[DrByte]

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)

[MotoDelta]

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.

[DrByte]

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

[kobra]

Give us a link to see your site

[MotoDelta]

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.

[kobra]

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

[Vger]

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

[MotoDelta]

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

Thanks for the assist!

[MotoDelta]

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