Datafeedr Support Forums

Datafeedr Support Forums (/index.php)
-   Questions (/forumdisplay.php?f=67)
-   -   Speed of Custom Views (/showthread.php?t=7436)

dhilditch August 3rd, 2012 08:48 AM

Speed of Custom Views
 
Hi Eric/Stefan,

I have been working recently on optimising the speed of our site and have done well so far - the shop is loading much faster now that I've eliminated a whole bunch of modules that Apache was loading by default. This reduces the memory footprint per worker thread and means the server doesn't start thrashing through virtual memory.

Then I got to work on our wordpress category pages - these were taking upwards of a MINUTE to load and I found it was because under the hood, the wordpress template was loading all of the [DFR:AddProducts?c=....] custom views on each page. I've modified those pages to not load the content and those pages all load sub-second now.

But still, on our site we are publishing 'buyer guides', 'expert advice' and 'a-list reviews'. On each of these posts we often place multiple custom views. I am finding that each custom view is taking 3 seconds to load. This seems incredibly excessive to me - I should be able to get it down to less than 100ms since it's only loading 5 products.

Here are some examples:

This post has two custom views on it:

http://www.fitness-saver.com/2012/05...cise-bicycles/

This post has four custom views on it:

http://www.fitness-saver.com/2012/05...tness-benches/

To confirm it is the DFRs (custom views) that are causing the speed problem, here is a post with no DFRs on it:

http://www.fitness-saver.com/2012/07...y-a-treadmill/

Can you help? We use a categoryid on every DFR so there should be a database optimisation possible but as you stated before, you won't support us if we modify stuff under the hood :(

dhilditch August 3rd, 2012 08:55 AM

Also - I understand W3 Total Cache and WP Super Cache but I want to get the underlying performance fixed first before I stick a caching layer on top of it.

Thanks.

Eric August 3rd, 2012 04:31 PM

Hi

You are still not caching your store. You should turn on caching here:

The Factory > Your Store > Store menu > Advanced Store Settings > Allow view caching

That should help considerably.

Eric

dhilditch August 5th, 2012 09:28 AM

Hi - thanks Eric. I will definitely add caching shortly, but I'd really like to speed up the underlying query if possible, probably using an index. I'm used to using SQL Server where it's easy to monitor queries running against the DB. I've had a look at the source code to try and figure out the query but it's tricky.

I'm figuring that an index against the categoryid would really help since ALL of our custom views on this site include a categoryid.

Thanks for your help - I hadn't been aware of this view caching thing from inside Datafeedr and had been thinking only of using WP Super Cache.

The reason I'm not happy with just caching is because I want the site to run blindingly fast for GoogleBot which is regularly going to be indexing pages which are not currently cached so Google considers our site blindingly fast (sub half-second) and gives us the related boost in SEO as a result.

dhilditch August 5th, 2012 09:34 AM

Sorry - I just realised I hadn't asked a question in that last post.

Could you tell me how the query looks for the custom views? Then I could try optimising them against our dev DB and post the solutions I find back here to see if you approve?

Eric August 5th, 2012 11:02 AM

Hi

The queries are all dynamically produced so you might need to install some sort of query monitor to capture all of the queries that run upon a page loading. Maybe something like this: http://wordpress.org/extend/plugins/debug-queries/

But once you turn on caching, those queries only get run 1 time, significantly reducing any MySQL load.

Eric

dhilditch August 6th, 2012 04:56 PM

Hi Eric,

Thanks again for your help. I installed that fantastic plugin you provided, although unfortunately for some reason it doesn't divulge the SQL of the [DFR:custom views] :(

Anyway, I took a guess at what the problem was, looked at the existing indexes and have discovered the solution - I think you'll like it:

alter table wp_dfr_shop_p2c add index(cat_id, id);

As it stood, the existing index goes in the other direction - id, cat_id - meaning the entire p2c table has to be scanned when running a p2c.cat_id = xxx type query, which as it turns out is all of our custom views AND many of the queries on our shop (where the queries DO show up thanks to that plugin).

Our shop is now MUCH MUCH faster. Maybe you'd like to test it on your own installations and add this index to the next release? I am seeing pages now respond in sub-second speed where previously some were taking upwards of 20 seconds!

I'm now going to stick the cache on top too but am very happy that Google will now find us to be fast.


All times are GMT -5. The time now is 01:11 PM.

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.