Too many attributes? Store too slow

I have searched for a few days, and found some help, not not enough for this issue. My load time for product pages is approx. 9 seconds, way too long.

I am on v1.3.9h
My site is http://tinyurl.com/6r9v7cm
I have done the jquery cache module, that helped a little.
I have done the recommendations from this thread that I could. https://www.zen-cart.com/tutorials/index.php?article=21
I have the advanced shipper module
Based on advice from my zen cart expert I moved from httpme to a VPS (level 3) on Hostgator.
I have optimized database
I have optimized all my images
and purged debug files

So the attributes I have (and they all seem necessary to collect the information I need.
Screen Charge (radio)
Product Color (dropdown)
Mixed Colors Breakdown (text)
Ink Color (dropdown)
Metallic ink (radio)
Art Charge (radio)
Print Design Front side (dropdown)
Print Design Back side (dropdown)
Extra Options (checkbox)
Attached Art file (file)
2nd Attached art file (file)
Text for imprint area (text)
Delivery Date (text)

All of this information is necessary to actually produce an order. I don't know if I could combine any of them, (don't see how)

The steps done above have not gotten me to a usable speed.

I think I may need an Apache administrator who is truly expert in setting up VPS accounts. Don't know how to do that.

I have to think there are other sites that have a larger number of attributes that have made it work.

thanks

rliddle:

I have done the recommendations from this thread that I could. https://www.zen-cart.com/tutorials/index.php?article=21
What do you mean by "that I could"? Which points did you skip, and why?
What are your parse times?
If you're running a managed VPS then the Webserver Tuning Tips link, in the same article you quoted, contains several things for the VPS manager to study and implement.

hmmm parse times are not showing. have it turned on.
and not commented out here.
includes/templates/bluecart/common/tpl_main_page.php

Line #171 : <div class="smallText center">Parse Time: <?php echo $parse_time; ?> - Number of Queries: <?php echo $db->queryCount(); ?> - Query Time: <?php echo $db->queryTime(); ?></div>

Regarding https://www.zen-cart.com/tutorials/index.php?article=21

I did 1,2,3, and 4 except these parts in 4.

• Enable Price Factor -- If you're not using price-by-attributes support, turn this off to stop those calculations and the related queries
• Enable Qty Price Discount -- If you're not doing quantity discounts, turn this off
  as I am using those.

5. Not applicable
6. Put them into categories
   7 not appicable, only slow on product pages
7. did gzip
8. did this
9. did this
10. working on
11. this is where I get lost.

OK got parse time

Parse Time: 8.386 - Number of Queries: 1665 - Query Time: 7.37952516934

So, the parse time output shows that 7.4 seconds of the total 8.4 seconds used for preparing outputing the page was used up by database wait-time.
So, it suggests that the bottleneck is the database, which suggests that MySQL isn't very well tuned ... ie: usually that means it needs more RAM allocated to various buffers in the my.cnf configuration settings that the server Administrator has access to configuring.

DrByte:

So, the parse time output shows that 7.4 seconds of the total 8.4 seconds used for preparing outputing the page was used up by database wait-time.
So, it suggests that the bottleneck is the database, which suggests that MySQL isn't very well tuned ... ie: usually that means it needs more RAM allocated to various buffers in the my.cnf configuration settings that the server Administrator has access to configuring.

Thank you for the reply, now the issue. I don't have a server Administrator. Have asked Host Gator to tune it up. They replied with this. Hello,

"Thank you for contacting HostGator support. I ran an optimize against the database. A few updates were made but most tables were already up to date. We appreciate your patience. Please contact us again if you need further assistance."

I think I need a little more expertise. Any way to find that expertise?

You've described that your store requires the complexi ty of attributes that you've set up.
But, if your hosting server isn't suitable for your needs, you may need to get a different service ... something tuned more appropriately for a database-driven ecommerce system.
It could be that the server you're on has too many other customers, and thus is not able to handle the demand you're placing on it.
Or, the VPS you've been put onto isn't equipped with enough resources to do the job.

Ask them how you can get more RAM available to MySQL, and how you can get them to tweak the my.cnf to make use of more RAM available to it.

I moved server, (what a nightmare) and have slightly better numbers, but still not usable.

Parse Time: 6.054 - Number of Queries: 1665 - Query Time: 5.01710750447

Trying to get them to "get more RAM available to MySQL, and how you can get them to tweak the my.cnf to make use of more RAM available to it. "

But not having luck, getting them to understand it.

I was having a similiar problem, and I leased a server to solve it. It costs me $99 monthly, but the response is lightning fast.

Ray, could you tell me where? I am using a vps now. seems to have some power, but not enough apparently.

Which particular product is specifically slow?

Hello DrByte. They are all slow. Stadium cups, Wine wraps, etc.

Could you go to phpMyAdmin and search the table:
products_attributes

for the products_id 12 ... I think that is Stadium Cups ... and see how many Attributes are on that Product?

Ajeh, I think this is it.

SELECT *
FROM zen_products_attributes
WHERE products_id =12
LIMIT 0 , 30

Showing rows 0 - 29 ( 760 total, Query took 0.0008 sec)

On a test site I have 696 attributes:
Parse Time: 2.983 - Number of Queries: 15829 - Query Time: 2.39863640736

and 1,006 Attributes:
Parse Time: 4.981 - Number of Queries: 22641 - Query Time: 4.16505090627

The amount of memory and configuration for it is really important for the database to speed things up ...

I do not have that price updater you are using nor SEO/rewrites so I am not sure how much that might add to this ...

As I said, the MySQL server's performance needs some serious tuning. And that'll need to be done by whoever manages the database server. They're the experts in that area, and they're the only ones with access to make the required changes.
https://www.zen-cart.com/tutorials/index.php?article=287

OK, I got my site to do the changes referenced. Got this reply.

We have followed all the instructions provided in the URL you provided and have made the modifications for Apache, PHP & MySQl

Still at:
Parse Time: 5.246 - Number of Queries: 1666 - Query Time: 4.27782364699

(rolleyes)
Get them to give you a copy of the active my.cnf file. I suspect they've either done nothing to it, or nothing of value. And maybe didn't restart MySQL for the changes to take effect.
Or they can't touch it for fear that it'll break things for other people using the server ... in which case you need a hosting company who knows what they're doing.

I was kind of wondering about that. It took them about 30 mins to do that, and it was a pretty blanket reply. I requested the copy. any idea how to find someone who is an expert on doing this? Simple searching on Google was not very helpful.

And, thank you for hour help.