Foreach loop taking long time in magento product collection with limit

Question

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()
                   ->addAttributeToSelect('*')->addAttributeToFilter('status',1);



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

$allProductObject->getSelect()->joinLeft(
                          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"); 
                     $allProductObject->getSelect()->limit(12);



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.

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.