Queries

So, I have a few customer's website with a great deal of attributes, but under 700 products each. One is 1.5.0 (with Data Digger Query cache installed, but upgraded for 1.5.0) the second is 1.5.1 with the built in caching.

The sites are just too slow.... here are some query examples

1.5.1 site
First load Parse Time: Parse Time: 0.693 - Number of Queries: 459 - Query Time: 0.243524054993
Second Load: Parse Time: Parse Time: 0.609 - Number of Queries: 458 - Query Time: 0.149512161057
Google PageSpeed Score 92/100

1.5.0 site
First load Parse Time: Parse Time: 0.644 - Number of Queries: 86 - Query Time: 0.101940719681
Second Load: Parse Time: Parse Time: 0.310 - Number of Queries: 94 - Query Time: 0.0615120733185
Google PageSpeed Score 91/100

Both sites hosted on the same server.

An example site (1.5.0), also hosted on the same server, which has few or no attributes
First load Parse Time: Parse Time: 0.212 - Number of Queries: 60 - Query Time: 0.0433254337006
Second Load: Parse Time: Parse Time: 0.188 - Number of Queries: 61 - Query Time: 0.0314254791107
Google PageSpeed Score 88/100


My question is what changes, besides these (http://www.zen-cart.com/content.php?63) which are already done, can be made to improve query times as well as caching? Any suggestions?

Ensure that the sites are hosted on a server which is optimised for Zen Cart queries. There is a list of recommended hosting providers, above.

limelites:

Ensure that the sites are hosted on a server which is optimised for Zen Cart queries. There is a list of recommended hosting providers, above.

Please re-read the entire post..... This is my server and as posted the query times are excellent.

Our servers are built specifically for Zen Cart.

How long are your sites actually taking to load? From what you have posted the time it takes for the server to generate the HTML including all PHP code and SQL Queries is under a second (approximately .5 to .6 of a second). As Google PageSpeed is not an indicator of page speed, if you have not already, I would recommend opening up Firebug (in Firefox or Chrome) to get an idea of how long it takes to load both the html content as well as other components (CSS, Javascript, Images, etc). You can also use tools like Fiddler to emulate other connection speeds (such as DSL or 3G LTE).

Your parse times are a lot higher than your SQL Query times, which could indicate a bottleneck at the CPU / Memory - it is taking far longer to process the PHP than the SQL Queries. You could attempt to do some caching of the page contents, but this is not trivial and has it's own set of issues (and may not give you much of a gain).

I use a large number of attributes on a couple sites and for those sites I currently see page parse times around .4 to .8 of a second on the shared hosting and in my test environment (P4 2.4G x64 running Xen 4.1 [about 6 VMs] - CentOS guest with 512MB ram - running Apache2, suPHP, and MySQL).

I'd be curious to hear what other people see for average page load times on sites utilizing attributes (the site mentioned above uses around 5-10 attributes per product).

1.5.0 Site
Pingdom -- Score 96/100 Load Time 1.66s Size 153.2KB
Webpage -- Test First Load: 3.319s Second Load: 3.227s

1.5.1 Site
Pingdom -- Score 94/100 Load Time 1.90s Size 189.3KB
Webpage -- Test First Load: 2.214s Second Load: 2.211s

We are caching everything, mod_deflate is running. All CSS, images, js etc is also cached. They really should load fast as hell, but the queries for the sites with many attributes just slow them down so much. Which is the nature of the post, does anyone have any tips, tricks or suggestions to help the query speed for cart with many attributes?

~Melanie

mprough:

... the queries for the sites with many attributes just slow them down so much. Which is the nature of the post, does anyone have any tips, tricks or suggestions to help the query speed for cart with many attributes?

Based upon what you posted about .24 seconds was spent running SQL Queries. About .45 seconds is spend processing PHP code. All of the ones you posted indicate your server is spending more time processing PHP code than running SQL Queries. You can probably gain by throwing more processor time to your web server / php processing.

As I mentioned earlier you could also cache the results of the php script (HTML) and serve the cached result directly instead of running the php script every time. This would save both the PHP and SQL time for cached requests. The downside is it may take awhile for changes made to products, categories, or other pages to be seen by website visitors (unless you wrote code to clear out all pages related to the updated item in the admin interface - not trivial).

That said, if you feel the SQL Queries are the bigger problem, here are a few things I did (quite awhile ago) on a couple Zen Cart based sites with a large number of attributes (over 10K rows in the database).

When I was doing SQL tuning, I also installed Query Log to identify any long running SQL Queries (modified to ommit queries returned by the Zen Cart SQL cache or Query Cache). I looked at any long running queries to see what queries or tables in the database could be optimized. I do remember reading a post about changing the table type for whos_online if you still have that enabled (I've disabled it on most of my sites). I've also disabled recording what product and categories are being looked at when the client is using an outside companies Analytic program to track the website (saves writing to the database).

I remember looking through the Query Log to identify where I could save requests using either Query Cache or the Zen Cart SQL cache by changing the SQl Query text to the exact text of a previous SQL query. I did make some changes to the query strings used by Zen Cart and installed modules / plugins. I also created a class specifically for handling common attribute queries (and enabled Zen Cart's SQL cache for these).

A number of the SQL queries perform JOINs or lookups from multiple tables. You can find these in the Zen Cart code and modify those lines to enable using the built in Zen Cart SQL caching. This does provide a small boost for these types of queries (at the cost of them sometimes being stale).

From http://www.zen-cart.com/content.php?63-how-do-i-speed-up-my-site:

4 (b). In your individual attribute assignments, set "Apply Discounts Used by Product Special/Sale: No" (assuming that the options don't have cost associated with them); this will heavily reduce your parse times.

Not touched the 1.5.0 site yet, but substantial gains made here

First load: Parse Time: Parse Time: 0.405 - Number of Queries: 290 - Query Time: 0.0843922608795
Second Load: Parse Time: 0.387 - Number of Queries: 288 - Query Time: 0.0676645597992

It has made a huge difference is load... not so much tool testing as I supplied when asked, but the waiting for the page to finish that people see. Load test tools often and in this case do not know the SQL is still processing =)

Anyhow, now when you click pages the real load is much closer to as fast as it should be ...

I changed a bunch of large tables to Inno

and I changed

attributes_discounted & product_attribute_is_free to 0 for cases when it is not in use.

~Melanie