VirtueMart Forum

VirtueMart Dev/Coding Central: VM1 (old version) => Development Projects, Modifications, Hacks & Tweaks. VM1.1 => Topic started by: Udo on January 04, 2010, 14:44:21 pm

Title: Boost slow product list browse performance
Post by: Udo on January 04, 2010, 14:44:21 pm
I have set up a relatively small shop with 20 categories and about 4000 products using Joomla 1.5.15 and VirtueMart 1.1.4. I have added some products to multiple (parent) categories. The problem is that browsing the larger categories is very slow. Fetching a page with only 20 (of 900) products took more then 15 seconds, which seems way too long for such a small database.

I analyzed the $count and $list queries created by shop_browse_queries.php, and found the $count query runs 3 seconds, and the $list query runs 9 seconds. In $list, the query uses 9s to copy to tmp table. This is caused by the fact that no indexes are used when joining web_vm_product_category_xref, even though the indexes are available (product_category_xref_category_id,idx_product_category_xref_product_id)

I remember not using indexes is often caused by using (OR) in the where statement, as is the case in the $count & $list queries:

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`

After that the indexes are used normally, and the query only needs 0.09 seconds !!!! to run in stead of 10s (more then 100x performance boost). As I am currently not using parent products, it's no problem to remove the second part.

I also noticed the query contains a useless where clause, and either the first or the second should be removed:


AND        `product_parent_id`=0
AND        (
          (`product_parent_id`='0')
        OR    (`product_parent_id`='')
        )


I'm not sure if this is a common issue or that I am the only one experiencing this and somehow my database is not performing as it should (if so I'd be interested in suggestions).

Title: Re: Boost slow product list browse performance
Post by: szbstvn on January 04, 2010, 16:49:22 pm
Hi!
I have a same problem, but this idea is nice for me.
Title: Re: Boost slow product list browse performance
Post by: szbstvn on January 04, 2010, 16:50:36 pm
Working! Great!

Tank You

Title: Re: Boost slow product list browse performance
Post by: yenommer on January 25, 2010, 16:11:01 pm
You are my hero!  ;D I have been fighting with this same problem for sooo long. Thanks again man it really works great!
Title: Re: Boost slow product list browse performance
Post by: Wallboy on February 11, 2010, 03:23:48 am
I have the same problem with the slow browse pages even when theres no products. With DEBUG enabled in VM, a 20 product browse page loads in roughly 11 seconds. If I take that browse page down in half, then it takes 5 seconds. I tried to modify the lines you mentioned, but am unsure which lines these are on. I found 2 lines that were similar.
Title: Re: Boost slow product list browse performance
Post by: enoon on February 12, 2010, 14:30:18 pm
Thanks it's a good speed gain. I also had a similar solution, but did now know where to search for the code to modify.
There is an alternative solution for the people who use product parents add:

   $where_clause[] = "`#__{vm}_product`.`product_id`=`#__{vm}_product_category_xref`.`product_id`";

before (line 310 in shop_browse_queries):   
   $where_clause[] = "((`#__{vm}_product`.`product_id`=`#__{vm}_product_price`.`product_id` AND `#__{vm}_shopper_group`.`shopper_group_id`=`#__{vm}_product_price`.`shopper_group_id`) OR `#__{vm}_product_price`.`product_id` IS NULL) ";

I must mentions that the speed gain is the same for both solutions. The issue was that the product was not linked to product_category_xref directly.
Title: Re: Boost slow product list browse performance
Post by: cworthing on February 12, 2010, 21:35:13 pm
I added the above code before the code you had said enoon. No difference.  Am I suppose to comment that longer piece of code out?

And my Line 310 is not that piece of code.
This is what my 310 is.

310.   // when someone is searching, we also show child products (product_parent_id != 0), but that's not the case here
   $where_clause[] = "`product_parent_id`=0 ";
}

The lines you refer to is line 306(I've never changed this file).

Is there a step im missing?  My site categories are the problem, clicking them frm the main page the pages come up sooo slowwwww. Been trying to fix this for a couple weeks now.
Title: Re: Boost slow product list browse performance
Post by: djlongy on February 12, 2010, 22:36:58 pm
I have set up a relatively small shop with 20 categories and about 4000 products using Joomla 1.5.15 and VirtueMart 1.1.4. I have added some products to multiple (parent) categories. The problem is that browsing the larger categories is very slow. Fetching a page with only 20 (of 900) products took more then 15 seconds, which seems way too long for such a small database.

I analyzed the $count and $list queries created by shop_browse_queries.php, and found the $count query runs 3 seconds, and the $list query runs 9 seconds. In $list, the query uses 9s to copy to tmp table. This is caused by the fact that no indexes are used when joining web_vm_product_category_xref, even though the indexes are available (product_category_xref_category_id,idx_product_category_xref_product_id)

I remember not using indexes is often caused by using (OR) in the where statement, as is the case in the $count & $list queries:

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`

After that the indexes are used normally, and the query only needs 0.09 seconds !!!! to run in stead of 10s (more then 100x performance boost). As I am currently not using parent products, it's no problem to remove the second part.

I also noticed the query contains a useless where clause, and either the first or the second should be removed:


AND        `product_parent_id`=0
AND        (
          (`product_parent_id`='0')
        OR    (`product_parent_id`='')
        )


I'm not sure if this is a common issue or that I am the only one experiencing this and somehow my database is not performing as it should (if so I'd be interested in suggestions).



This is A LIFE SAVER. I have around 50000 products. I wanted them in the same group because there was no need for browsing, just searching products using SKU. But when user accidently selected that group it killed the site. So I've split this category to 40 subcategories to speed it up but it still takes ages to open one of these subcats.

Your solution changed the site to superfast!

Unfortunately VM does NOT handle large (well, if 50000 recs is large today) databases well. :((
Title: Re: Boost slow product list browse performance
Post by: MikeUK on February 12, 2010, 23:00:05 pm

Unfortunately VM does NOT handle large (well, if 50000 recs is large today) databases well. :((

I disagree, and don't think this is necessarily a fair comment. This is not just about VM. More important is the system admin and the server, as well as the setup / development of the site (especially what modules are used, modifications, etc).

A smooth system with this many products, with any e-commerce system, is down to a good / experienced system admin who knows what to do / what not to do, and can make the required modifications to cater for such a large number of products (which is the great thing about open-source software.
Title: Re: Boost slow product list browse performance
Post by: djlongy on February 13, 2010, 10:48:13 am

Unfortunately VM does NOT handle large (well, if 50000 recs is large today) databases well. :((

I disagree, and don't think this is necessarily a fair comment. This is not just about VM. More important is the system admin and the server, as well as the setup / development of the site (especially what modules are used, modifications, etc).

A smooth system with this many products, with any e-commerce system, is down to a good / experienced system admin who knows what to do / what not to do, and can make the required modifications to cater for such a large number of products (which is the great thing about open-source software.

I disagree on that :)

We run a dedicated linux (slackware) server on core2duo proc with 4gb ram etc. There are 5 virtual hosts (httpd), only one has this "big" database of products. After implementing the above tweak the site became superfast. Before that queries hogged the 100% cpu for minutes, not seconds.
All other vhosts run just perfect.

I agree there's always some room for additional optimizing but when mysql query runs as described, I cannot say other but that VM is not optimized for 50000+ products.

Title: Re: Boost slow product list browse performance
Post by: MikeUK on February 13, 2010, 11:41:28 am
Fair enough, and good to see you have some decent equipment, but my point was that VM is open source, designed to be adjusted, and 50000+ products is not normal. So it is the responsibility of the website admin / developer, not VM.

I'm not saying VM cannot be improved, I'm saying it is not the fault of VM.
Title: Re: Boost slow product list browse performance
Post by: cworthing on February 13, 2010, 14:53:22 pm
Where is the place I would apply this change?

Quote
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`
Title: Re: Boost slow product list browse performance
Post by: djlongy on February 13, 2010, 16:16:13 pm
Hi, find the modified file attached...

@Mike:

I agree that since VM is an open source there are some issues and that they can be solved by skilled people. However, I don't see why 50000+ product should not be normal? We have this many SPARE PARTS in the catalogue so being in the motorcycle business this is rather small amount of products in fact...

As for who's "fault" it is for such poor performace...programmer's of course. I'm not trying to be offensive, I just want to say that regarding my problem so far I got only these (some contradictory) statements:
- 50000 is too much, reduce number of products (I can't, remember?)
- 50000 is no problem for VM, it runs very smoothly, YOU have a problem (with server, setup etc)
- not logical why you have 50000 in the same group (it is logical when people use only search by product code to add to cart, they are not intended to browse...)
-etc.
So I still claim that VM as it is, no tweaks, is not doing well with 50000 products (or 25000 for that mater).
Which, since it's open source, is not something catastrophic but it's nice that it's admitted and some good coders are able to fix it instead we all go finding problems on servers etc.

Therefore this current tweak is a blessing in my eyes...but VM is still an awesome product, for me the only choice really.



[attachment cleanup by admin]
Title: Re: Boost slow product list browse performance
Post by: cworthing on February 13, 2010, 17:04:54 pm
djlongy, is that file intended for me? If it was, I replaced that file with mine(backed mine up) but it did no speed-ups or slowdowns for the category browsing or search function.
Title: Re: Boost slow product list browse performance
Post by: djlongy on February 14, 2010, 12:33:09 pm
djlongy, is that file intended for me? If it was, I replaced that file with mine(backed mine up) but it did no speed-ups or slowdowns for the category browsing or search function.

Yeah, this was for you. In my case it caused an overall speed improvement it's hard to imagine. Any category opens in a second, especially the 1000+ product categories that took more than a minute to open now show the product list in two seconds.

If it doesn't speed up your site then perhaps you have a problem elsewhere :?
Title: Re: Boost slow product list browse performance
Post by: cworthing on February 14, 2010, 16:22:24 pm
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!
Title: Re: Boost slow product list browse performance
Post by: shawnsi on February 19, 2010, 01:23:50 am
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. :)
Title: Re: Boost slow product list browse performance
Post by: scept1c on February 19, 2010, 09:52:24 am
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? :(
Title: Re: Boost slow product list browse performance
Post by: powerPT on February 20, 2010, 20:47:58 pm
Your are my god! Amazing hack! I hope that VM team consider some tweak for next version...
Title: Re: Boost slow product list browse performance
Post by: beachy on February 25, 2010, 16:29:14 pm
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?
Title: Re: Boost slow product list browse performance
Post by: haword on February 25, 2010, 20:38:40 pm
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
Title: Re: Boost slow product list browse performance
Post by: Hermes on April 27, 2010, 11:40:10 am
Thank You very much for this modification. Incredible & Amazing & working perfect in my installation too!!
Title: Re: Boost slow product list browse performance
Post by: Deacil on April 28, 2010, 19:15:27 pm
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.
Title: Re: Boost slow product list browse performance
Post by: franzpeter on May 05, 2010, 16:31:42 pm
Dear Udo and djlongy,

thank you very much for that modification. It does boost the vm shop performance from slow to ultrafast.
Title: Re: Boost slow product list browse performance
Post by: callumgilhooly on May 13, 2010, 19:19:55 pm
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
Title: Re: Boost slow product list browse performance
Post by: derek webster on May 25, 2010, 17:42:59 pm
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
Title: Re: Boost slow product list browse performance
Post by: shinta on June 17, 2010, 18:36:22 pm
@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" ;)
Title: Re: Boost slow product list browse performance
Post by: bdarlin on July 10, 2010, 20:30:27 pm
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?
Title: Re: Boost slow product list browse performance
Post by: Lylene on July 12, 2010, 22:18:11 pm
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 ?
Title: Re: Boost slow product list browse performance
Post by: kunalvm on July 15, 2010, 08:25:04 am
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 :)
Title: Re: Boost slow product list browse performance
Post by: Sjaak Boer on August 05, 2010, 08:12:51 am
Although others are enthousiastic about this solution. It doesn't work for me. I can't get it to work.

Would it be possible to publish a tutorial. Could be I am doing it totally wrong.

Thanks.

Sjaak
Title: Re: Boost slow product list browse performance
Post by: derek webster on August 29, 2010, 21:31:58 pm
I too encountered many slow down problems,

I changed to a dedicated server - Didnt help

I've got about 120,000 products.

I eventualy tracked down a big contributor to the slow down to be the categories module - it was performing many queries every time the page loaded as it was onevery page of the site. SO I turned on caching within the Joomla config and everything sped up, Turnin 30second page loads down to 2sec

I realise caching isnt preferable as I previously thought it caused random errors within Joomla bit It actually works really well.

In a nut shell  - you need to try to cut down the amount of queries loading every time the page refreshes... I even took the category module out of the Checkout pages completely.

If you want to post a link - I'd be happy to have a wee look to see if there were any suggestions I could make.

Good Luck
Derek
Title: Re: Boost slow product list browse performance
Post by: IntraX on September 03, 2010, 13:55:22 pm
Fair enough, and good to see you have some decent equipment, but my point was that VM is open source, designed to be adjusted, and 50000+ products is not normal. So it is the responsibility of the website admin / developer, not VM.

I'm not saying VM cannot be improved, I'm saying it is not the fault of VM.

I think that it is, VM is like a "car frame with basic exterior and interior" and it is up to the user to make it look, work, speed, act, and behave as wished. Sure, the point that VM contains a lot of unneeded code is maybe true, but same applies to Magento and even Joomla. But the point here is that also Joomla is based on the philosophy of "one system for all", so it works for all and those who need only one thing out of Joomla, e.g. a store with VM can add and remove certain things that first make it more handy, second faster BUT third also less versatile. The most simple example is that no one who does not use Community Builder should install it on a Joomla or a more complex example would be to rewrite code of VirtueMart to decrease the requests to the server or whatever point in the system that is not needed for the specific function it will have. When you use VM only as catalog and this will not change, you can also remove anything from the code that is needed for shopping actions and surely it will be faster in the frontend and still work when you code it correctly.

But actually it is not really the right choice to run VM with a lot of products on a Virtual Server, a dedicated server should be it, and today it should be a QuadCore Opteron with SSD or even better be on cloud hosting.

Like mentioned several times, this hack will not speed up a slow server or slow Joomla with lots of addons but when you have a fast server and modules that are not suckers for request it will work. Problem is that the default mod_productcategories is slowing any site down, when you use the mod_virtuemart as category module things look different again. So only modules really needed should be active or installed or when you can tweak modules those should be tweaked too to avoid slow downs. I am not a technician or coder myself, but to know how it works is really the basic stuff to work with software like VM.
Title: Re: Boost slow product list browse performance
Post by: plieka on October 14, 2010, 14:33:32 pm
Thanks a million,

you just short-cut my search for a solution with a few hours.

I used the second option that was proposed and it worked right of the bat.
Went from 20 seconds to 3 seconds which is a welcome improvement to say the leasts.

I almost considerd moving to Magento (NOT) ;D considering the speed the search had.

P
~4000 products
J1.5.21
VM.1.1.4
Title: Re: Boost slow product list browse performance
Post by: IntraX on October 14, 2010, 14:38:19 pm
Well in case people still use 1.1.4 due to hacks and so on. In 1.1.5 this part was now changed in shop_browse.queries:

1.1.4

Quote
$where_clause[] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
$where_clause[] = "`#__{vm}_product_category_xref`.`category_id`=`#__{vm}_category`.`category_id`";
// Filter Products by Category

1.1.5

Code: [Select]
// The "OR" in the where clause slows down the whole query. It is only needed when there are parent-products
$tmpdb = new ps_DB();
$tmpdb->query( "SELECT COUNT(*) AS parentcnt FROM #__{vm}_product WHERE product_parent_id>0");
$tmpdb->next_record();
if($tmpdb->f('parentcnt')>0) {
$where_clause[] = "(`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id` OR `#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id`)";
} else {
$where_clause[] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
}
$where_clause[] = "`#__{vm}_product_category_xref`.`category_id`=`#__{vm}_category`.`category_id`";
// Filter Products by Category

With more then 40k products the solution in 1.1.4 caused the site to load endlessly, with the new improvement in 1.1.5 it loads browse pages in maybe 1 to 3 seconds, always related to the amount of products in the particular category and also the product page performance increased substantially.
Title: Re: Boost slow product list browse performance
Post by: jaimwa on October 30, 2010, 11:21:45 am
I'm using Joomla 1.5.21 and VM 1.1.5 I have about 2500 products but 19 user groups and am running Shopper Group Extension.
The site is hosted on a shared server but this has never been a problem for me in the past with VM sites

The site is running so slowly. Is this fix appropriate for VM 1.1.5 or has it already been fixed in this verion?

Any other tips would be greatly appreciated.

The site is http://67.19.63.20/~solvar/
Title: Re: Boost slow product list browse performance
Post by: IntraX on October 30, 2010, 11:41:27 am
No, this can't be the reason for this slow poor performance on your site as this code is from VM 1.1.5 already. But maybe the file did not overwrite in case you made an update from 1.1.4! But you should simply check if this code is present in your shop, it is in the browse queries. (Edited some stuff from some other topic that crisscrossed)

I have browsed your site and this one is generally very slow, I checked the product page and the inquiry page and both take very long to open, all the content besides the products already need a lot time to load, so this must be related to the server indeed. Normally people would say you should use compression for css and js files and minimize the image load and so on, but as the only site on a good fast server all this is not making a big difference often, especially when the site is just under development, it should load very fast with very low traffic.

And from how the page loads, images slowly come up and so on, this is not looking like a VM fault.

Generally people should realize that they should not host more then 1 shop on 1 server, but lots of people fill the server with several sites and wonder why the performance is weak:)

So first you should check if this code is present or not, then you can take a look at other things that may cause this.
Title: Re: Boost slow product list browse performance
Post by: jaimwa on October 30, 2010, 12:07:25 pm
Thanks so much for looking. I'm going to rehost and try modgzip and some sql caching.

How can I tell if there are too many queries happening?
Title: Re: Boost slow product list browse performance
Post by: jaimwa on October 30, 2010, 13:38:43 pm
Maybe it's because we have 19 shopper groups and are running Shopper Group Extension?

Is there an easy way of disabling these to test?
Title: Re: Boost slow product list browse performance
Post by: jaimwa on October 30, 2010, 17:44:47 pm
I have also noticed that i have legacy queries as well. That is probably part of the issue. Legacy is turned off and still getting them.

Any ideas?
Title: Re: Boost slow product list browse performance
Post by: IntraX on October 30, 2010, 18:13:42 pm
Thanks so much for looking. I'm going to rehost and try modgzip and some sql caching.

How can I tell if there are too many queries happening?

First still check if the code above is really in your shop_browse.queries.php file as sometimes it can happen that you still have the old file in your system.

You should also generally not have any module, plugin or component enabled and even installed in your Joomla system that is not really needed. Also, when you do not use the default Joomla and VM modules, you should uninstall those.

Next would always be, the VM product categories module is very load intense and can quickly slow down a site when it is turned on, I would rather use the mod_virtuemart for categories then the categories moduel directly (at least this was stil an issue in 1.1.3 and I do not know if they ever re-coded the default categories module).

Maybe it's because we have 19 shopper groups and are running Shopper Group Extension?

Is there an easy way of disabling these to test?

I don't know, do you really have to use 19 shopper groups? Is this an external shopper group extension? In this case you must be able to turn it of somehow.

It could be caused by this but normally I would say that this may only cause problems when several different types of shopper in different groups are simultaniously browsing the site and not when only 1 or 2 frontend group visitors are looking at the shop, but you never know.

I have also noticed that i have legacy queries as well. That is probably part of the issue. Legacy is turned off and still getting them.

Any ideas?

What type of Legacy queries do you refer to? Joomla or VirtueMart? VirtueMart still contains lines of code from old versions (valid mos) so maybe you refer to this?

----> You can always make one check. You turn off all modules and plugins and see if the site loads faster, when it does, you now check through all those extensions with turning one on after the other and always check the frontend if the site is slowing down from one, this way you may be able to point out which module may cause the slowdown and replace it or recode it or may be able to figure out which module may have a conflict with another module, plugin or component as this is also sometimes the reason for a site having poor performance.

----> It will never hurt to fine-tune your server and mysql database too, as most servers run on a basic configuration that does not even reflect their own hardware like RAM size andd so and can easiely achieve more then their configuration is limiting them to. This can also make a big difference.
Title: Re: Boost slow product list browse performance
Post by: jaimwa on October 30, 2010, 20:36:27 pm
Thanks again for the help.

This is really odd but when I turn legacy on in Joomla I get 4 legacy queries. When it's turned off I get about 450 legacy queries.

I have installed both speed boosters from Codingmall, turned on legacy, turned on gzip and caching and now the site is running much much faster.

Really odd about the legacy mode!
Title: Re: Boost slow product list browse performance
Post by: bunak on May 26, 2011, 22:24:06 pm
The problem is that the operations "or" indexes are used badly.
Code: [Select]
$where_clause[] =
"(`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id` OR
`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id`)";

Therefore, this condition should be split into two with the query on the omnibus UNION.

So be careful and make copies. This huck for VirtueMart 1.1.8 stable

1. For operations UNION, add a field `product_price` that will work For sorting
change
Code: [Select]
// These are the names of all fields we fetch data from
$fieldnames = "`#__{vm}_product`.`product_id`, `product_name`, `product_packaging`,  `products_per_row`, `category_browsepage`, `category_flypage`, `#__{vm}_category`.`category_id`, `product_full_image`, `product_thumb_image`, `product_s_desc`, `product_parent_id`, `product_publish`, `product_in_stock`, `product_sku`, `product_url`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_available_date`, `product_availability`, `#__{vm}_product`.`mdate`, `#__{vm}_product`.`cdate`";
BY
Code: [Select]
// These are the names of all fields we fetch data from
$fieldnames = "`#__{vm}_product`.`product_id`, `product_name`, `product_packaging`,  `products_per_row`, `category_browsepage`, `category_flypage`, `#__{vm}_category`.`category_id`, `product_full_image`, `product_thumb_image`, `product_s_desc`, `product_parent_id`, `product_publish`, `product_in_stock`, `product_sku`, `product_url`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_available_date`, `product_availability`, `#__{vm}_product`.`mdate`, `#__{vm}_product`.`cdate`, `#__{vm}_product_price`.`product_price`";

2. Change order by for UNOIN operation
Change
Code: [Select]
switch( $orderby ) {
case 'product_list':
$orderbyField = '`#__{vm}_product_category_xref`.`product_list`'; break;
case 'product_name':
$orderbyField = '`#__{vm}_product`.`product_name`'; break;
case 'product_price':
$orderbyField = '`#__{vm}_product_price`.`product_price`'; break;
case 'product_sku':
$orderbyField = '`#__{vm}_product`.`product_sku`'; break;
case 'product_cdate':
$orderbyField = '`#__{vm}_product`.`cdate`'; break;
default:
$orderbyField = '`#__{vm}_product`.`product_name`'; break;
}
BY
Code: [Select]
// bunak OPTIMIZE --------------------->>>>>
switch( $orderby ) {
case 'product_list':
//$orderbyField = '`#__{vm}_product_category_xref`.`product_list`'; break;
$orderbyField = '`product_list`'; break;
case 'product_name':
//$orderbyField = '`#__{vm}_product`.`product_name`'; break;
$orderbyField = '`product_name`'; break;
case 'product_price':
//$orderbyField = '`#__{vm}_product_price`.`product_price`'; break;
$orderbyField = '`product_price`'; break;
case 'product_sku':
//$orderbyField = '`#__{vm}_product`.`product_sku`'; break;
$orderbyField = '`product_sku`'; break;
case 'product_cdate':
//$orderbyField = '`#__{vm}_product`.`cdate`'; break;
$orderbyField = '`cdate`'; break;
default:
//$orderbyField = '`#__{vm}_product`.`product_name`'; break;
$orderbyField = '`product_name`'; break;
}
// bunak OPTIMIZE <<<<<---------------------

3. Change WHERE clause for UNION. From OR to separated sub-queries
Change
Code: [Select]
if($tmpdb->f('parentcnt')>0) {
$where_clause[] = "(`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id` OR `#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id`)";
} else {
$where_clause[] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
}
By
Code: [Select]
// bunak - optimize ---------------->>>
$where_clause_union = array();
if($tmpdb->f('parentcnt')>0) {
//$where_clause[] = "(`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id` OR `#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id`)";
$where_clause_union[1] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
$where_clause_union[2] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id`";
} else {
$where_clause[] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
}
// bunak - optimize <<<----------------

4. In END prepare QUERY for UNOIN
Replace
Code: [Select]
$q = implode("\n", $join_array ).' WHERE '. implode("\n AND ", $where_clause );
$count .= $q;

$q .= "\n GROUP BY `#__{vm}_product`.`product_sku` ";
$q .= "\n ORDER BY $orderbyField $DescOrderBy";

// Joomla! 1.5 supports listing "All" items, which means $limit == 0
if( vmIsJoomla(1.5) && $limit == 0 ) {
$list .= $q;
} else {
$list .= $q . " LIMIT $limitstart, " . $limit;
}
By
Code: [Select]
// bunak OPTIMIZE --------------------->>>>>
$q1 = implode("\n", $join_array ). ' WHERE ' . ((count($where_clause_union) > 0) ? " \n " . $where_clause_union[1] . " AND " : "") . implode("\n AND ", $where_clause );

if (count($where_clause_union) == 0){
$count .= $q1;
$list .= $q1;
} else {
$q2 = implode("\n", $join_array ). ' WHERE ' . $where_clause_union[2] . " AND " . implode("\n AND ", $where_clause );

$count = $count . $q1 . " \n UNION \n " . $count . $q2;
$list = $list . $q1 . " \n UNION \n " . $list . $q2;
}

$list .= "\n ORDER BY $orderbyField $DescOrderBy";

// Joomla! 1.5 supports listing "All" items, which means $limit == 0
if( vmIsJoomla(1.5) && $limit == 0 ) {
//
} else {
$list .= " LIMIT $limitstart, " . $limit;
}
// bunak OPTIMIZE <<<<<---------------------


Its my solution work in here http://fiatonline.com.ua/ [700 000 parents products]
Title: Re: Boost slow product list browse performance
Post by: bunak on June 11, 2011, 07:53:44 am
Ho-ho-ho
I find easiest way, for solver "Boost slow product list browse performance"
I look the shop_browse_queries.php carefully, and saw
if WM don't search products by keyword
then the `product_parent_id` set to 0 in 315-317 rows

Code: [Select]
if( empty( $keyword ) ) {
// when someone is searching, we also show child products (product_parent_id != 0), but that's not the case here
$where_clause[] = "`product_parent_id`=0 ";
}

So we don't need in hard compare for MySQL server in WHERE clause

Code: [Select]
// The "OR" in the where clause slows down the whole query. It is only needed when there are parent-products
$tmpdb = new ps_DB();
$tmpdb->query( "SELECT COUNT(*) AS parentcnt FROM #__{vm}_product WHERE product_parent_id>0");
$tmpdb->next_record();
if($tmpdb->f('parentcnt')>0) {
$where_clause[] = "(`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id` OR `#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id`)";
} else {
$where_clause[] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
}

REPLACE it by
Code: [Select]
// The "OR" in the where clause slows down the whole query. It is only needed when there are parent-products
$tmpdb = new ps_DB();
$tmpdb->query( "SELECT COUNT(*) AS parentcnt FROM #__{vm}_product WHERE product_parent_id>0");
$tmpdb->next_record();
if($tmpdb->f('parentcnt')>0) {
// bunak - OPTIMIZE ---------------->>>
$where_str = "(`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id` ";
if( !empty( $keyword ) ) {
// when someone is searching, we also show child products (product_parent_id != 0), IT is this case
$where_str .= " OR `#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_parent_id` ";
}
$where_str .= ")";
$where_clause[] = $where_str;
// bunak - OPTIMIZE <<<----------------
} else {
$where_clause[] = "`#__{vm}_product_category_xref`.`product_id`=`#__{vm}_product`.`product_id`";
}
Title: Re: Boost slow product list browse performance
Post by: jaamsadams on July 19, 2011, 20:52:05 pm
Ho-ho-ho
I find easiest way, for solver "Boost slow product list browse performance"
....

[Using VM 1.1.9]

Can u please specify which one of above should be used or both above by bunak should be used togather
I am wondering which way would be more optimum

one side question relating to the same code file: I am pulling full images for items directly from the distributor web site. By implemnting solution above by bunak. I observed that it's pulling those full images upon showing category page, without any such need at that level. That's causing a time lag in my scenario. Can someone guide me how do can I remove code causing full size image pulling in shop_browse_queries.php page.
Title: Re: Boost slow product list browse performance
Post by: sirius on August 01, 2011, 16:06:13 pm
Hi,

only the last one from bunak is needed.

actually installed for us on a VM 1.1.9
Title: Re: Boost slow product list browse performance
Post by: aforantman on September 05, 2011, 06:34:00 am
Hi All,

I have tried all of the above and nothing has worked.
My production address for the shop is:

http://111.223.236.34/~trebor/index.php?option=com_virtuemart&Itemid=18

I am running this site on a virtual dedicated server
I have version 1.1.9

If the suggestion involves changing code could you please provide a file name, line number, the current code, and the code that will be replacing that.

Thanks in advance for your help.

Cheers,

Anthony.
Title: Re: Boost slow product list browse performance
Post by: giziro on September 29, 2011, 21:54:46 pm
Hi everyone,

I am posting this because I had the same problem with a VirtueMart Catalog slow down and this topic and the solution code inside helped me with optimization, you can check it out:

http://forum.virtuemart.net/index.php?topic=53806.0

Cheers!
Title: Re: Boost slow product list browse performance
Post by: mbit on November 23, 2011, 20:47:45 pm
 i got about ~240 queries in flypage and 150 in homepage. The waiting time of the html is 1-2seconds! while all the rest images files etc are not more than 1.5sec!  I have about ~700 products and this delay has been a pain :/ .  I am usins 1.1.9 and i I tried all of these scripts but didnt see big changes in the delay of html file. any ideas? a
Title: Re: Boost slow product list browse performance
Post by: PRO on November 23, 2011, 21:12:44 pm
You can force cache of images, css, and js files in htaccess

<FilesMatch "\.(ico|pdf|flv|jpg|jpeg|png|gif|js|css|swf)$">
Header set Expires "Thu, 15 Apr 2012 20:00:00 GMT"
</FilesMatch>


ALSO; most of the time its the template.

Whats your google page speed score?
http://code.google.com/speed/page-speed/download.html


Title: Re: Boost slow product list browse performance
Post by: mbit on November 23, 2011, 21:56:36 pm
You can force cache of images, css, and js files in htaccess

<FilesMatch "\.(ico|pdf|flv|jpg|jpeg|png|gif|js|css|swf)$">
Header set Expires "Thu, 15 Apr 2012 20:00:00 GMT"
</FilesMatch>


ALSO; most of the time its the template.

Whats your google page speed score?
http://code.google.com/speed/page-speed/download.html


instead of that i have
Quote
# disable the server signature
ServerSignature Off

#RewriteBase
#####################################################
# CONFIGURE media cachingFileETag None
FileETag None
<IfModule mod_expires.c>
ExpiresActive On
ExpiresDefault A600
ExpiresByType image/x-icon A2592000
ExpiresByType application/x-javascript A604800
ExpiresByType text/css A604800
ExpiresByType image/gif A2592000
ExpiresByType image/png A2592000
ExpiresByType image/jpeg A2592000
ExpiresByType text/plain A86400
ExpiresByType application/x-shockwave-flash A2592000
ExpiresByType video/x-flv A2592000
ExpiresByType application/pdf A2592000
ExpiresByType text/html A600
</IfModule>

<ifModule mod_headers.c>
  <filesMatch "\\.(ico|pdf|flv|jpg|jpeg|png|gif|swf)$">
    Header set Cache-Control "max-age=2592000, public"
  </filesMatch>
  <filesMatch "\\.(css)$">
    Header set Cache-Control "max-age=604800, public"
  </filesMatch>
  <filesMatch "\\.(js)$">
    Header set Cache-Control "max-age=216000, private"
  </filesMatch>
  <filesMatch "\\.(xml|txt)$">
    Header set Cache-Control "max-age=216000, public, must-revalidate"
  </filesMatch>
  <filesMatch "\\.(html|htm|php)$">
    Header set Cache-Control "max-age=1, private, must-revalidate"
  </filesMatch>
  <FilesMatch "\.(js|css|xml|gz)$">
    Header append Vary Accept-Encoding
  </FilesMatch>
</ifModule>
RewriteRule .* - [E=HTTP_IF_MODIFIED_SINCE:%{HTTP:If-Modified-Since}]
RewriteRule .* - [E=HTTP_IF_NONE_MATCH:%{HTTP:If-None-Match}]
#####################################################
pagespeed gives me 88
+ my server has Connection close not Enabled Keep-Alive.
My issue is with the html file that does sooo long in order to load and i dont know why :/
Title: Re: Boost slow product list browse performance
Post by: ir_valentin on January 09, 2012, 20:44:58 pm
Hi, find the modified file attached...

Thank you for the file, it's much faster now.I have over 3000 products and before had to wait 10 seconds or more.