Very slow loading of admin product page due to product features

[ioweb-gr]

Prerequisites

• I understand and accept the project's code of conduct.
• I have already searched in existing issues and found no previous report of this bug.

Describe the bug and add attachments

We have a website with fairly large products with combinations packed with features. Most of our products have over 60 features active (with more than 20 possible values per feature) and some have combinations of over 40 colors and 10 sizes and there are more than 40 images as well per product.

When trying to edit the product in the backend we face extreme slowdown and browser freezing.
It can take more than 5 minutes to give control to the user, until the interface becomes responsive and fully loaded.

Microsoft edge profiler, reports loops of rendering layout which takes more than 2-3 seconds I'm assuming for each new feature it causes layout shift and re-render.

Moreover, we profiled prestashop and noticed there are fairly large execution times just to load the form, apart from rendering it in the browser.

e.g. a request like this

is repeated 3 times with total execution of over 10 seconds to fetch the combinations with the timeline like this

Also the actual request to fetch the product in admin is taking more than 34 seconds

With the timeline showing some very specific delays in evaluating twig templates for display

The server is a 12core dedicated server with 64G RAM and Nvme disk and the load is very low so we can exclude server performance

Expected behavior

Product is open to edit in 5-15 seconds

Steps to reproduce

1. Create a product with combinations of 40 colors and 10 sizes
2. Add 40+ images to the product
3. Add two languages
4. Create 60+ features with at least 20 feature values per feature
5. Set values to the product for all features
6. Try to edit the product in the backend in any browser and watch it freeze.

PrestaShop version(s) where the bug happened

1.7.5.2

PHP version(s) where the bug happened

7.2/7.3

If your bug is related to a module, specify its name and its version

Prestashop Features

[ioweb-gr]

PS: Disabling the features from Advanced -> Performance settings, makes the product load in less than 7 seconds.

[ioweb-gr]

Please also pay special notice to this query taking 2.38s with 60 calls

select * from ps_feature_value v left join ps_feature_value_lang vl on (v.id_feature_value = vl.id_feature_value and vl.id_lang = ?) where v.id_feature = ? and (v.custom is null or v.custom = ?) order by vl.value asc

It appears to me as the order by clause is actually slowing down the query execution.

e.g.

select * from ps_feature_value v left join ps_feature_value_lang vl on (v.id_feature_value = vl.id_feature_value and vl.id_lang = 2) where (v.custom is null or v.custom = 0) order by vl.value asc limit 1,500;

takes 1.2sec to execute

but

select * from ps_feature_value v left join ps_feature_value_lang vl on (v.id_feature_value = vl.id_feature_value and vl.id_lang = 2) where (v.custom is null or v.custom = 0) limit 1,500;

takes 70ms

select * from ps_feature_value v left join ps_feature_value_lang vl on (v.id_feature_value = vl.id_feature_value and vl.id_lang = 2) where (v.custom is null or v.custom = 0) order by vl.value asc limit 1,500
[2023-03-11 10:52:47] 500 rows retrieved starting from 1 in 1 s 277 ms (execution: 1 s 199 ms, fetching: 78 ms)
select * from ps_feature_value v left join ps_feature_value_lang vl on (v.id_feature_value = vl.id_feature_value and vl.id_lang = 2) where (v.custom is null or v.custom = 0) limit 1,500
[2023-03-11 10:52:48] 500 rows retrieved starting from 1 in 133 ms (execution: 72 ms, fetching: 61 ms)

This query seems to come from function \FeatureValueCore::getFeatureValuesWithLang

[Hlavtox]

Hi @ioweb-gr, the product page has been completely remade for Prestashop v8.1, specifically the combination management is now lightning fast.

My suggestion - do you think you could make a copy of your shop's data, try it with 8.1 beta and tell us the results of your tests? https://github.com/PrestaShop/PrestaShop/releases/tag/8.1.0-beta.1

If there are still some bottlenecks, we could try to find a way to optimize it. I think there is nothing that the maintainer team can do with a performance on such old version now, sorry. 👍

[ioweb-gr]

Well the thing is that we bump into other errors when trying to upgrade the website even to 1.7.8 versions as there's no proper upgrade script that can manage the database upgrade.

In that respect Presta reminds me of Joomla updates, where everything broke once you upgraded. Nonetheless I would like to give it a try provided there is an upgrade script for 8.1.0 to try it on a staging env. Is there a clear documentation on how to move from 1.7 to 8?

[Hlavtox]

@ioweb-gr Upgrade using the autoupgrade module should work fine, it will handle everything.

Yes, it can break things that are not compatible, but if you upgrade all your modules before the upgrade, it should be a smooth sail. Then maybe some theme tweaks and fixes and you should be golden.

[ioweb-gr]

Can I skip the backup process of the module?

[ioweb-gr]

So I would love to upgrade it so that I can test the backend at least but it seems I get this error

Got error 'PHP message: PHP Fatal error:  Uncaught TypeError: Argument 1 passed to PrestaShop\\PrestaShop\\Core\\Localization\\CLDR\\ComputingPrecision::getPrecision() must be of the type int, null given, called in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Context.php on line 514 and defined in /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/src/Core/Localization/CLDR/ComputingPrecision.php:41\nStack trace:\n#0 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Context.php(514): PrestaShop\\PrestaShop\\Core\\Localization\\CLDR\\ComputingPrecision->getPrecision()\n#1 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/Cart.php(2154): ContextCore->getComputingPrecision()\n#2 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/src/Adapter/Presenter/Cart/CartPresenter.php(332): CartCore->getOrderTotal()\n#3 /var/www/vhosts/staging.example.com/upgrade.staging.example.com/httpdocs/classes/contro..

[ioweb-gr]

After modifying the interface and class to accept null as parameter the site opened but I can not log in to the backend to test. It doesn't throw any error in php error logs, symfony logs, or the backend page, it just refreshes to a log in prompt.

It seems it can't upgrade the database correctly

Unknown column 'h.active' in 'where clause'<br /><br /><pre>SELECT lower(name) as name
FROM `ps_hook` h
WHERE (h.active = 1)
</pre>

And a silent error on the login screen again

[prestaalba]

Check in your browser the ajax request with PS debug mode enabled. There you should find the error details. Please let me know how is the performance in PS 8.1 in your scenario.

[paulnoelcholot]

@kpodemski could you please test this? Thanks :)

[kpodemski]

Hi @ioweb-gr

First of all, thanks for reporting this. Regarding autoupgrade: it's quite a complicated subject. There's clearly a problem with your database structure because this query from 1.7.8.0.sql, didn't work:

ALTER TABLE PREFIX_hookADDactiveTINYINT(1) UNSIGNED DEFAULT 1 NOT NULL AFTERdescription;

Anyway, I think you reported something that could be improved, whether it's a new product page or some other places using features coming from this method. We discuss improvements using "Github Discussions", so I'm transferring this conversation there.

For the time being, I suggest you remove ORDER BY. Could you let me know how many records you have in feature_lang?

[ioweb-gr]

@kpodemski
ps_feature_lang -> 110 records
ps_feature_value_lang -> 900670 records

The query itself worked fine when executed by itself, so the reason why it failed is unknown. Is it possible this specific upgrade script was never executed due to a bug in any case?

After adding the column I still experience the same login issue. The problem is there is no error logged whatsoever, just a dead page as shown in the image. Any assistance is appreciated to get to the bottom of this issue as it's a critical performance issue.

Blackfire reports indicate very poor performance.

[ioweb-gr]

Any ideas why I get this error during log in? I'm trying to upgrade so that I can test the performance on the upgraded database (backend only) and I get so many db errors. Autoupgrade doesn't really work properly

Column 'id_shop' cannot be null<br /><br /><pre>INSERT INTO `ps_log` (`severity`, `error_code`, `message`, `object_id`, `id_shop`, `id_shop_group`, `id_lang`, `in_all_shops`, `id_employee`, `object_type`, `date_add`, `date_upd`) VALUES ('1', '0', 'Back office connection from 79.167.19.231', '0', NULL, NULL, '1', '1', '17', '', '2023-03-30 10:18:24', '2023-03-30 10:18:24')</pre>
at line 773 in file classes/db/Db.php

The error's stack trace points to core functions only

DbCore->displayError - [line 385 - classes/db/Db.php]
DbCore->query - [line 744 - classes/db/Db.php]
DbCore->q - [line 477 - classes/db/Db.php]
DbCore->insert - [line 603 - classes/ObjectModel.php]
ObjectModelCore->add - [line 177 - classes/PrestaShopLogger.php]
PrestaShopLoggerCore::addLog - [line 246 - controllers/admin/AdminLoginController.php]
AdminLoginControllerCore->processLogin - [line 198 - controllers/admin/AdminLoginController.php]
AdminLoginControllerCore->postProcess - [line 308 - classes/controller/Controller.php]
ControllerCore->run - [line 510 - classes/Dispatcher.php]
DispatcherCore->dispatch - [line 92 - admin/index.php]

[sudo-do]

I was imported sql backup of necessary data to fresh 8 installation. Auto upgrade doesn't really work good.

[ioweb-gr]

I see, but ultimately we're going to have to upgrade our shop. We can't just migrate every time an upgrade is needed by installing and restoring data only. So both issues are equally important

[sudo-do]

Right, both issues are high priority

[ioweb-gr]

I gave a shot to the autoupgrade with a different php version (7.4) and it's working again, but it threw a warning. I think my issue is coming due to this warning during autoupgrade which prevents some set or db updates to run

WARNING -
            <div class="upgradeDbError">
            [WARNING] SQL 1.7.6.2
            1136 in INSERT INTO `ps_product_lang` SELECT
`entity`.`id_product`, 1, `entity`.`id_lang`, `entity`.`description`, `entity`.`description_short`, `entity`.`link_rewrite`, `entity`.`meta_description`, `entity`.`meta_keywords`, `entity`.`meta_title`, `entity`.`name`, `entity`.`available_now`, `entity`.`available_later`, `entity`.`delivery_in_stock`, `entity`.`delivery_out_stock`
FROM `ps_product_lang` entity
LEFT JOIN `ps_product_lang` entity2 ON `entity2`.`id_shop` = 1 AND `entity`.`id_product` = `entity2`.`id_product`
WHERE `entity2`.`id_shop` IS NULL: Column count doesn't match value count at row 1</div>
ERROR - SQL 1.7.6.2 1136 in INSERT INTO `ps_product_lang` SELECT
`entity`.`id_product`, 1, `entity`.`id_lang`, `entity`.`description`, `entity`.`description_short`, `entity`.`link_rewrite`, `entity`.`meta_description`, `entity`.`meta_keywords`, `entity`.`meta_title`, `entity`.`name`, `entity`.`available_now`, `entity`.`available_later`, `entity`.`delivery_in_stock`, `entity`.`delivery_out_stock`
FROM `ps_product_lang` entity
LEFT JOIN `ps_product_lang` entity2 ON `entity2`.`id_shop` = 1 AND `entity`.`id_product` = `entity2`.`id_product`
WHERE `entity2`.`id_shop` IS NULL: Column count doesn't match value count at row 1

[ioweb-gr]

OK I've managed to open the admin page by fixing the query above and now I have access to the catalog page on the backend. There is no difference whatsoever with Prestashop 8.0.1 and with hundreds of combinations and images on a product it won't open unless we reach a minute or so. I'll see if I can upgrade further

[ioweb-gr]

And I'm getting error

INFO - === Step upgradeDb
ERROR - Error during database upgrade. You may need to restore your database.
ERROR - [ERROR] Version to install is too old. Current version: 8.0.2.0. Version to install: 8.0.2.

[ioweb-gr]

Latest version 8.0.2 and the issue persists. Products with lots of features are very slow to openin the backend.

[sudo-do]

indexing the tables containing id_feature, custom, id_feature_value, and id_lang will likely result in improved performance. I won't be able to verify this with hundreds of product features anyway.

[ioweb-gr]

I could request permission to share the tables containing products / product features if needed but it can only be shared via a secure channel with prestashop

[ioweb-gr]

So here's the timeline of how long it takes to load the page... 51 seconds on 8.0.2 which is pretty much slower than 1.7.5.2 so not only did it not improve, it's actually worse.

[ioweb-gr]

Sorry I don't understand the difference this is a profile of opening a product in the admin catalog grid

[MatShir]

On Advanced Parameters > New & Experimental Features, you will see a switch to test the new product page. Once enabled, go to the product page and you can check if the performance is improved :)

[ioweb-gr]

@MatShir I was able to try it after all and there's no difference whatsoever. The product page takes ages to load both in normal and with experimental features.

[MatShir]

Are you sure :/ ? The new product page is officially available from the 8.1. Could you do some test and share the test then ?

[MatShir]

Hi @ioweb-gr,
Could you try the new 8.1.3 patch release? There are query improvements on the product page, and in the future (9.0.0), there are more features on the develop branch.