Too much queries on category page

[zerxx]

Hi!
I'm using Joomla 2.5.9 and VM 2.0.20b.
I have a category with about 300 products with customfields (20-30 customfields) used to store product parameters.

This category opens really slow. It takes about 10 seconds before (with 50 products per page) it starts to load in the browser.
VM makes about 1100-1500 queries to the database.

That's huge. Is there a way to speed it up and reduce the number of queries?

[Peter Pillen]

I don't think the customfields will be the problem, unless you load all of them in category view. If so, you will need to re-program your queries so that there are less queries done.

I had the same problem, but I jut reduced the number of products in category view from 36 to 21 which lowered the number of queries quite a lot. It loaded pages much faster.

Also try reviewing your enabled modules in your template. I disabled a few joomla modules in vituemart view and only showed them in the frontpage or other pages to which the modules are relevant (such as latest news). That reduces your queries also.

[zerxx]

1. I've noticed that accordion menu module made about 300 queries, so i changed with the virtuemart default one, it reduced the number of queries by ~200
But still virtuemart category module makes one query per category (no the module itself, but class in /models/category.php

Code Select Expand

SELECT `virtuemart_media_id`
  FROM `ssdbt_virtuemart_category_medias`
These queries are useless on category page, because category images are not shown there

2. VM makes 4-5 queries per product getting customfileds even if i'm not using any filter module

3. I've commented out some queries
In \administrator\components\com_virtuemart\models\product.php

Code Select Expand

$q = 'SELECT `virtuemart_shoppergroup_id` FROM `#__virtuemart_product_shoppergroups` WHERE `virtuemart_product_id` = "' . (int)$virtuemart_product_id . '"';
$this->_db->setQuery ($q);
$shoppergroups = $this->_db->loadResultArray ();


Code Select Expand

$q = 'SELECT `ordering`,`id` FROM `#__virtuemart_product_categories`
WHERE `virtuemart_product_id` = "' . $this->_id . '" and `virtuemart_category_id`= "' . $product->virtuemart_category_id . '" ';
$this->_db->setQuery ($q); 
// change for faster ordering
$ordering = $this->_db->loadObject ();


In \administrator\components\com_virtuemart\helpers\calculationh.php

Code Select Expand

$this->_db->setQuery('SELECT `vendor_currency` FROM #__virtuemart_vendors  WHERE `virtuemart_vendor_id`="' . $this->productVendorId . '" ');
$single = $this->_db->loadResult();
$this->vendorCurrency = $single;

I'm not using shoppergroups and different currencies, so i don't need these queries.
It reduced the number of queries by about 150

But Still i'm getting 600-800 queries on category page, still too much

[jjk]

This might be of interest to you:
http://forum.virtuemart.net/index.php?topic=109146.msg370327#msg370327
Have you been using the firefox browser plugin 'Firebug' (it's Net tab) for locating eventual other time wasting elements?

[zerxx]

well, waiting for vm 2.1..

yes, i've been using Firebug for testing.
On daytime (when many people online) it takes more than 10 seconds for the server to respond, images and other elements are loading quiet fast

But the server response time is huge...

I'm starting to get very dissapointed, and think to move to another script..

[jjk]

But the server response time is huge...

This comment won't solve your problem, but when I read your last post, the first thing I thought was ...maybe he is one of those VM users who have chosen one of these popular mass hosters who place thousands of domains on a single server. Last week I looked at a VM shop which had 7500 neighbours on the server. Good hosters usually limit that to something like 50 or maybe up to 100 if they operate very fast multi-CPU servers.

[zerxx]

...maybe he is one of those VM users who have chosen one of these popular mass hosters who place thousands of domains on a single server.

I've moved the site to VPS. It's a bit faster, but not much than virtual hosting