Widget has too many database calls? Need a refactor suggestion

Question

I'm using Magento 1.9.3 and I have a theme that is loading very slow. After a debug I found that on the home page for example I'm loading New Products widget, Featured Poducts Widget, Sale Products Widget and Manufacturer Products Widget.

If I commented these widgets from the CMS Page, the website loaded instantly. With them active, the website has a delay of 5 seconds.

I have the following manufacturers widget:

<?php
$maufacturers = Mage::getModel('manufacturer/manufacturer')->getCollection()->addFieldToFilter('status',Array('eq'=>1))->setPageSize(15)->setCurPage(1); 
?> 
<div id="manufacture-logo" class="manufacture_products">
 <div class="category-title"><h2><span><?php echo $this->getHeading(); ?></span></h2></div>
 <?php $_collectionSize = $maufacturers->count() ?>
 <?php 
 $sliderFor =5;
 $productCount = $_collectionSize; 
 ?> 
 <?php if ($productCount >= $sliderFor): ?>
 <div class="customNavigation">
 <a class="btn prev">&nbsp;</a>
 <a class="btn next">&nbsp;</a>
 </div> 
 <?php endif; ?> 
 <ul class="<?php if ($productCount >= $sliderFor){?>product-carousel<?php }else{?>products-grid<?php }?>" id="<?php if ($productCount >= $sliderFor){?>brand-carousel<?php }else{?>brand-grid<?php }?>"> 
 <?php foreach($maufacturers as $manufacturer): ?> 
 <?php if(Mage::getModel('eav/entity_attribute_source_table')->setAttribute(Mage::getModel('eav/entity_attribute')->load(Mage::getModel('eav/entity_attribute')->getIdByCode('catalog_product', Mage::getStoreConfig('manufacturer/general/attribute_code'))))->getOptionText($manufacturer->getManufacturerName())): ?>
 <li class="<?php if ($productCount >= $sliderFor){?>slider-item<?php }else{?>product-items<?php }?> item">
 <div class="product-block">
 <div class="product-block-inner">
 <a href="<?php echo $manufacturer->getUrl();?>">
 <?php echo $this->getLayout()->createBlock('core/template')->setmanufacturerimage($manufacturer->getFilename())->setListPageFlag(1)->setTemplate('manufacturer/manufacturer_resize.phtml')->toHtml(); ?>
 </a>
 <?php if($this->displayName()):?>
 <div class="manufacturer-name">
 <a href="<?php echo $manufacturer->getUrl(); ?>"> <?php echo Mage::getModel('eav/entity_attribute_source_table')->setAttribute(Mage::getModel('eav/entity_attribute')->load(Mage::getModel('eav/entity_attribute')->getIdByCode('catalog_product', Mage::getStoreConfig('manufacturer/general/attribute_code'))))->getOptionText($manufacturer->getManufacturerName()) ?>
 </a>
 </div>
 <?php endif; ?>
 </div><!-- End div class product-block-->
 </div><!-- End div class product-block-inner -->
 </li>
 <?php endif; ?>
 <?php endforeach; ?>
 </ul>
</div>
 <span class="brand_default_width" style="display: none; visibility: hidden;"></span>

Am I right to say that there are DB calls inside the Foreach? I know this is a bad code.

Below you'll find the code for the Featured Products Widget:

<div class="featured-products">
 <div class="category-title"><h2><?php echo $this->__($this->getBlockLabel()) ?></h2></div>
 <div class="featured-products-inner">
<?php if(!$this->getProductCollection()->count()): ?>
<p class="note-msg"><?php echo $this->__('There are no products matching the selection.') ?></p>
<?php else: ?> 
<?php if (($_productCollection = $this->getProductCollection()) && $_productCollection->getSize()): ?>
 <?php // Grid Mode ?>
 <?php $_collectionSize = $_productCollection->count() ?>
 <?php 
 $sliderFor =4;
 $productCount = $_collectionSize; 
 ?>
 <?php if ($productCount >= $sliderFor): ?>
 <div class="customNavigation">
 <a class="btn prev">prev</a>
 <a class="btn next">next</a>
 </div> 
 <?php endif; ?> 
 <ul class="<?php if ($productCount >= $sliderFor){?>product-carousel<?php }else{?>products-grid<?php }?>" id="<?php if ($productCount >= $sliderFor){?>featured-carousel<?php }else{?>featured-grid<?php }?>">
 <?php $i = 0; $row = 0; foreach ($_productCollection->getItems() as $_product): ?>
 <?php
 $stock_text = $_product->getStockText();
 $faraCos = $_product->getFaraCos();
 ?>
 <li class="item <?php if ($productCount >= $sliderFor){?>slider-item<?php }else{?>product-items<?php }?>"> 
 <div class="product-block">
 <div class="product-block-inner">
 <div class="product-image-block-inner"> 
 <div class="product-image-block"> 
 <?php if($this->helper('framework')->isNewProduct($_product)):?>
 <div class="new-label"><?php echo $this->__('New') ?></div>
 <?php endif; ?>
 <?php if($this->helper('framework')->isSpecialProduct($_product)):?>
 <div class="sale-label"><?php echo $this->__('Sale') ?></div>
 <?php endif; ?>
 <a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->stripTags($this->getImageLabel($_product, 'small_image'), null, true) ?>" class="product-image">
 <?php $productId = $_product->getId(); $_product = Mage::getModel('catalog/product')->load($productId); ?>
 <img class="remover-image" id="product-collection-image-<?php echo $_product->getId(); ?>" src="<?php echo $this->helper('catalog/image')->init($_product, 'image')->resize(178,248); ?>" alt="" />
 <img class="hover-image" src="<?php echo $this->helper('catalog/image')->init($_product, 'image')->resize(178,248); ?>" width="178" height="248" alt="<?php echo $this->stripTags($this->getImageLabel($_product, 'thumbnail'), null, true) ?>" />
 </a>
 </div>
 </div>
 <div class="product_desc">
 <h2 class="product-name" ><a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->escapeHtml($_product->getName()) ?>">
 <?php echo $this->escapeHtml($_product->getName()); ?>
 </a></h2> 
 <?php echo $this->getPriceHtml($_product, true) ?> 
 <?php echo $this->getReviewsSummaryHtml($_product, 'short') ?>
 <?php if ($stock_text==201) { ?> 
 <div class="ribbon-wrapper-green"><div class="ribbon-green">în stoc</div></div>
 <?php } elseif ($stock_text==202) { ?>
 <div class="ribbon-wrapper-red"><div class="ribbon-red">stoc epuizat</div></div>
 <?php } elseif ($stock_text==200) { ?>
 <div class="ribbon-wrapper-limitat"><div class="ribbon-limitat">stoc limitat</div></div>
 <?php } elseif ($stock_text==219) { ?>
 <div class="ribbon-wrapper-comanda"><div class="ribbon-comanda">la comanda</div></div>
 <?php } elseif ($stock_text==226) { ?>
 <div class="ribbon-wrapper-stocfurnizor"><div class="ribbon-stocfurnizor">stoc furnizor</div></div>
 <?php } ?>
 <?php /*?> <div class="description"><?php echo $this->getReviewsSummaryHtml($_product) ?></div><?php */?>
 </div> 
 <div class="product_hover-block">
 <div class="actions">
 <?php if($_product->isSaleable()): ?>
 <?php if (!$faraCos && $stock_text!=202): ?>
 <button type="button" title="<?php echo $this->__('Add to Cart') ?>" class="button btn-cart" onclick="setLocation('<?php echo $this->getAddToCartUrl($_product) ?>')"><span><?php echo $this->__('Add to Cart') ?></span></button>
 <?php else: ?>
 <p class="availability out-of-stock"><span><a href="https://www.distributieutilaje.ro/contact">Contactati-ne</a></span></p>
 <?php endif; ?>
 <?php else: ?>
 <p class="availability out-of-stock"><span><?php echo $this->__('Out of stock') ?></span></p>
 <?php endif; ?> 
 <?php if (!$faraCos && $stock_text!=202): ?>
 <ul class="add-to-links">
 <?php if ($this->helper('wishlist')->isAllow()) : ?>
 <li><a href="<?php echo $this->helper('wishlist')->getAddUrl($_product) ?>" class="link-wishlist" title="<?php echo $this->__('Add to Wishlist') ?>"><?php echo $this->__('Add to Wishlist') ?></a></li>
 <?php endif; ?>
 <?php if($_compareUrl=$this->getAddToCompareUrl($_product)): ?>
 <li><a href="<?php echo $_compareUrl ?>" class="link-compare" title="<?php echo $this->__('Add to Compare') ?>"><?php echo $this->__('Add to Compare') ?></a></li>
 <?php endif; ?>
 </ul> 
 <?php endif; ?>
 </div>
 </div>
 </div><!-- End div class product-block-inner -->
 </div><!-- End div class product-block -->
 </li> 
 <?php endforeach; ?>
 </ul> 
 <span class="featured_default_width" style="display:none; visibility:hidden"></span>
<?php endif; ?>
<?php endif; ?> 
</div> 
</div> 

Am I right to say that this code is bad? That there are database calls inside the foreach? What would you refactor in case of the Featured Produts widget? Thanks.

Answer 1

To load home page quickly and you want to display more than 2 widget then you should use ajax call for each and every widget for home page. For example: once the home page loaded, you should fire ajax call and render response html for particular block/widget. Please check below URL to get more idea.

https://stackoverflow.com/questions/3876725/magento-product-listing-using-ajax

Answer 2

This would require a deeper code trace (and probably some adjustments in block classes), but a quick tip could be...

Try to avoid loading the full product object in a product list, as it happens in the second piece of code you have posted... that would free memory & make things faster

If you loose some valuable content after doing that then you'd find which attributes are needed in your templates & add them to product list, through backoffice Attributes Management, using used_in_product_listing attribute