updateCategories() inconsistent times, works almost instantly sometimes and sometimes it slows to a crawl

[Tomas Petreikis]

Has anyone had any issue with using updateCategories() before for updating lots of products categories at once? We're using this to map supplier categories to our own categories and we need to change 100's-1000's of products categories. The problem we're running into is that updateCategories usually slows down to a crawl when updating larger amounts of products 100+ and will take hours to do it.

I've checked the timings of it running on 50 products and sometimes it updates them fine and sometimes it starts slowing down immensely after 30-40.

One other thread suggested it might be an action hook triggering on updateCategories(), but even after disabling / overriding the modules with this hook (ps_mainmenu, ps_facetedsearch) it still didn't fix it.

Our shop currently has ~150 000 products and 300+ categories, the server hardware shouldn't be a problem, checked the metrics and nothing was bottlenecking it.

Would love to hear any suggestions on where the problem could be originating from, or a way to find out what's causing it myself.

Attaching an example script for bulk updating categories.

 

$productIds = Db::getInstance()->executeS('SELECT id_product FROM `' . _DB_PREFIX_ . 'product` ORDER BY id_product LIMIT 60');
$categoryId = 1;
$time_pre_total = microtime(true);
foreach ($productIds as $id) {
  echo '------------------------------------------';
  var_dump($id);
  echo '<br>';
  $time_pre = microtime(true);
  $product = new Product((int)$id['id_product'], null, null, (int)Context::getContext()->shop->id);
  $time_post = microtime(true);
  $exec_time = $time_post - $time_pre;
  var_dump($exec_time);
  echo '<br>';
  $time_pre = microtime(true);
  $product->updateCategories([(int)$categoryId]);
  $time_post = microtime(true);
  $exec_time = $time_post - $time_pre;
  var_dump($exec_time);
  echo '<br>';
  $time_pre = microtime(true);
  $product->id_category_default = $categoryId;
  $time_post = microtime(true);
  $exec_time = $time_post - $time_pre;
  var_dump($exec_time);
  echo '<br>';
  $time_pre = microtime(true);
  $product->update();
  $time_post = microtime(true);
  $exec_time = $time_post - $time_pre;
  var_dump($exec_time);
  echo '<br>';
  echo '------------------------------------------';
}
echo '------------------------------------------';
echo '------------------------------------------';
echo '<br>';
$time_post_total = microtime(true);
$exec_time_total = $time_post_total - $time_pre_total;
var_dump($exec_time_total);
echo '<br>';
echo 'cycle ended';

 

[Tomas Petreikis]

The issue was with the cleanPositions() method in Products class, it was recalculating positions after each update, I'll be looking to override / write a different method for myself to use.