Queries

[mprough]

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?

[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.

[mprough]

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.

[lhungil]

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).

[mprough]

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

[mprough]

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