Order grid is insanely slow

[PaulEmich]

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.

[tig-jeffreybranderhorst]

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

[jeroenkwaad]

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.

[tig-jaspersmits]

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