Boost slow product list browse performance

[cworthing]

Yes, yes I did.  And this DOES speed it up a TON!  The full images and thumb images I had were already on a hosted site(from our corporation) and they were loading rediculously slow, I just have to actually load the images to my server and do it that way. Bummer, no easy way! Thanks djlongy and the above posters!

[shawnsi]

Thank Udo and djlongy for sharing the query improvements! I've been trying to improve the query for quite some time now, but never improved so significantly until made the changes that you've suggested. Thank you so much!

Hope queries will be improved in VM version 1.5.

[scept1c]

the speed has risen significantly, thanks! I just wonder why the default settings are so awfully bad in comparison, is it going to be fixed?

[powerPT]

Your are my god! Amazing hack! I hope that VM team consider some tweak for next version...

[beachy]

Thanks for posting this solution. When I changed it on my site it caused all of the thumbnails to disappear.

Does anyone have any idea why that would be?

[haword]

I also thought of this before recently, but made changes a little differently, you can download and check here http://files.mail.ru/UZEUQ6

i change also $count

[Hermes]

Thank You very much for this modification. Incredible & Amazing & working perfect in my installation too!!

[Deacil]

I am not sure what I am doing wrong.  I have searched for the query Udo posted and I cannot find it.  I tried the 2 shop_browse_queries.pnp files posted on this site and neither one have any impact on the speed of pulling up a category.  It takes the same amount of time with and without the new file.

Udo, can you please explain how you made your changes.  I am sure all of the newbies (myself) would greatly appreciate info on where to make the code change.

[franzpeter]

Dear Udo and djlongy,

thank you very much for that modification. It does boost the vm shop performance from slow to ultrafast.

[callumgilhooly]

Brilliant. I'm building a site with around 30,000 products and kept getting time outs and errors on searches. Was tearing my hair out about how to fix this.. Downloaded and used the file above and it's made a great difference. No more errors and results returned quickly.

Many Thanks

Callum

[derek webster]

This should be part of the core - the speed increase was insane - we've got 60,000
with 900+ in some categories. and the page times were 30sec and with this mod the page time wen down to about .5 sec.

Unbelievable
Thanks for this

[shinta]

@Udo, thank you!  Your hack sped up my page loads from about 1 minute to 0.3 to 0.4 seconds!  I timed the before and after page loads multiple times because I couldn't believe it!  I have about 15,000 products, with 100-200 products in most categories (largest is about 800).  I was so sad when I thought I'd have to give up VM because of the crippling performance, but now I'm extremely happy I can continue using it!

This thread should be stickied imo.
Thankfully, this thread was the first google result for "virtuemart slow for large number of products"

[bdarlin]

I have tried this on my site (~5500 products) but it hasn't appreciably sped up anything.  I've discovered that the culprit is the vmSEF plugin.  I also tried the router.php (manual) plugin.  Both slow the site down to a crawl.  Disabling them brings the types of results others have seen.

Does anybody know a good SEF plugin that doesn't slow the site down so much?

[Lylene]

WHERE (`web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id` OR `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_parent_id`)

If the statement is replaced with:

WHERE `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id`

I've looked at a shop_browse_queries from vm1.1.3 and it was already as suggested ... does it mean this bad query was an update given with vm1.1.4 ?

[kunalvm]

i am using vm1.1.4..my site used to become very slow after user login..but after making the changes you suggested..its much faster!
thanx