Slowness when loading page with many attribute values

[gehaynes]

I have been working on this issue for the past two days as well and have been through every line of the attributes.php and commented out all of the query calls that I think we do not need. We do need to price products by attributes, but not by attributes that are causing the problem.

I am running into a situation where every time I add a new attribute to a dropdown box it adds about 20 extra queries according to the query report. My problem is that ultimately I need three dropdown boxes with 200 values in each which adds up to around 12000 additional queries, which brings the page to a grinding halt.

Here is a comparison of two identical products with the only difference being the addition of the dropdown attribute on product #2 with 2 values.

Product #1 - http://www.bathjunkiestaug.com/index...products_id=85

Product #2 - http://www.bathjunkiestaug.com/index...products_id=86

I have seen people mention dividing the products out into smaller categories, but this will not work for my situation. Can anyone give me some guidance as to where to look so I can cut some more overhead. I am not afraid to get my hands dirty as long as someone can give me a place to start...

Thanks,
GUY

[Kim]

Another place to start: http://www.websiteoptimization.com/s....com/index.php

OPTIMIZE your images and pages ... 340k+ is too big

[jameszeller]

As the OP, thanks to all that have commented here. I haven't checked in until tonight.
My host is CrystalTech. They let me move my database to another server that they said, or I assume, had less load. No real difference in the speed.
I have since opted for text boxes for the ones with many options. I will use drop downs just for 10 or less values.

Does anyone know if CrystalTech could be perhaps a slower host than others?

James

[misty]

You may not have to move servers, IF you
reduce all those images to more respectable size,
then retest loading times.
as Kim has suggested.

[DrByte]

You might also look up other posts about your host to see what sort of problems others have had with them, whether Zen Cart or not. Might try other spellings of their name too.

[gehaynes]

Another place to start: http://www.websiteoptimization.com/s....com/index.php

OPTIMIZE your images and pages ... 340k+ is too big

Thank you, but optimizing a couple of images will probably not overcome the slowness created by the attributes. In my two examples you will notice that the parse time triples and 52 additional queries are run after the attribute (dropdown with only two values) is added to the page for Product #2.

I will keep working on it and let you know what I come up with. Perhaps there is a way attributes can be cached and only recreated when changes are made. Pulling static HTML will be much quicker than recreating from the db.

Thanks,
GUY

[gehaynes]

Here are the results of my testing. My issue ended up being solved pretty easy with no code changes, but here are the things I discovered in the process that may help others:

Attributes Module - [modules/attributes.php]
The module loops through each Option Name and then through each individual Option Value to create the attribute display types (checkbox, radio, dropdown, etc...). Each type has its own display options that are also created. The primary bottleneck I found was around line #118 and #119. This is where the pricing functions [functions/functions_prices.php] are called for each individual Option Value. This is where the huge numbers of queries were coming from in my initial problem.

I had three Option Names each with 200 Option Values being created as dropdown boxes. This equated to around 12000 queries to create these three boxes.

My Solution:
After toying with the idea of caching the dropdown boxes instead of creating them on the fly, I started looking at how the pricing was called for my other attributes (some have pricing and some do not). I determined that the key to avoiding all of those queries was to make sure I did not use pricing for my large attributes and make sure I selected "No" for "Include in Base Price
When Priced by Attributes" when adding the attributes. They were all set to $0 anyway, but by not checking that option originally it was causing the cart to try and pull the pricing information anyway. Once I made the change the pages loaded in a respectable time.

Thoughts for Improvement:
It would seem that the pricing information for each attribute could be pulled as part of the initial query of Option Values created in attributes.php (line #75) then passed to the pricing functions. This would alleviate the extra query being run for each individual Option Value as they are created. If I get time after this project I will go back and see if this is possible with the current data structure.

I hope this information helps the other posters with this problem. If your attributes do not contain pricing information, make sure that option is turned off!

[kcb410]

My Solution:
After toying with the idea of caching the dropdown boxes instead of creating them on the fly, I started looking at how the pricing was called for my other attributes (some have pricing and some do not). I determined that the key to avoiding all of those queries was to make sure I did not use pricing for my large attributes and make sure I selected "No" for "Include in Base Price
When Priced by Attributes" when adding the attributes. They were all set to $0 anyway, but by not checking that option originally it was causing the cart to try and pull the pricing information anyway. Once I made the change the pages loaded in a respectable time.

I have a section of promotional products on my site that loads very slowly, 5-7 seconds for most product pages. After reading the above thread I realize that for most of the attributes I have "Yes" for "Include in Base Price When Priced by Attributes" checked when most have no cost associated to them.

My questions....Is this still an issue with ZC 1.3.8a? and if so is there a way I can switch all attributes with no cost to "No" for "Include in Base Price When Priced by Attributes" without going to all 1200 products??

Any help is appreciated.. Thanks!!

[DrByte]

Here's a thought which I have NOT tested thoroughly to determine whether the mathematical calculations or pricing display is negatively affected. Would be interested in your feedback:

/includes/functions/functions_prices.php
line 124 circa v1.3.8a:
Add the additional condition to the SELECT query, as shown:

$product_att_query = $db->Execute("select options_id, price_prefix, options_values_price, attributes_display_only, attributes_price_base_included from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . (int)$products_id . "' AND options_values_price != 0 and attributes_display_only != '1' and attributes_price_base_included='1'". " order by options_id, price_prefix, options_values_price");

REMINDER: I apologize that I've NOT tested this to determine any risk of negative impact. Use at own risk.

[kcb410]

Thanks Dr Dyte.
On Monday I'll test it locally and then try it on my website and report back.