Need tester for performance tweaks

Ozfarmer wrote:Is there any way to stop the error occurring?

well the error tells you search not found. So you would have to edit the vqmod and figure out why. My guess is that since this is an old thread it was made for 1.4.x, and the queries have changed considerably for 1.5.x

Tried this on OC v.1.5.5.1 and used the vQmod Log_Analyze_DB_Queries for testing.

I do use the vQmod already made to prevent the total product count error.

I have performance issues propably related to catalog/model/catalog/product.php getProducts($data = array())

This SQL is runned a lot of times on frontpage load. (Propably for almost all categories) fetching products.

[/media/exthdd/www/domain/vqmod/vqcache/vq2-catalog_model_catalog_product.php]
Query Time: 0.0112 :: SELECT p.product_id, (SELECT AVG(rating) AS total FROM rp_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM rp_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '8' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM rp_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '8' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special FROM rp_product_to_category p2c LEFT JOIN rp_product p ON (p2c.product_id = p.product_id) LEFT JOIN rp_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN rp_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '24' GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC
Aggregate: 1.3967

Just wondering, why should all products load here at all?

I tried the performance tweak first i did it manually, and saw no gain.
Then I tried this threads vQmod, uncommented the last part as suggested, no errors but then again no gain.

I have like 1300 products and 60 categories, so that might be why I can't see any big difference.

But I have load of these SQL requests, each of them taking load time. I will try to uncomment the line just to make sure, my research is correct.

I have a front pagespeed load at about 2.4s and would like to reduce it with like a 1 sec to 1.2s or sth.

I have done all known server patches, header, caching, using Google pagespeed module for Apache, so there is not much to gain here. Measuring the site with chromes developher tools, shows that it is the OC pageload that is taking time and MySQL process is going sky high. Theres is no big pictures, most js script now comes directly from google.

I have several other shops running in parallel on the same server and a test shop, based on the data and they all perform much better.

EDIT: Uncommenting the SQL gave way better performance, like 1,6s, but also removed my categori panel

I think the SQL needs to be revised for better performance

now im running modified opencart 1.5.3.1 with 160k products, 3M categories :-)
homepage in 50 - 100 ms :-)

that function getProducts is pretty lame, because it only calls the function getProduct => 1 product = 1 query
the next stop is menu - each branch needs its own query

Qphoria wrote:Ok I've made this into a vQmod script.

..any update for new releases? E.g. 1.5.6.4? Still experiencing performance issues with 10K products and 2.5K categories.
I'd like to check things with these changes.

Thanks,

M

My two cents... new version for OC 1.5.6.4, included category. I took advantage of it.

Regards,

M

dumb question, but what is it really supposed to do ? With or without the VqMod, I see similar results, when testing category- or product- page speed, at least with this script. But I also use other Stuff, such as Cache-Mod's, as well.

OpenShop.li - Page Load Time 1.2092 Seconds - OpenCart.li
http://www.openshop.li/cart/index.php?r ... &limit=600

Thanks for infos
Ernie

BTW: When loading category Pages, it presents this as well, if the VqMod is enabled: Just as info, it may be a Mod-specific detail, but I have no time at present to really check on details.

your question is not dumb. Actually this script can give benefits or not. It mainly depends of your store set up, how many products and categories you have, etc.
Personally I found it useful with my set up with about 4k categories and 15k products.
M