Prestashop: Poor performances in order status update due very slow StockManager.php queries

[gennaris]

After troubleshooting slowdowns in order status change from a customer with a large catalogue I noticed that there are two queries that takes a very lot of time to run.

ValidateOrder method and status change are taking up to 15~20 seconds due to those queries.

First query in /src/Adapter/StockManager.php:171 (updateReservedProductQuantity method)
takes ~ 7 seconds to run on high-spec dedicated MySQL server
Second query in /src/Adapter/StockManager.php:118 (updatePhysicalProductQuantity method)
takes ~ 10 seconds to run on high-spec dedicated MySQL server

First query is :

UPDATE ps_stock_available sa
SET sa.reserved_quantity = (
SELECT SUM(od.product_quantity - od.product_quantity_refunded)
FROM ps_orders o
INNER JOIN ps_order_detail od ON od.id_order = o.id_order
INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
WHERE o.id_shop = 1 AND
os.shipped != 1 AND (
o.valid = 1 OR (
os.id_order_state != 8 AND
os.id_order_state != 6
)
) AND sa.id_product = od.product_id AND
sa.id_product_attribute = od.product_attribute_id
GROUP BY od.product_id, od.product_attribute_id
)
WHERE sa.id_shop = 1
AND sa.id_product IN (SELECT product_id FROM ps_order_detail WHERE id_order = 15244)

Second query is :

UPDATE ps_stock_available sa
SET sa.physical_quantity = sa.quantity + sa.reserved_quantity
WHERE sa.id_shop = 1

This query seems to update the whole ps_stock_available table even if I am changing the status of a single order (?).

In my case the ps_stock_available table have 903.000 distinct rows (website have 150k products with 740k combinations) so you'll need a large shop to reproduce this.

Note that the problem was already represented in BOOM-6265 , but issue was closed and mis-referenced to other closed issues that had nothing to do with this problem, so it is still present in 1.7.6.0

[khouloudbelguith]

Hi @gennaris,

Thanks for your report.
Ping @eternoendless, @PrestaShop/prestashop-core-developers what do you think?

Thanks!

[lutek]

Got the same problem!
I just wanted to publish a new live version of prestashop and I stopped it. Conversions will decrease.

I got 60k rows in ps_stock_available.

/src/Adapter/StockManager.php:118
/classes/order/OrderHistory.php:419
/classes/PaymentModule.php:548
/modules/ps_wirepayment/controllers/front/validation.php:64
/tools/profiling/Controller.php:225
/override/classes/Dispatcher.php:178
/index.php:73

Prestashop: 1.7.6.1

my quick patch

Index: src/Adapter/StockManager.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- src/Adapter/StockManager.php    (revision f02476e96bf465bfd938dfbf20795e4c6b25d330)
+++ src/Adapter/StockManager.php    (revision 99fd101d09a5ef878e1a65cf843788d54332b428)
@@ -113,6 +113,10 @@
             $updatePhysicalQuantityQuery .= ' AND sa.id_product = ' . (int) $idProduct;
         }

+        if ($idOrder) {
+            $updatePhysicalQuantityQuery .= ' AND sa.id_product IN (SELECT product_id FROM {table_prefix}order_detail WHERE id_order = ' . (int) $idOrder . ')';
+        }
+
         $updatePhysicalQuantityQuery = str_replace('{table_prefix}', _DB_PREFIX_, $updatePhysicalQuantityQuery);

         return Db::getInstance()->execute($updatePhysicalQuantityQuery);

[gennaris]

@lutek - I had a drastical approach :D I have totally commented out the two queries (my customer does not need - use the physical / reserved quantities) and everything seems good so far, but I think both methods must be reviewed by the core developers as it seems to be quite a blocker for large catalogs.

[PierreRambaud]

Hi @gennaris and really thanks for your explanation.
I think we can try your fix, it's pretty relevant and seems logic.
Could you create a pull request?

[Mindfield-Studio]

Hi,

Just a "stupid" question: Why updating physical & reserved stock when it's already supposed to be updated in Payment process, Order History, and Admin Order Controller ?

So, I'm testing without this "update" by commenting this in "StockRepository":

public function getData(QueryParamsCollection $queryParams)
{
//        $this->stockManager->updatePhysicalProductQuantity(
//            $this->shopId,
//            $this->orderStates['error'],
//            $this->orderStates['cancellation']
//        );

        return parent::getData($queryParams);
}

PS: you can also remove this method (it's useless without the update)

[Mindfield-Studio]

So, I found another solution:
Keeping the last update date in cache, and executing "updatePhysicalProductQuantity" when the last update every 30 mins (if stock page is loaded).

[Matt75]

@PrestaShop/prestashop-core-developers this issue cause trouble with PrestaShop Checkout too. On shared hosting if PaymentModule::validateOrder() is timeout PrestaShop Order is not created or partially created !

[ComonSoft]

Hi,
We have the same problem with two sites that have thousands of products and hundreds of thousands of orders. It takes almost 20 seconds to 1 minute! Since the passage in 1.7 we lose orders! Is there at least a fix or solution? The 1.6 was a turbo compared to the 1.7 for this part. One of our customers is furious and one of them report to migrate to 1.7!
PS 1.7.6.5 PHP 7.2 Mariadb / debian dedicated server 8 process / 30Gb RAM

In complement the slowest query is updateReservedProductQuantity() in StockManager
The explain query

PRIMARY | | ALL | distinct_key | NULL | NULL | NULL | 2 |  
-- | -- | -- | -- | -- | -- | -- | -- | --
1 | PRIMARY | sa | ref | product_sqlstock,id_shop,id_product | product_sqlstock | 4 | sitev2.ps_order_detail.product_id | 4 | Using where; Using index
3 | MATERIALIZED | ps_order_detail | ref | order_detail_order,product_id,id_order_id_order_de... | order_detail_order | 4 | const | 2 |  
2 | DEPENDENT SUBQUERY | o | ref | PRIMARY,current_state,id_shop | id_shop | 4 | const | 155705 | Using where
2 | DEPENDENT SUBQUERY | os | eq_ref | PRIMARY | PRIMARY | 4 | sitev2.o.current_state | 1 | Using where
2 | DEPENDENT SUBQUERY | od | ref | order_detail_order,product_id,product_attribute_id... | order_detail_order | 4 | sitev2.o.id_order | 1 | Using where

Please help.

[ComonSoft]

Same issue as Matt75, timeout error during payement, partial Order, payement error.
We loose a lot of customers ! Any solution or temporary fix ? Solution proposed above is included in PS 1.7.6.5 and it still very slow.

[marcusagm]

I have the same problem, there are cases where the execution takes 12 to 1 minute.
Why can updatePhysicalProductQuantity be called without the product ID? It does not make sense to update all records. This can result in many problems with the stock. I had a lot of orders with out-of-stock products.

There are five files in which this method is called without the product ID.

https://github.com/PrestaShop/PrestaShop/search?q=updatePhysicalProductQuantity&unscoped_q=updatePhysicalProductQuantity

[reanim8ed]

On order validation the same updatePhysicalProductQuantity method is run twice:
In /classes/order/OrderHistory.php:414
And in /classes/PaymentModule:545