Jump to content

Order confirmation last step for ordering very slow (about 30 secs)

robb84

By robb84
in Core developers

 Share

Recommended Posts

robb84 Newbie

robb84

Posted
Posted

I'm just investigating what is the cause of slowdown loading in the last checkout step. I'm attaching screenshot and debug code :

 

screen1-slow-order.thumb.png.b6a8ac5e307f929540b6f0c47b03448a.png 

Tables stress
108 product_attribute
105 product_attribute_shop
77 attribute_lang
77 attribute
77 product_attribute_combination
65 attribute_group_lang
35 stock_available
25 product
24 image
23 image_shop
23 image_lang
22 product_shop
21 cart_product
-------------------------
ObjectModel instances

Product:	171	

/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Cart.php [2321]
/classes/Cart.php [2321]
/classes/Cart.php [2321]
/classes/stock/StockAvailable.php [470]
/modules/ps_emailalerts/ps_emailalerts.php [511]
/modules/ps_emailalerts/ps_emailalerts.php [511]
/classes/order/OrderDetail.php [853]
/classes/stock/StockAvailable.php [470]
/modules/ps_emailalerts/ps_emailalerts.php [511]
/modules/ps_emailalerts/ps_emailalerts.php [511]
/classes/order/OrderDetail.php [853]
/classes/stock/StockAvailable.php [470]
/modules/ps_emailalerts/ps_emailalerts.php [511]
/modules/ps_emailalerts/ps_emailalerts.php [511]
/classes/order/OrderDetail.php [853]
/src/Core/Foundation/Database/EntityRepository.php [141]
/src/Core/Foundation/Database/EntityRepository.php [141]
/src/Core/Foundation/Database/EntityRepository.php [141]
/src/Core/Foundation/Database/EntityRepository.php [141]
/src/Core/Foundation/Database/EntityRepository.php [141]
/src/Core/Foundation/Database/EntityRepository.php [141]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/src/Adapter/Image/ImageRetriever.php [52]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/src/Adapter/Image/ImageRetriever.php [52]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/src/Adapter/Image/ImageRetriever.php [52]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/src/Adapter/Image/ImageRetriever.php [52]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/src/Adapter/Image/ImageRetriever.php [52]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/src/Adapter/Image/ImageRetriever.php [52]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]
/classes/Link.php [98]

-----------------------------

Customer:46	

/config/config.inc.php [212]
/modules/ps_wirepayment/controllers/front/validation.php [46]
/classes/Carrier.php [1529]
/classes/PaymentModule.php [208]
/classes/order/OrderDetail.php [710]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/modules/myprestaloyalty/LoyaltyModule.php [71]
/classes/order/Order.php [290]
/classes/order/Order.php [290]
/classes/order/Order.php [290]
/classes/order/Order.php [290]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]
/classes/Product.php [603]

---------------------------

Address:18	

/classes/module/Module.php [1832]
/classes/tax/Tax.php [197]
/classes/Product.php [2984]
/classes/Product.php [4538]
/classes/Cart.php [3408]
/classes/Cart.php [2545]
/classes/PaymentModule.php [299]
/classes/PaymentModule.php [350]
/classes/order/OrderDetail.php [709]
/classes/order/OrderDetail.php [745]
/classes/order/OrderDetail.php [745]
/classes/order/OrderDetail.php [745]
/modules/ps_emailalerts/ps_emailalerts.php [280]
/modules/ps_emailalerts/ps_emailalerts.php [281]
/classes/PaymentModule.php [747]
/classes/PaymentModule.php [748]
/classes/controller/FrontController.php [1570]
/classes/shop/Shop.php [465]
--------------------------------------------

Carrier:17	

/classes/Cart.php [2321]
/classes/Cart.php [4573]
/classes/Cart.php [3383]
/classes/Cart.php [4573]
/classes/Cart.php [2321]
/classes/Cart.php [2321]
/classes/Cart.php [2321]
/classes/Cart.php [2576]
/classes/Cart.php [4573]
/classes/Cart.php [4573]
/classes/Cart.php [2761]
/classes/PaymentModule.php [308]
/classes/order/OrderInvoice.php [634]
/classes/order/OrderInvoice.php [634]
/classes/order/OrderInvoice.php [634]
/modules/ps_emailalerts/ps_emailalerts.php [283]
/modules/ps_legalcompliance/ps_legalcompliance.php [733]

------------------------------------------------------------

State:15	

/modules/ps_emailalerts/ps_emailalerts.php [345]
/modules/ps_emailalerts/ps_emailalerts.php [348]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/PaymentModule.php [749]
/classes/PaymentModule.php [750]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/controller/FrontController.php [1600]

---------------------------------------------------------------------
Country: 15	

/config/config.inc.php [146]
/classes/controller/FrontController.php [452]
/classes/Cart.php [2546]
/classes/PaymentModule.php [300]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/AddressFormat.php [353]
/classes/controller/FrontController.php [1601]
-----------------------------------------------------

Shop:12

/config/config.inc.php [120]
/classes/PaymentModule.php [213]
/classes/stock/StockAvailable.php [715]
/classes/stock/StockAvailable.php [715]
/classes/stock/StockAvailable.php [715]
/classes/stock/StockAvailable.php [715]
/classes/stock/StockAvailable.php [715]
/classes/stock/StockAvailable.php [715]
/classes/Mail.php [142]
/modules/myprestaloyalty/LoyaltyModule.php [73]
/classes/Mail.php [142]
/classes/Mail.php [142]

I'm on Prestashop 1.7.2 and PHP 7.1. Another thing, it's the Shipping and Payment section are very slow to load in backoffice.

 

What can cause this ??

Thank you

Link to comment
Share on other sites
  • 2 months later...
robb84 Newbie

robb84

Posted
  • Author
Posted

Well, investigating the situation, I found the file that slow down the last step for about 25 seconds, resides in the query for updating the availability in database. 
The file is: /src/Adapter/StockManager.php at this function here: 

public function updatePhysicalProductQuantity($shopId, $errorState, $cancellationState, $idProduct = null, $idOrder = null)
    {
        $this->updateReservedProductQuantity($shopId, $errorState, $cancellationState, $idProduct, $idOrder);
        $updatePhysicalQuantityQuery = '
            UPDATE {table_prefix}stock_available sa
            SET sa.physical_quantity = sa.quantity + sa.reserved_quantity
            WHERE sa.id_shop = ' . (int) $shopId . '
        ';
        if ($idProduct) {
            $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); 

    }

and here: 

private function updateReservedProductQuantity($shopId, $errorState, $cancellationState, $idProduct = null, $idOrder = null)
    {
        $updateReservedQuantityQuery = '
            UPDATE {table_prefix}stock_available sa
            SET sa.reserved_quantity = (
                SELECT SUM(od.product_quantity - od.product_quantity_refunded)
                FROM {table_prefix}orders o
                INNER JOIN {table_prefix}order_detail od ON od.id_order = o.id_order
                INNER JOIN {table_prefix}order_state os ON os.id_order_state = o.current_state
                WHERE o.id_shop = :shop_id AND
                os.shipped != 1 AND (
                    o.valid = 1 OR (
                        os.id_order_state != :error_state AND
                        os.id_order_state != :cancellation_state
                    )
                ) 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 = :shop_id
        ';
        $strParams = array(
            '{table_prefix}' => _DB_PREFIX_,
            ':shop_id' => (int) $shopId,
            ':error_state' => (int) $errorState,
            ':cancellation_state' => (int) $cancellationState,
        );
        if ($idProduct) {
            $updateReservedQuantityQuery .= ' AND sa.id_product = :product_id';
            $strParams[':product_id'] = (int) $idProduct;
        }
        if ($idOrder) {
            $updateReservedQuantityQuery .= ' AND sa.id_product IN (SELECT product_id FROM {table_prefix}order_detail WHERE id_order = :order_id)';
            $strParams[':order_id'] = (int) $idOrder;
        }
        $updateReservedQuantityQuery = strtr($updateReservedQuantityQuery, $strParams);
        
            return Db::getInstance()->execute($updateReservedQuantityQuery);
     
    }

Now, commenting the lines: 

// return Db::getInstance()->execute($updateReservedQuantityQuery);

and 

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

in both functions. The page loading confirmation time, is about 1 sec.😎

The million dollar question is: By commenting out this line, can it cause strange behavior? I have not noticed any freak, the stock decreases regularly from the product/combination after order confirmation, and everything seems ok when increasing the stock level from backend to product/combinations.
    
Hope in a reply for someone more expert than me :)

Thank you.

Link to comment
Share on other sites
rrataj Rookie

rrataj

Posted
Posted

Simple answer - it depends. As far as I can see you just disabled "Lowering stock for bought products" (It's a little simplification, but that's the main point) , so if you don't use stock information then you should be fine, but if you use it, for example you have only 10 products available, then you will have a problem - products stock won't be updated after each sale, it will always stay at 10.

And side effect - these changes will be overriden after prestashop code upgrade.

I hope now you know a bit more :)

  • Like 1
Link to comment
Share on other sites
robb84 Newbie

robb84

Posted
  • Author
Posted

Thanks for reply rrataj! What I saw, is that after placing an order, the quantity decrease from stock (I can see it in product backend). Is there any way to avoid commenting these lines, while keeping the same result ? As I understood, when placing an order, it should be update only the product/combination stock. But I suspect it update all products stock and combination...otherwise I can't explain how it takes more than 40 seconds to update only the stock of only 1 product.

 

Cheers,

Roberto

Link to comment
Share on other sites
rrataj Rookie

rrataj

Posted
Posted

It looks like this is an open issue in PrestaShop: https://github.com/PrestaShop/PrestaShop/issues/14703

There are couple solutions mentioned there, but none is ideal :) :

- commenting out (as you did) 

- Keeping the last update date in cache, and executing "updatePhysicalProductQuantity" when the last update every 30 mins (if stock page is loaded).

- quick patch with adding check for $idOrder: 

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);

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...