Boost slow product list browse performance

[Sjaak Boer]

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

[derek webster]

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

[IntraX]

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.

[plieka]

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) considering the speed the search had.

P
~4000 products
J1.5.21
VM.1.1.4

[IntraX]

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

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

[jaimwa]

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/

[IntraX]

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.

[jaimwa]

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?

[jaimwa]

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?

[jaimwa]

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?

[IntraX]

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.

[jaimwa]

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!

[bunak]

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]

[bunak]

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`";
}

[jaamsadams]

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.