The problem
Once you have thousands of products in your WooCommerce store, you usually need an automated mechanism to periodically update the stock levels and prices.
The usual way to achieve this is via a CSV file (perhaps exported from a spreadsheet 
) which contains the changes.
A typical example (assuming unique SKUs in your shop) might be:
stockupdates.csv
sku,regular_price,costprice,stock,backorders 001R00610,479.55,429.55,0,no 003N01018,12.25,9.25,1,notify
Note that in this example, costprice is a custom field, not a standard WooCommerce field or attribute.
Unfortunately most of the data import routines (there are numerous plugins available, as well as WooCommerce’s own native Product Import routine) are too slow to be run on a daily basis just to update price and stock of a large number of products.
Solutions
Here we are assuming that you have command line access to your server. If you don’t, these methods will not help you.
One faster way is to create SQL update statements from stockupdates.csv to directly modify the relevant MySQL tables, for example:
UPDATE wp_postmeta a JOIN wp_postmeta b ON a.post_id = b.post_id AND b.meta_key = '_sku' AND b.meta_value LIKE '001R00610' SET a.meta_value = CASE a.meta_key WHEN '_regular_price' THEN '479.55' WHEN '_price' THEN '479.55' WHEN 'costprice' THEN '470.25' WHEN '_stock' THEN '0' WHEN '_backorders' THEN 'no' WHEN '_stock_status' THEN 'outofstock' ELSE a.meta_value END;
As we are by-passing WooCommerce’s usual processing, note that we also need to update _price and _stock_status (which WooCommerce would normally update in the background).
However this method does not cause cached information to be flushed, nor does it fire other WooCommerce triggers which may be important (and should the underlying database schema change in the future, this SQL code may break).
But there is another way, utilising WP-CLI, the WordPress command line interface, in combination with a bit of PHP code that we knocked up:
updatestock.php
<?php //wp --path=/var/www/html/wordpress eval-file updatestock.php stockupdates.csv // sku regular_price costprice stock backorders // 0 1 2 3 4 // Tested against WordPress 4.9.6, WooCommerce 3.4.3, PHP 7.2 in July 2018 //ini_set( 'display_errors', 1 ); error_reporting(E_ALL); function stop_the_insanity() { global $wpdb, $wp_object_cache; $wpdb->queries = array(); if ( is_object( $wp_object_cache ) ) { $wp_object_cache->group_ops = array(); $wp_object_cache->stats = array(); $wp_object_cache->memcache_debug = array(); $wp_object_cache->cache = array(); if ( method_exists( $wp_object_cache, '__remoteset' ) ) $wp_object_cache->__remoteset(); // important } } global $wpdb; $csvfile = $args[0]; if (! is_readable($csvfile)) WP_CLI::error( sprintf("Failed to open %s\n", $csvfile) ); define( 'WP_IMPORTING', true ); // Prevent some post-import extraneous checks // Count number of rows in CSV file $file = new \SplFileObject($csvfile, 'r'); $file->seek(PHP_INT_MAX); $nolines = $file->key() - 1; WP_CLI::log( sprintf("Updating %s products\n", $nolines) ); $handle = fopen($csvfile, "r"); if ($handle) { //$wpdb->query( 'SET autocommit = 0;' ); // Improves time by 8%, but causes site freezes and record lock errors $line = fgets($handle); // Skip header line $progress = \WP_CLI\Utils\make_progress_bar( $csvfile, $nolines, 5000 ); $linesprocessed = 0; while (($line = fgets($handle)) !== false) { $data = str_getcsv($line); $prod_id = wc_get_product_id_by_sku( $data[0] ); if ($prod_id) { try{ $product = wc_get_product( $prod_id ); }catch(\Exception $e){ WP_CLI::warning( sprintf("wc_get_product problem with: %s :%s\n", $prod_id, $e->getMessage()) ); } $product->set_regular_price( $data[1] ); $product->set_stock_quantity( $data[3] ); $product->set_backorders( $data[4] ); $product->update_meta_data( 'costprice', $data[2] ); try{ $product->save(); }catch(\Exception $e){ WP_CLI::warning( sprintf("Save problem with: %s :%s\n", $data[0], $e->getMessage()) ); } if ((++$linesprocessed % 50) == 0){ // Release object cache every 50 products, or we gradually slow down and eventually exhaust memory when processing > 20,000 products //$wpdb->query( 'COMMIT;' ); // Needed if we disable autocommit if (function_exists('stop_the_insanity')){ stop_the_insanity(); } sleep(1); } $progress->tick(); } else { WP_CLI::warning( sprintf("Unknown SKU [%s] on line %d: %s\n", $data[0], $linesprocessed, $line, $data[0]) ); } } fclose($handle); //$wpdb->query( 'COMMIT;' ); $progress->finish(); WP_CLI::success( "Update completed" ); } else { WP_CLI::error( sprintf("Failed to open %s\n", $csvfile) ); }
Aside: We tried using $wpdb->query('SET autocommit = 0;'); to disable auto-commit, along with forcing a commit every 100 products, but this resulted in intermittent freezes to the WordPress site and lock errors. And it only improved the time by 8%, so is only worth pursuing for offline updates. We also tried wp_suspend_cache_addition(true); which does stop memory usage growing, but is 33% slower than our periodic stop_the_insanity() flush approach. We also disabled WordPress’s default WP-Cron behaviour (i.e. running on every page load) and replaced it with a curl triggered invocation every 5 minutes.
To run the above function from the command line, the user must have read permissions on wp-config.php. Assuming you have WP-CLI installed (if not, see above link) and updatestock.php and stockupdates.csv are in the current working directory:
wp --path=/var/www/html/wordpress eval-file updatestock.php stockupdates.csv
Updating 10 products
stockupdates.csv 100% [=======================================] 0:06 / 0:06
Success: Update completed
Using the above (but actually updating three custom fields, not just the one costprice) we achieve updates of approximately 4 products per second – faster than WooCommerce’s native importer (2.4 per second). The code could use more error checking perhaps, but as a starter-for-ten it should put you on the right path.
wc_get_product_id_by_sku()call. So WordPress 5.5.1, WooCommerce 3.6.1, PHP 7.3 now achieves an update rate of 15 products per second – a significant increase.
