Magento stock sync using a lot of memory- what options do i have to reduce memory usage?

Question

I have a file of around 100,000 sku's that need to have their quantity updated in Magento.

For speed reasons, I am doing a few things: 1. I am generating an array of SKU, Product ID and SKU so that I don't have to do a call for each product to find the product ID from the SKU code, or to get the current quantity. 2. The current quantity from above is then compared to the quantity in the file. If it is the same, then no update occurs. 3. We are using a multi warehouse plugin and so I also keep a list of the cataloginventory_stock_item item_id so that I can just call the model and update it.

My issue is, is that the process is using almost 2GB of memory. I can't quite see why. The file that I am reading line by line is only 5MB. I run the following functions and put the results into an array which must be where the memory is getting used.

protected function _getStockItemsAsArray() {
    $stockCollection = Mage::getModel("cataloginventory/stock_item")->getCollection();

    $out = [];
    foreach ($stockCollection as $stock) {
      $product_id = $stock->getData("product_id");
      $warehouse_id = $stock->getData("stock_id");
      $stock_item_id = $stock->getData("item_id");
      $qty = $stock->getData("qty");
      $is_in_stock = $stock->getData("is_in_stock");

    $out[$product_id][$warehouse_id]["stock_item_id"] = $stock_item_id;
    $out[$product_id][$warehouse_id]["qty"] = $qty;
    $out[$product_id][$warehouse_id]["is_in_stock"] = $is_in_stock;
  }

  return $out;
}

protected function _getCurrentStockAsArray() {
  $resource = Mage::getSingleton("core/resource");
  $readConnection = $resource->getConnection("core_read");

  $query = "
    SELECT
      p.sku,
      p.type_id,
      w.stock_id,
      SUM(IFNULL(pe.qty,0)) pending_qty,
      SUM(s.qty) on_hand_qty
    FROM
      " . $resource->getTableName("catalog_product_entity") . " p
    CROSS JOIN
      " . $resource->getTableName("cataloginventory_stock") . " w
    INNER JOIN
      " . $resource->getTableName("cataloginventory_stock_item") . " s on
      s.product_id = p.entity_id
    LEFT OUTER JOIN
    (
      SELECT
        oi.product_id,
        oi.stock_id,
        IFNULL(SUM(qty_ordered),0) qty
      FROM
        " . $resource->getTableName("sales_flat_order_item") . " oi
      INNER JOIN
        " . $resource->getTableName("sales_flat_order") . " o ON
        oi.order_id = o.entity_id
      WHERE
        o.tigers_export_status IN('Incomplete', 'Failed', 'Pending', 'Exporting')
        AND o.status NOT IN('canceled', 'closed')
      GROUP BY
        oi.product_id,
        oi.stock_id
    ) pe ON
      pe.product_id = p.entity_id
      and pe.stock_id = w.stock_id
    WHERE
      p.sku IS NOT NULL
      OR p.sku != ''
    GROUP BY
      p.sku,
      p.type_id,
      w.stock_id
  ";
  $data = $readConnection->fetchAll($query);

  $out = [];
  foreach( $data as $key => $value ) {
    $out[$value["sku"]][$value["stock_id"]]["on_hand_qty"] = (int)$value["on_hand_qty"];
    $out[$value["sku"]][$value["stock_id"]]["pending_qty"] = (int)$value["pending_qty"];
    $out[$value["sku"]][$value["stock_id"]]["type_id"] = (int)$value["type_id"];
  }
  return $out;
}



protected function _getSkuToProductIDAsArray() {
  $resource = Mage::getSingleton("core/resource");
  $readConnection = $resource->getConnection("core_read");

  $query = "
    SELECT
      sku,
      entity_id
    FROM
      " . $resource->getTableName("catalog_product_entity") . "
    WHERE
      sku IS NOT NULL
    ";
  $data = $readConnection->fetchAll($query);

  $out = [];
  foreach( $data as $key => $value ) {
    $out[$value["sku"]] = (int)$value["entity_id"];
  }
  return $out;
}

What other options are available to use less memory but still have similar performance?

Answer 1

Using the database adapter method fetchAll() to fetch large result sets will definitely cause a heavy demand on system and resources.

Your code may execute for a very long time and PHP will probably run out of memory if you're fetching a large amount of data.

You can fix this inefficient memory utilization by replacing:

$data = $readConnection->fetchAll($query);
$out = [];
foreach( $data as $key => $value ) {

By:

$data = $readConnection->query($query);
$out = [];
while ($value = $data->fetch()) {

This way, each database row is fetched separately using the fetch() method to reduce resource consumption.

The database server will execute only one query and the database buffer will be used for retrieving records one by one.