Magento stock update via Direct SQL

Question

I have to update 50000 stock levels from a csv file. I am running 2 SQL queries. One to obtain the productID from an EAN barcode and the other to update the stock. When iterating through a small batch of 1000 this completes in approx 1 second. When trying to run the full update (which I thought would run for approx 50 seconds) it runs for minutes until the server times out.

Is there any way I can optimise the sql or code?

The following 2 functions perform the update.

function _getIdFromEAN($ean){
    global $conn;
    global $db;

    //Get product id from EAN
        $sql            = "SELECT entity_id FROM ".$db['pref']."catalog_product_entity_varchar WHERE attribute_id = 147 AND value = ". $ean;

        if ($result = $conn->query($sql)):

        if ($result->num_rows):
            $row = $result->fetch_assoc();
            return $row['entity_id'];
        endif;
    endif;

    return false;

}

function _updateStocks($data){

    global $conn;
    global $db;

        $ean                    = $data[0];
    $newStockQty        = $data[1] <= MIN_STOCK_LEVEL ? 0 : $data[1];
    $isInStock          = $newStockQty > 0 ? 1 : 0;

        $productId              = _getIdFromEAN($ean);

    if ($productId):    

        $sql                    = "UPDATE ".$db['pref']."cataloginventory_stock_item csi,
                                           ".$db['pref']."cataloginventory_stock_status css 
                    SET csi.is_in_stock = ".$isInStock.", csi.qty = ".$newStockQty." 
                    WHERE csi.product_id = ".$productId ." AND csi.product_id = css.product_id";                   


            if ($result = $conn->query($sql)):
            return true;
        endif;

    endif;

    return false;

}

Answer 1

The problem was reading the database on every iteration in function _getIdFromEAN($ean) created a massive overhead. I resolved this by requesting all the data in one query and then assigning this to an array. I then iterated through the array to update the stock. All 57000 records processed in approx. 60 seconds. PHP below (not perfect) hope it helps someone.

<?php

$time_start = microtime(true);

global $conn;
global $db;


define("MIN_STOCK_LEVEL", 5);
define("CSV_FILE","stock_levels.csv");

$count      = 0;
$errorUpdating  = 0;
$notExists  = 0;


$xml = simplexml_load_file('../app/etc/local.xml', NULL, LIBXML_NOCDATA);
if(is_object($xml)):

    createDatabaseConnection($xml);//Create Connection

    $data = array_map('str_getcsv', file(CSV_FILE));//Data to csv

    array_shift($data); //remove headings

    $eanKeys = _getEANs();

    foreach ($data as $_data):

        $ean = $_data[0];
        $qty = $_data[1];

        if (array_key_exists($ean,$eanKeys)):
            $productId = $eanKeys[$ean];

            if (_updateStocks($productId,$qty)):
                $count++;
            else:
                $errorUpdating ++;
            endif;
        else:
            $notExists ++;
        endif;

    endforeach;

endif;

echo '<p>Total execution time in seconds: ' . (microtime(true) - $time_start).'</p>';
echo '<p>'.$count.' - products processed</p>';
echo '<p>'.$errorUpdating.' - Error Updating</p>';
echo '<p>'.$notExists.' - products do not exist</p>';


/***************** UTILITY FUNCTIONS ********************/


function createDatabaseConnection($xml) {
    global $conn;
    global $db;

    $db['host'] = $xml->global->resources->default_setup->connection->host;
    $db['name'] = $xml->global->resources->default_setup->connection->dbname;
    $db['user'] = $xml->global->resources->default_setup->connection->username;
    $db['pass'] = $xml->global->resources->default_setup->connection->password;
    $db['pref'] = $xml->global->resources->db->table_prefix;

    $conn = new mysqli($db['host'], $db['user'], $db['pass'], $db['name']);

    // check connection
    if ($conn->connect_error):
        trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
    endif;
}




function _getEANs(){
    global $conn;
    global $db;

    //Get product id from EAN - attribute_id value obtained from Magento Admin
        $sql            = "SELECT value as ean,entity_id  FROM ".$db['pref']."catalog_product_entity_varchar WHERE attribute_id = 147";

        if ($result = $conn->query($sql)):

        if ($result->num_rows):

            while ($row = $result->fetch_assoc()):
                $data[$row['ean']]= $row['entity_id']; // Set EAN as key                
            endwhile;
        endif;
    endif;

    return $data;

}

function _updateStocks($productId,$qty){

    global $conn;
    global $db;

    $newStockQty        = $qty <= MIN_STOCK_LEVEL ? 0 : $qty;
    $isInStock          = $newStockQty > 0 ? 1 : 0;


    $sql                    = "UPDATE ".$db['pref']."cataloginventory_stock_item csi,
                                       ".$db['pref']."cataloginventory_stock_status css 
                SET csi.is_in_stock = ".$isInStock.", csi.qty = ".$newStockQty." 
                WHERE csi.product_id = ".$productId ." AND csi.product_id = css.product_id";                   


        if ($result = $conn->query($sql)):
        return true;
    endif;


    return false;

}

?>