Slowness in orders - orders when all stores are selected in Multistore

[ocio87]

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 screenshots

Hello,

I have just configured the multistore but it has been identified that when it is selected with all the stores and Orders - Orders is entered, it is slow to load the page (11-12 Sec). If a specific store is selected, the order list load is normal (1.5 to 3 sec).

https://drive.google.com/file/d/1-esjJvlC-l-_PdFw_ewGxFJj_PQ68xwC/view?usp=sharing

Expected behavior

The loading behavior should be similar to when a single store is selected. The new store has only created one order. It shouldn't influence the loading time as much.

Steps to reproduce

Select all stores in Multi-store.
Enter orders - orders
High wait time for page load.

PrestaShop version(s) where the bug happened

1.7.8.2

PHP version(s) where the bug happened

7.4

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

No response

[Hlavtox]

Can you try to enable debug profilling in cofing/defines.inc.php? It will display all statistics at the end of each page and you can see exactly the timings of each DB query. That could help debug it.

[hibatallahAouadni]

Hello @ocio87

Could you please follow @Hlavtox instructions and make a screenshot.
Please check and feedback.

PS: thanks @Hlavtox for your quick reply 🚀

Thanks!

[ocio87]

Hello,

Query with all stores:

SELECT
CONCAT(
cu.firstname, ' ', cu.lastname
) AS customer,
o.id_order,
o.reference,
o.total_paid_tax_incl,
os.paid,
osl.name AS osname,
o.id_currency,
cur.iso_code,
o.current_state,
o.id_customer,
cu.id_customer IS NULL as deleted_customer,
os.color,
o.payment,
s.name AS shop_name,
o.date_add,
cu.company,
cl.name AS country_name,
o.invoice_number,
o.delivery_number,
(
SELECT
IF(
count(so.id_order) > 0,
0,
1
)
FROM
ps_orders so
WHERE
(so.id_customer = o.id_customer)
AND (so.id_order < o.id_order)
LIMIT
1
) AS new
FROM
ps_orders o
LEFT JOIN ps_customer cu ON o.id_customer = cu.id_customer
LEFT JOIN ps_currency cur ON o.id_currency = cur.id_currency
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
LEFT JOIN ps_order_state os ON o.current_state = os.id_order_state
LEFT JOIN ps_shop s ON o.id_shop = s.id_shop
INNER JOIN ps_country c ON a.id_country = c.id_country
INNER JOIN ps_country_lang cl ON c.id_country = cl.id_country
AND cl.id_lang = : context_lang_id
LEFT JOIN ps_order_state_lang osl ON os.id_order_state = osl.id_order_state
AND osl.id_lang = : context_lang_id
WHERE
o.id_shop IN (: context_shop_ids)
ORDER BY
o.id_order desc
LIMIT
50

Query with a store:

[Hlavtox]

@ocio87 Do you think you can try this?

• Open: \src\Core\Grid\Query\OrderQueryBuilder.php and find public function getSearchQueryBuilder(SearchCriteriaInterface $searchCriteria)
• Comment out $this->addNewCustomerField($qb); and add this above to the select? ->addSelect('1 as new')

[ocio87]

You are the best, with that change the consultation improved.

[Hlavtox]

So, now we must investigate, how to get the "new client" information faster :D

[ocio87]

You're right, after the change the client column everything remained in Yes.

[Hlavtox]

@ocio87 Try one more thing.

• Go back to \src\Core\Grid\Query\OrderQueryBuilder.php and put it back to what it was.
• Find $qb->addSelect('(' . $this->getNewCustomerSubSelect() . ') AS new'); and change it to $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');

We will see if the COUNT is or the subselect itself is the performace issue 👍

[ocio87]

It Works very well.

[hibatallahAouadni]

Thanks @Hlavtox for all you replies 💪 waiting for your PR 🚀
@ocio87 did you encounter this issue with older version?

PS: Related to this PR #26221

[ocio87]

Hello @hibatallahAouadni ,

I don't know, I'm just starting to enable multistore to the site that has been in place since version 1.5.x.

He hadn't had the need to enable.

[hibatallahAouadni]

Ping @PrestaShop/prestashop-core-developers could someone please verified this issue 🙏
Thanks in advance!

[Hlavtox]

@atomiix You worked on the last PR. Do you think you could implement my solution properly?

[ocio87]

Is it okay to keep doing this solution manually in later versions while @atomiix gives us an answer?

Thanks!

[davidcylke]

@Hlavtox you're the man, again! I would pay for your performance improvements files mods :D

[Hlavtox]

@davidcylke HAHA 😄❤️

Guys, we should make a PR for this if it works. @ocio87

[ocio87]

Hello @Hlavtox ,

I see that they did not apply this improvement to version 8.0.1.

Slowness is no longer present in this version?