6

I'm working on a 10000 products Magento store. I scheduled a cronjob to run everyday at 7am to read a CSV file and update each product inventory and set it out of stock if new QTY is less than 1.

Currently, the update process takes around an hour (more/less) to update all 10000 products. My code as follow:

 $csv = new Varien_File_Csv();
 $csvData = $csv->getData('products.csv');

 foreach($csvData as $k => $v){
     // code to store product_id and new_qty into a temporary table 
 }

 $stock_item_model = Mage::getModel('cataloginventory/stock_item');
 $stock_item_collection = $stock_item_model->getCollection();

 foreach($stock_item_collection as $stock_item) {

     // load new_qty from temporary table based on product_id
     $new_qty = Mage::getResourceModel('custom/temp')->getQty($stock_item->getProductId())

     // set new QTY
     $stock_item->setQty($new_qty);

     // check if in stock or out of stock
     if($new_qty < 1) {
         $stock_item->setIsInStock(0);
     } else {
         $stock_item->setIsInStock(1);
     }   
 }

 // save all
 $stock_item_collection->walk('save'); // this takes around an hour to finish.

So, is there any other way to decrease the update time? Do I need to change the way I'm saving stock items?

  • What version of Magento? – B00MER Dec 3 '14 at 11:18
  • 1
    @B00MER Magento EE 1.13.1.0 – Aboodred1 Dec 3 '14 at 16:07
3

try changing indexes to manual, then reindex everything once the import id done (and put the indexes back to auto). if that doesn't help, try working with the database directly - that might depend on your store setup, if you rely on observers when products go out of stock or back in stock.

|improve this answer|||||
  • how can reindexing everything once the import is done reduce update time? – Aboodred1 Dec 1 '14 at 18:14
  • 1
    the default behavior is to reindex all product data, prices, stock data, etc while iterating through walk('save'). that will definitely slow things down. if you change all the indexes to "manual" they won't index all products while iterating. once the script ends, you'll need to reindex everything and switch back to auto. consider the following: save + reindex + save + reindex... vs save + save + ... + reindex_all. For N big enough, index_all runs way faster than N * index – Cristi Dec 1 '14 at 18:59
  • so do I need to remove walk('save') from my code? and replace it with $stock_item->save() within foreach, then run reindex_all? – Aboodred1 Dec 2 '14 at 17:12
  • Yes you're right save + save + save + reindex_all was faster but its still take long time. Thanks though – Aboodred1 Dec 3 '14 at 1:19
2

So, there's

  • magmi
  • danslo/ApiImport
  • use sql against the DB (although this isn't generally recommended) Assuming by product_id you mean the magento entity_id, create your TEMPORARY TABLE in memory, then join it against cataloginventory_stock_item (is_in_stock,qty), cataloginventory_stock_status (stock_status, qty) and cataloginventory_stock_status_idx (stock_status, qty) to update them
|improve this answer|||||
  • If I decided to go with the last option, do I need to reindex after update is complete? – Aboodred1 Dec 1 '14 at 18:40
  • Not on community, _idx is the only index being used. You have trouble with any caches though, or any database structure changes in the future – Richard Dec 1 '14 at 18:41
  • You mean if I used the last option? right – Aboodred1 Dec 1 '14 at 18:49
  • 1
    Yeah, the third table is the index table – Richard Dec 1 '14 at 18:50
2

Definitely use Magmi. It will zip through 10,000 records happily in under 5 minutes. You can also call it on the cli so it can be automated to run every hour if you wish.

I work on a site with 60,000 products, 26 store views and languages. Magmi is the only way we can manage such a big site without forever waiting for updates to complete.

|improve this answer|||||
  • can I create a custom import/update script in Magmi which does not rely on product sku? – Aboodred1 Dec 1 '14 at 19:42
1

* UPDATE *

Here is a another copy-pasta of a more bare bones PHP/SQL approach without Magento's layers of onions getting in the way.

This script will take a CSV use the MySQL's built-in functions to load and parse the CSV into a temporary table, where a single loop is performed to update product EAV tables accordingly.

NOTES:

  • Keep in mind other flags/observers won't be triggered without initializing the whole Magento Stack.
  • is_in_stock isn't considered.
  • Promos around products may need to be applied through indexing.
  • Enterprise EE 1.13+ may index properly with the addition of MySQL Triggers without the need to having to reindex or modify indexer modes to manual.

Also read over a previous post about optimizing your stack to avoid having to do such:

.

<?php

////////////////GLOBAL DEFINITIONS////////////////////
//DATABASE SETTINGS 
$dbConfig = array(
     'host'      => 'localhost',
     'username'  => 'username',
     'password'  => 'password',
     'dbname'    => 'dbname',
     'driver_options'=> array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'),
);

///// Path to Your CSV file
$path = '/path/html/var/import/WebStockImport.csv';

// Path to Mage.php 
require_once 'app/Mage.php';

// Connect to the databases
$db_magento = Zend_Db::factory('Pdo_Mysql', $dbConfig);

updateTempTableFromFile($db_magento, $path);   

$query = $db_magento->select()->from('temp_table');

$contents_array = $db_magento->fetchAll($query);

// Run for each line
foreach ($contents_array as $line) {


    $sku = $line['sku'];
    $qty = $line['qty'];

    $exists = $db_magento->query("SELECT COUNT(sku) cnt FROM catalog_product_entity WHERE sku = '$sku' LIMIT 1");

    $find_product = (($exists->fetchObject()->cnt) > 0) ? true : false;

    if ($find_product == true) {
        $entity_id = getEntityID_bySKU($db_magento, $sku);   
        updateQTY ($db_magento, $entity_id, $qty);
    echo "</tr><td>$sku</td>-<td>$qty</td></tr><br>";   
    }

}

function getEntityID_bySKU($db_magento, $sku) {

    $entity_row = $db_magento->query("SELECT entity_id FROM catalog_product_entity p_e WHERE p_e.sku = '$sku'")->fetchObject();
    $entity_id  = $entity_row->entity_id;
    return $entity_id;
}

function updateQTY($db_magento, $entity_id, $qty) {

     $db_magento->query("UPDATE cataloginventory_stock_item s_i, cataloginventory_stock_status s_s
         SET   s_i.qty = '$qty', s_i.is_in_stock = IF('$qty'>0, 1,0),
               s_s.qty = '$qty', s_s.stock_status = IF('$qty'>0, 1,0)

         WHERE s_i.product_id = '$entity_id' AND s_i.product_id = s_s.product_id ");

}

function updateTempTableFromFile($db_magento, $path){
    $db_magento->query("TRUNCATE TABLE temp_table");
    $db_magento->query("LOAD DATA LOCAL INFILE '$path'

                      INTO TABLE temp_table
                      FIELDS TERMINATED BY ','
                      LINES TERMINATED BY 'n'
                      (`store`,`sku`, `qty`,`is_in_stock`)");

}
?>

Sauce:


* OLD POST *

Give this a try as it will avoid a lot of the overhead of the entire stack, as long as you're just updating stock/inventory this will work much faster.

<?
define('MAGENTO', realpath(dirname(__FILE__)));
require_once MAGENTO . '/app/Mage.php';
umask(0);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
$count = 0;
$file = fopen(MAGENTO . '/var/import/updateStockLevels.csv', 'r');
while (($line = fgetcsv($file)) !== FALSE) 
{
    if ($count == 0) 
    {
        foreach ($line as $key => $value) 
        {
            $cols[$value] = $key;
        }
    }
    $count++;
    if ($count == 1) continue;
    #Convert the lines to cols
    if ($count > 0) 
    {
        foreach ($cols as $col => $value) 
        {
            unset(${$col});
            ${$col} = $line[$value];
        }
    }
    // Check if SKU exists
    $product = Mage::getModel('catalog/product')->loadByAttribute('sku', $sku);
    if ($product) 
    {
        $productId = $product->getId();
        $stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
        $stockItemId = $stockItem->getId();
        $stock = array();
        if (!$stockItemId) 
        {
            $stockItem->setData('product_id', $product->getId());
            $stockItem->setData('stock_id', 1);
        }
        else
        {
            $stock = $stockItem->getData();
        }
        foreach ($cols as $col => $value) 
        {
            $stock[$col] = $line[$value];
        }
        foreach ($stock as $field => $value) 
        {
            $stockItem->setData($field, $value ? $value : 0);
        }
        $stockItem->save();
        unset($stockItem);
        unset($product);
    }
    echo "<br />Stock updated $sku";
}
fclose($file);
?>

note: $file location path for CSV file, and CSV is simply: sku, qty.

"sku","qty"
"prod1","11"

Sauce:

|improve this answer|||||
  • Note, you'll want to add a check for is_in_stock to update products that come in and out of stock. – B00MER Dec 1 '14 at 21:13
  • I'll give it a try, but do I need to reindex product attribute after the update is completed? – Aboodred1 Dec 1 '14 at 21:36
  • 1
    It will by default if the indexing is stock indexer is set to auto: github.com/OpenMage/magento-mirror/blob/magento-1.9/app/code/… note the method: afterCommitCallback below as well. – B00MER Dec 1 '14 at 21:50
  • I implemented your idea, the update process took the same time as my code that I have my question. – Aboodred1 Dec 2 '14 at 21:18
  • 1
    thanks for the update .. the new code you posted is similar to Magmi updateStock function. I already solved this issue with Magmi because its really fast. Thanks for your effort. – Aboodred1 Dec 9 '14 at 17:26
1

I am using Magento 1.9

I have tried with the following code. it's OK But i have another smart code

$stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
$stockItemId = $stockItem->getId();
$stockItem->setData('qty', $stockQty);
$stockItem->setData('manage_stock',1);
$stockItem->save(); 

My code is following:-

Mage::getModel('cataloginventory/stock')->backItemQty($productId,$new_qty); 
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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