0

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;

}
3
  • Could you run this from the command line and echo some debug? my suspicion is that something is wrong with your data source. Using the Magento models would sanitize the data before the query – Haim Oct 23 '18 at 17:56
  • Just ran from command line and no errors. I parse the csv using $data = array_map('str_getcsv', file(CSV_FILE)); and if I iterate through the complete file with no calls to the database the output is fine. – hejhog Oct 23 '18 at 18:46
  • I suspect that when you are querying the database you're passing as ean a value that's tripping it – Haim Oct 23 '18 at 22:27
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;

}

?>

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.