Manufactuer queries too many

[niosme]

Very bad logic on manufacturer from module call or on show all manufacturer products from the product or everywhere a link with a manufacturer exists. In eshop with 200+ manufacturers we have duplicated calls on mysql and on same fields causes unexpected high load time to the server.
They have to be 1 call and not 247 to each. Not calling and executing a query inside for loops but get first all items and do it in one query call after for loops.

Just speaking friendly and i would apriciate if you want me to help you with this.

You can see the queries if you enable joomla debug and the times for each query takes to load.

247 calls × SELECT `bwzjs_virtuemart_medias`.* 
  FROM `bwzjs_virtuemart_medias`

247 × SELECT l.slug 
  FROM `bwzjs_virtuemart_manufacturers_en_gb` as l

50 × SELECT `bwzjs_virtuemart_products`.* ,`bwzjs_virtuemart_products_en_gb`.* 
  FROM `bwzjs_virtuemart_products`
  INNER JOIN `bwzjs_virtuemart_products_en_gb`
  ON `bwzjs_virtuemart_products`.`virtuemart_product_id` = `bwzjs_virtuemart_products_en_gb`.`virtuemart_product_id`

42 × SELECT *
  FROM `bwzjs_virtuemart_product_prices`

42 × SELECT `virtuemart_manufacturer_id`
  FROM `bwzjs_virtuemart_product_manufacturers`

42 × SELECT *
  FROM `bwzjs_virtuemart_product_categories`

42 × SELECT `virtuemart_media_id`
  FROM `bwzjs_virtuemart_product_medias`

36 × SELECT c.`virtuemart_custom_id`, c.`custom_parent_id`, c.`virtuemart_vendor_id`, c.`custom_jplugin_id`, c.`custom_element`, c.`admin_only`, c.`custom_title`, c.`show_title` , c.`custom_tip`,   c.`custom_value`, c.`custom_desc`, c.`field_type`, c.`is_list`, c.`is_hidden`, c.`is_cart_attribute`, c.`is_input`, c.`searchable`, c.`layout_pos`, c.`custom_params`, c.`shared`, c.`published`, c.`ordering`, c.`virtuemart_shoppergroup_id`, field.`virtuemart_customfield_id`, field.`virtuemart_product_id`, field.`customfield_value`, field.`customfield_price`,   field.`customfield_params`, field.`published` as fpublished, field.`override`, field.`disabler`, field.`noninheritable`, field.`ordering`,   field.`product_sku`, field.`product_gtin`, field.`product_mpn`   
  FROM `bwzjs_virtuemart_customs` AS c
  LEFT JOIN `bwzjs_virtuemart_product_customfields` AS field
  ON c.`virtuemart_custom_id` = field.`virtuemart_custom_id`

28 × SELECT `bwzjs_virtuemart_categories`.* ,`bwzjs_virtuemart_categories_en_gb`.* 
  FROM `bwzjs_virtuemart_categories`
  INNER JOIN `bwzjs_virtuemart_categories_en_gb`
  ON `bwzjs_virtuemart_categories`.`virtuemart_category_id` = `bwzjs_virtuemart_categories_en_gb`.`virtuemart_category_id`

20 × SELECT `virtuemart_media_id`
  FROM `bwzjs_virtuemart_category_medias`

17 × SELECT `virtuemart_product_id`
  FROM `bwzjs_virtuemart_products`

13 × SELECT *
  FROM `bwzjs_virtuemart_categories_en_gb`AS l
  INNER JOIN `bwzjs_virtuemart_categories`AS c
  ON c.`virtuemart_category_id` = l.`virtuemart_category_id`

13 × SELECT `bwzjs_virtuemart_manufacturers`.* ,`bwzjs_virtuemart_manufacturers_en_gb`.* 
  FROM `bwzjs_virtuemart_manufacturers`
  INNER JOIN `bwzjs_virtuemart_manufacturers_en_gb`
  ON `bwzjs_virtuemart_manufacturers`.`virtuemart_manufacturer_id` = `bwzjs_virtuemart_manufacturers_en_gb`.`virtuemart_manufacturer_id`

13 × SELECT l.virtuemart_category_id, l.category_name, published
  FROM `bwzjs_virtuemart_categories` as c 
  LEFT JOIN `bwzjs_virtuemart_categories_en_gb` as l
  ON l.`virtuemart_category_id` = c.`virtuemart_category_id`

7 × SELECT `virtuemart_category_id` AS `child`, `category_parent_id` AS `parent`     
  FROM  bwzjs_virtuemart_categories

3 × SELECT `extension_id` as `id`, `folder` as `type`, `element` as `name`, `params`
  FROM bwzjs_extensions

3 × SELECT *
  FROM bwzjs_virtuemart_calcs 
  LEFT JOIN bwzjs_virtuemart_calc_shoppergroups using(virtuemart_calc_id)

2 × SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle, m.params
  FROM bwzjs_modules AS m

2 × SELECT `extension_id` AS `id`,`element` AS `option`,`params`,`enabled`
  FROM `bwzjs_extensions`

2 × SELECT *
  FROM `bwzjs_virtuemart_userfields`

2 × SELECT `product_stockhandle`
  FROM `bwzjs_virtuemart_products`

1 × SELECT CONCAT(`vendor_accepted_currencies`, ",",`vendor_currency`) AS all_currencies, `vendor_currency`
  FROM `bwzjs_virtuemart_vendors`

[niosme]

i figured out, that the manufacturer module does this job so wrong. Those 247 calls and 247 duplicated are from the virtuemart manufacturer module.
It does 247 to get medias and 247 to get the names of manufacturers.

So i have to rewrite to load the queries better or in just one query instead of so many.

[niosme]

I fixed it if you want to make just one query instead of 247 + 247 queries for 247 manufacturers which makes site load fast as hell when a lot of manufacturers exists.

this changes i made in mod_virtuemart_manufacturer

Code Select Expand


<?php
defined('_JEXEC') or  die( 'Direct Access to '.basename(__FILE__).' is not allowed.' );
/*
* manufacturer Module
*
* @package VirtueMart
* @subpackage modules
*
* @copyright (C) 2012-2014 The VirtueMart Team
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL
* VirtueMart is Free Software.
* VirtueMart comes with absolute no warranty.
*
* @link https://virtuemart.net
*/

if (!class_exists( 'VmConfig' )) require(JPATH_ROOT .'/administrator/components/com_virtuemart/helpers/config.php');
// VmConfig::loadConfig();
// vmLanguage::loadModJLang('mod_virtuemart_manufacturer');

$display_style =  $params->get( 'display_style', "div" ); // Display Style
$manufacturers_per_row = $params->get( 'manufacturers_per_row', 1 ); // Display X manufacturers per Row
$headerText =  $params->get( 'headerText', '' ); // Display a Header Text
$footerText =  $params->get( 'footerText', ''); // Display a footerText
$show =  $params->get( 'show', 'all'); // Display a footerText

// $model = VmModel::getModel('Manufacturer');
// $manufacturers = $model->getManufacturers(true, true,true);
// $model->addImages($manufacturers);

if(empty($width = VmConfig::get('img_width', 0))){$width=50;}
if(empty($height = VmConfig::get('img_height', 0))){$height=50;}

$lang = JFactory::getLanguage();
$languageTag = $lang->getTag();//en-GB
$languageTag = str_replace("-","_",strtolower($languageTag));//en_gb

$loadImages = ($show == 'image' or $show == 'all')?1:0;
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('manufacturer.virtuemart_manufacturer_id, lang_manufacturer.mf_name as mf_name
'.($loadImages?', mmassocs.virtuemart_media_id, medias.file_url, medias.file_meta':''))
    ->from($db->quoteName('#__virtuemart_manufacturers', 'manufacturer'))
    ->join('INNER', $db->quoteName('#__virtuemart_manufacturers_'.$languageTag , 'lang_manufacturer') . ' ON ' . $db->quoteName('manufacturer.virtuemart_manufacturer_id') . ' = ' . $db->quoteName('lang_manufacturer.virtuemart_manufacturer_id'));
if($loadImages){
    $query->join('INNER', $db->quoteName('#__virtuemart_manufacturer_medias', 'mmassocs') . ' ON ' . $db->quoteName('mmassocs.virtuemart_manufacturer_id') . ' = ' . $db->quoteName('manufacturer.virtuemart_manufacturer_id'))
    ->join('INNER', $db->quoteName('#__virtuemart_medias', 'medias') . ' ON ' . $db->quoteName('medias.virtuemart_media_id') . ' = ' . $db->quoteName('mmassocs.virtuemart_media_id'));
}
$query->where('manufacturer.published=1')->order('mf_name ASC');

$db->setQuery($query);
$manufacturers = $db->loadObjectList();

if(empty($manufacturers)) return false;
$totalManus =  count( $manufacturers);

// load the template
require JModuleHelper::getLayoutPath('mod_virtuemart_manufacturer', $params->get('layout', 'default'));
?>


also commented all of your helper file
and in default layout the only change i made is ( commented it out cause i have to print the img directly cause your function couldnt able to work

Code Select Expand


<!-- < ?php -->
<!-- if ($manufacturer->images && ($show == 'image' or $show == 'all' )) { ?> -->
<!-- < ?php echo $manufacturer->images[0]->displayMediaThumb('',false);?> -->
<!-- < ?php -->
<!-- } -->
<?php if(!empty($manufacturer->file_url) && ($show == 'image' or $show == 'all' )):
 echo '<img width="'.$width.'" height="'.$height.'" src="'.$manufacturer->file_url.'" alt="'.(empty($manufacturer->file_meta)?$manufacturer->mf_name:$manufacturer->file_meta).'"></img>';
endif;


With all this we are making a single query instead of manufacturers*2 queries the module current does.
Attached the module if anyone wants it or if you want to keep this logic with some changes you have in your mind.

With love.

[Milbo]

Great work, thank you. Looks interesting. But I am sure I cannot take it directly, as you mentioned with the medias. But you wrote a interesting query. I am sure you noticed the code wasnt updated for years.

You maybe noticed, that the categories are a lot faster on vm4.0.8? I did a similar thing.

[niosme]

Glad you liked that.

I am using this module cause my sites load slow but I would like to see the implementation in a future version.

Have a nice day.

[Milbo]

What I do not understand here, the query in the original code is just ONE for all manufacturers. Just the medias are loaded one by one (which can be enhanced, right). Maybe you had an old module?

[niosme]

Yes you're right for the query. I saw it in the administrator model and its one querie.
So its not the module that have to do with this so it not an outdated module. It calls the main virtuemart model manufacturer.

With the third true variable you get the medias if the user want or dont want to get the images. So this have to be true or false
$loadImages = ($show == 'image' or $show == 'all')?true:false;
$manufacturers = $model->getManufacturers(true, true, $loadImages );

Also as you load the images why we add them again with this $model->addImages($manufacturers) ?
At manufacturer we need only one image there is no true reason to get all the images list but just the first media.

By the way if i open the console of joomla to count the queries it shows up all those queries but your queries are as you said. One shot for manufacturers and then for the images seperate for every manufacturer.
Dont know how i achieved to get so many queries but the half queries also are a lot.

[Milbo]

The first loads just the media ids, the second the media itself.

Yes we need a general function there, which works for products, cats and medias, how many medias should be really loaded, it is not just one and all. For products we have also an hidden config for 2, for overlay effects! We may can also enhance the queries loading the media, to one query. So that we have two queries for manufacturers.

and your query does not consider the langfallbacks which are absolutly mandatory for multilang store.