I have following code in which I have join product collection with sales order item table. this query takes fraction of a seconds to execute but foreach loop after that takes around half minute while collection has limit to 12 products. :--

$strtime = microtime(true);

$allProductObject = Mage::getModel('catalog/product')->getCollection()

$toDate=date('Y-m-d', strtotime('today'));
$fromDate=date('Y-m-d', strtotime('today -1 days'));

                          array('order_item' => $allProductObject->getTable('sales_flat_order_item')), 
                          "order_item.product_id = e.entity_id AND order_item.created_at BETWEEN '{$fromDate}' AND '{$toDate}'", 
                          array("SUM(order_item.qty_ordered-(order_item.qty_refunded+order_item.qty_canceled)) as sold_qty")
                     $allProductObject->getSelect()->group('e.entity_id')->order("sold_qty desc"); 

echo $finaltime = microtime(true)-$strtime."<br>"; // print 0.0453298091888 sec

 $strtime1 = microtime(true);

 foreach($allProductObject as $product)
       echo $product->getEntityId()."<br>";
       echo $product->getname()."<br>";
       echo $product->getSoldQty()."<br>"."<br>";
  echo $finaltime1 = microtime(true)-$strtime1."<br>"; // print 39.8572499752 sec

Why above foreach loop taking so much time to execute while query fetches only 12 products ? Thanks in Advance.

  • do you need the qty of sold products only along with basic product name ? Aug 16, 2016 at 6:53
  • I want to get best sales product with qty sold with a time period. Aug 16, 2016 at 7:03

1 Answer 1


The problem is in the request. You just created a collection at the moment of the first test. But you didn't load it (send a request to the database). At the beginning of a cycle Magento starts loading the collection (sends a request) automatically and it is probably very heavy. Try to optimize your request because this is the most preferable decision when you have large tables of data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.