PS 8.1.5 : Displaying orders on admin is very slow (30 seconds)

[bliscar]

Hello.

I successfully managed the manual migration from PS 1.6.1.24 to PS 8.1.5

I followed this topic which is very useful and precise : https://devdocs.prestashop-project.org/8/basics/keeping-up-to-date/migration/

I just got one issue : 

Displaying the orders list in the admin panel is very slow. It takes around 30 seconds to display (database contain 35,000 orders).

With debug mode, it seems that one SQL query takes a long time to execute, as you can see in the screenshots.

I am not an expert in SQL, so I do not know how to solve this problem 😕

Has anyone ever had this problem? Many thanks.

Edited April 11 by bliscar (see edit history)

[bliscar]

Any idea about my problems ? 🙂

[Ogni]

Hi.

Not an expert but I am currently in the exact same process of migrating a 1.6.1x shop to 8.15 and I faced the exact same issue today, with consistent 15+ seconds doctrine calls on admin pages with ~300k records.

In my case I found that while /app/config/parameters.yml has a default database_host value of "127.0.0.1", my operating system (and maybe firewall) add a lot of overhead when using the loopback IP address. 

As my installation runs everything on the same machine, I changed the database_host value to "localhost" in hope that this would use an unix socket instead of going through the whole tcp layer and everything worked like a charm instantly.

This may not be the exact solution for your problem (please carefully consider your own configuration if you decide to try something similar as there are a lot of factors involved here) but I hope this sheds some light on your issue. 

In summary : my advice is to not to edit SQL code but instead look for something misconfigured in the pipeline between your app and your database instance. 

Happy migrating. 

[bliscar]

Regarding my case, I found a fixing. I change the SQL request in the file :

\src\Core\Grid\Query\OrderQueryBuilder.php

I replace : 

$qb->addSelect('(' . $this->getNewCustomerSubSelect() . ') AS new');

With this :

$qb->addSelect('IF((SELECT so.id_order FROM ps_orders so WHERE (so.id_customer = o.id_customer) AND (so.id_order < o.id_order) LIMIT 1) IS NOT NULL, 0, 1) AS new');

Everything is working fine now in my case.