Order grid is insanely slow
Closed this issue · 3 comments
When having lots of orders in Magento the sales grid tends to get slow together with this module.
Upon sorting a temporary table gets created due to a different group/order column combination.
Queries run op to 12.5 seconds when just viewing the basic order grid of 20 results (no filters applied)
SELECT `main_table`.*, IF(`postnl_shipment`.`confirm_date`, `postnl_shipment`.`confirm_date`, `postnl_order`.`confirm_date`) AS `confirm_date`, IF(`postnl_shipment`.`delivery_date`, `postnl_shipment`.`delivery_date`, `postnl_order`.`delivery_date`) AS `delivery_date`, `postnl_join_order`.`shipping_method`, `postnl_join_payment`.`method` AS `payment_method`, `postnl_join_shipping_address`.`country_id`, `postnl_order`.`is_pakje_gemak`, `postnl_order`.`is_pakketautomaat`, `postnl_order`.`type` AS `delivery_option_type`, `postnl_order`.`options`, group_concat(`postnl_shipment`.`confirm_status` ORDER BY `postnl_shipment`.`created_at` DESC SEPARATOR ",") AS `confirm_status`, group_concat(`postnl_shipment`.`shipping_phase` ORDER BY `postnl_shipment`.`created_at` DESC SEPARATOR ",") AS `shipping_phase`, group_concat(`postnl_shipment`.`shipment_type` ORDER BY `postnl_shipment`.`created_at` DESC SEPARATOR ",") AS `shipment_type`, group_concat(`postnl_shipment`.`product_code` ORDER BY `postnl_shipment`.`created_at` DESC SEPARATOR ",") AS `product_code` FROM `sales_flat_order_grid` AS `main_table`
INNER JOIN `sales_flat_order` AS `postnl_join_order` ON `main_table`.`entity_id`=`postnl_join_order`.`entity_id`
LEFT JOIN `sales_flat_order_payment` AS `postnl_join_payment` ON `main_table`.`entity_id`=`postnl_join_payment`.`parent_id`
LEFT JOIN `sales_flat_order_address` AS `postnl_join_shipping_address` ON `main_table`.`entity_id`=`postnl_join_shipping_address`.`parent_id` AND `postnl_join_shipping_address`.`address_type`='shipping'
LEFT JOIN `tig_postnl_order` AS `postnl_order` ON `main_table`.`entity_id`=`postnl_order`.`order_id`
LEFT JOIN `tig_postnl_shipment` AS `postnl_shipment` ON `main_table`.`entity_id`=`postnl_shipment`.`order_id` GROUP BY `main_table`.`entity_id` ORDER BY created_at DESC LIMIT 20;
Key | Value |
---|---|
no. orders | 268.150 |
runtime | 12,52 sec |
locktime | 0,000341 sec |
viewed rows | 2.716.806 |
These times are insane and unworkable for a production environment. A fix for this is to add virtual columns to the sales/order_grid entity so all values are indexed in the table instead. That way all these joins become unnecessary as well as for the group by.
Hello pauluse,
Thank you for contacting us and making us known of this problem.
I will make an issue for this on the backlog om PostNL Magento 1.
Beste Regards,
Jeffrey Branderhorst
Goedenavond Jeffrey,
Wij gebruiken sinds vorige week de EE versie van de extensie (1.15.5) en ervaren dezelfde traagheid.
Testen gedaan voor/na installatie en het scheelt bij 200 orders 8 seconden laadtijd (12 seconden ipv 4).
Kan dit issue opgelost worden? Logistiek personeel ervaart het als erg langzaam.
Good morning,
This issue will be closed. It is possible to discuss it here: #4
Both issues address the same point. What i would recommend is talking to your PostNL accountmanagers and ask them to put priority on this casenumber at TIG: POSTNLM1-384
Kind regards,
Jasper Smits
TIG