CODEWITHSUNDEEP
gridmagento2
Anton-GrNL
Anton-GrNL

Reputation: 1

Magento 2 grids slow

On our Magento 2 store we have gained quite a few customer records (around 800.000) and orders (around 1.600.000). We are experiencing bad performance from the sales grid and customer grid pages. So, I decided to do some research on the sql queries and noticed the following.

The slowest query (around 10 secs) on the order grid was (without any filters):

SELECT COUNT(DISTINCT main_table.entity_id) FROM sales_order_grid AS main_table LEFT JOIN ae_ec AS ec ON main_table.entity_id = ec.ec_order_id LEFT JOIN amasty_mostviewed_pack_sales_history ON amasty_mostviewed_pack_sales_history.order_id = main_table.entity_id LEFT JOIN sales_order AS sales_order_table ON main_table.entity_id = sales_order_table.entity_id

On the customer grid it was the following (around 7 seconds):

SELECT COUNT(*) FROM customer_grid_flat AS main_table LEFT JOIN directory_country_region AS rct ON rct.region_id=main_table.billing_region_id LEFT JOIN directory_country_region_name AS rnt ON rnt.region_id=main_table.billing_region_id AND rnt.locale='en_US' LEFT JOIN amasty_rewards_rewards AS amrewards ON main_table.entity_id = amrewards.customer_id AND amrewards.id IN ( SELECT MAX(id) FROM amasty_rewards_rewards GROUP BY customer_id)

I guess this is only for displaying the total number of orders. Also, the extra joins are not needed I think. They are added by the extension providers.Any advice if there is a (good) way of disabling the counts on the grids? 10 seconds improvement on loading time is huge for us.

Upvotes: 0

Views: 387

Answers (1)

Andrew
Andrew

Reputation: 12809

Are you using OpenSource or Adobe Commerce?

If you're using Adobe Commerce you can archive orders:

https://experienceleague.adobe.com/docs/commerce-admin/stores-sales/order-management/orders/order-archive.html?lang=en

This feature reduced the amount of orders in the order grid and will keep your site performant.

Unfortunately this feature is not available in the OpenSource version.

In that case it could be worth looking at a 3rd party module such as : https://amasty.com/order-archive-for-magento-2.html

Upvotes: 0

Related Questions