Web Service goes slow with more products

[jeffPro97]

Describe the bug i've some speed problem when adding product with webservice When I add only few products, the adding is relatively fast, but when I add more products to the shop, for example 1000 - 5000 products, the adding goes always slower and slower.

For example:

• adding 100 product to the empty shop (without pictures) takes about 40 seconds
• adding 1000 products takes about 12 minutes
• adding 2000 products takes about 36 minutes

it is also slow when i try to remove 3000 products in back office at a same time, for example : when i try to delete 10 products it is 1s per product when i try to delete 3000 products it is 5s per product

Additional information PrestaShop version: 1.7.5.1

Thank you

[khouloudbelguith]

Hi @jeffPro97,

Thanks for your report. I manage to reproduce the issue with PS1.7.6.1. I used this script

<?php
/**
 * Created by PhpStorm.
 * User: khouloud.belguith
 * Date: 03/09/19
 * Time: 12:00
 */

ini_set('display_errors','on');
// Here we define constants /!\ You need to replace this parameters
define('DEBUG', true); // Debug mode
define('PS_SHOP_PATH', 'http://shop.com/'); // Root path of your PrestaShop store
define('PS_WS_AUTH_KEY', 'Key'); // Auth key (Get it in your Back Office)
require_once('./PSWebServiceLibrary.php');

$psXML = <<<XML
<prestashop>
<product>
  <id/>
  <name>
  <language id="1">New Peoducts WS</language></name>
  <price>120</price>
  <link_rewrite>aaaa</link_rewrite>
  <advanced_stock_management>1</advanced_stock_management>
  <active>1</active>
  <id_category_default>2</id_category_default>
  <associations>
    <categories>
      <category><id>2</id></category>
    </categories>
  </associations>
  <reference>boom</reference>
  <low_stock_alert>0</low_stock_alert>
  <id_shop_default>1</id_shop_default>
 <available_for_order>1</available_for_order>
  <minimal_quantity>1</minimal_quantity>
  <state>1</state>
</product>
</prestashop>
XML;
$webService = new PrestaShopWebservice(PS_SHOP_PATH, PS_WS_AUTH_KEY, DEBUG);
$xml = new SimpleXMLElement($psXML);
var_dump($xml->asXML());
$opt = array( 'resource' => 'products' );
$opt['postXml'] = $xml->asXML();
//$opt['id_shop'] = 'all';
for ($i = 1; $i <= 3000; $i++) {
    $xml = $webService->add( $opt );
}

$xml = $webService->add( $opt );

And I have this results:

• 1000 products => 22 minutes
• 2000 products => 42 minutes
• 3000 products => 1hour

I’ll add this to the debug roadmap so that it’s fixed. If you have already fixed it on your end or if you think you can do it, please do send us a pull request! Thanks!

[jeffPro97]

Hello, do you have an idea of when this bug will be resolve ? I tried every modification of performance i found, but actually when i have 3000 products, i can't go under 4s per product. Thanks

[khouloudbelguith]

@jeffPro97, it is added to our bug roadmap & we have a team working on the performance issues. Thanks for your patience.

[lichesser]

Hy,

same here but even worse. We are exporting > 50k products from our ERP and the update takes about 25 to 35 seconds meanwhile for one product (the math is on you). Is there something I can do to speed up webservice meanwhile until the performance bug is fixed and released (I am not really willing to accept, that I need to wait 100 hours for 12k products)?

[lichesser]

Hy,

it turned out, that only updating a product is taking that long (up to 30 seconds), not the Add action. Maybe there is a massive sql statement triggered when updating, but it's hard to find out, what the real problem could be. Is there a way to debug or log the webservice in any way?

[lichesser]

Final conclusion after logging mysql: when doing an update to a product (I don't know if this also happens if you save a product in backoffice) it calls a massive update logic which only sets the position of a product in 'vaps_category_product' to a new position.

UPDATE category_product SET position = '66925' WHERE id_category = 2 AND id_product = 66932

Since we have more than 60k products, more than 60k update statements are called on every product update. So in worst case I have to do at least 3.6 * 10^6 update calls when updating my complete stock.

If I do an update in backoffice none of the above mentionned update statements happen.

So, imho this is nothing for your bug roadmap but your 'optimize-my-product' queue.

[lichesser]

Final update of our case: if we set position_in_category to null, update is performing like expected. Don't know what is the case behind the fact, that a position in a category is changed on every update (and of course for every other product within this category).

Regards, L

[alexdn90]

Hello,

We have the same problem here. In wich file did you make this: position_in_category to null ?

Thanks, Alexandre

[khouloudbelguith]

We have an issue reported previously about position_in_category https://github.com/PrestaShop/PrestaShop/issues/14903

Thanks!

[lichesser]

Hello,

We have the same problem here. In wich file did you make this: position_in_category to null ?

Thanks, Alexandre

Hy Alexandre,

we do this in our exporter script (that part of our C# application where addProduct or updateProduct happens)

Regards, L

[houckontape]

Hello , would it be possible to have information on the problem of slowness during important import of product? I meet the same problem, is there a job to start on this subject? maybe a priority track ?

thanks ! ps: my first job today looking mysql log , and more coffee .

[matks]

As discussed together with Product Team, this is not a bug, this is an improvement.

[ifelseoficial]

I am working with prestashop 1.7;

I check mysql query logs and see the same case that @lichesser:

"when doing an update to a product (I don't know if this also happens if you save a product in backoffice) it calls a massive update logic which only sets the position of a product in 'vaps_category_product' to a new position.

UPDATE category_product SET position = '66925' WHERE id_category = 2 AND id_product = 66932 "

To solve this case, if you wan't use position in category to sort product, and you are using webservice to update product. You should change funtion setWsPositionInCategory($position) from classes/Product.php, in my case it where in 6231 line

/**
 * Webservice setter : set virtual field position in category.
 *
 * @return bool
 */
public function setWsPositionInCategory($position)
{

[...]
}

and add in first line inside function return true;

public function setWsPositionInCategory($position)
{
    return true;

[...]
}

Now webservice don't exec "massive update logic" to change de position product in category. In mi case performance has improved remarkably (3 times, 15 min -> 5 min with 135 product in development server);

As well as I has changed product pagination in Backoffice>shop parameters>products>pagination(bottom)>sort by> update date