#1 Prefetch Payments for Orders in Admin Grid

[lbajsarowicz]

PR Checklist

Please check if your PR fulfills the following requirements:

• [ ] The commit message is descriptive of the changes within (see https://keepachangelog.com/en/1.1.0/)
• [ ] Code conforms to Magento coding standards
• [ ] Code is compatible with all currently supported versions of Magento and PHP

PR Type

Performance optimization for Admin Sales grid by prefetching Payments for the whole Orders collection

With a listing of 100 Orders

The number of SQL calls dropped almost by half

| Calls     | Time             | SQL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|-----------|------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 100x -100 | 148 ms -76 ms    | SELECT `u`.* FROM ( (SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_int` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_varchar` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_datetime` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_decimal` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_text` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?))) ) AS `u` ORDER BY `store_id` ASC                                                                                                                                                                                                                                                          |
| 200x -200 | 20.3 ms -17 ms   | select ... from catalog_category_product where (product_id = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 100x -100 | 27.1 ms -30 ms   | select main_table.*, ..., if(...) as title, ..., if(...) as price, if(...) as price_type from catalog_product_option as main_table join catalog_product_entity as cpe on cpe.entity_id = main_table.product_id join catalog_product_option_title as default_option_title on default_option_title.option_id = main_table.option_id left join catalog_product_option_title as store_option_title on store_option_title.option_id = main_table.option_id and store_option_title.store_id = ? left join catalog_product_option_price as default_option_price on default_option_price.option_id = main_table.option_id and default_option_price.store_id = ? left join catalog_product_option_price as store_option_price on store_option_price.option_id = main_table.option_id and store_option_price.store_id = ? where (cpe.entity_id = ?) and (default_option_title.store_id = ?) order by sort_order asc, title asc                                                                                                                                                                                                                                                                                                                          |
| 100x -100 | 12.9 ms -16 ms   | select t.* from catalog_product_entity as t where (entity_id = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 100x -100 | 63.3 ms -13 ms   | select ...ifnull(...) as label, ifnull(...) as position, ifnull(...) as disabled, ..., ifnull(...) as video_provider, ifnull(...) as video_url, ifnull(...) as video_title, ifnull(...) as video_description, ifnull(...) as video_metadata, ... from catalog_product_entity_media_gallery as main join catalog_product_entity_media_gallery_value_to_entity as entity on main.value_id = entity.value_id left join catalog_product_entity_media_gallery_value as value on main.value_id = value.value_id and value.store_id = ? and value.entity_id = entity.entity_id left join catalog_product_entity_media_gallery_value as default_value on main.value_id = default_value.value_id and default_value.store_id = ? and default_value.entity_id = entity.entity_id left join catalog_product_entity_media_gallery_value_video as value_video on value.value_id = value_video.value_id and value.store_id = value_video.store_id left join catalog_product_entity_media_gallery_value_video as default_value_video on default_value.value_id = default_value_video.value_id and default_value.store_id = default_value_video.store_id where (main.attribute_id = ?) and (main.disabled = ?) and (entity.entity_id = ?) order by if(...) asc |
| 100x -100 | 8.35 ms -13 ms   | select ? from catalog_product_website where (product_id = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 100x -100 | 13.1 ms -12 ms   | select catalog_product_entity.* from catalog_product_entity where (entity_id = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 114x -15  | 15.6 ms -17 ms   | select stock_source_link.* from inventory_source_stock_link as stock_source_link join inventory_source_item on inventory_source_item.source_code = stock_source_link.source_code where (stock_source_link.stock_id = ?) and (inventory_source_item.sku = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 100x -100 | 15.5 ms -10 ms   | select ... from catalog_product_entity_tier_price where (website_id = ?) and (entity_id = ?) order by qty asc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 114x -44  | 20.7 ms -4.58 ms | select ... from inventory_stock_2 where (sku = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 42x -74   | 25.3 ms +14.3 ms | select sum(?) as quantity from inventory_reservation where (sku = ?) and (stock_id = ?) limit ?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 100x -100 | 37.6 ms +6.39 ms | SELECT COUNT(*) AS `count` FROM `inventory_source_stock_link` AS `issl` INNER JOIN `inventory_source` AS `is` ON issl.source_code = is.source_code INNER JOIN `inventory_source_item` AS `isi` ON issl.source_code = isi.source_code WHERE (issl.stock_id = ?) AND (is.enabled = ?) AND (isi.sku = ?) AND (isi.status = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 64x +10   | 17 ms +4.63 ms   | SELECT `u`.* FROM ( (SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_category_entity_varchar` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_category_entity_int` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_category_entity_text` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_category_entity_datetime` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_category_entity_decimal` AS `t` WHERE (entity_id = ?) AND (attribute_id IN (?)) AND (`store_id` IN (?))) ) AS `u` ORDER BY `store_id` ASC                                                                                                                                                                                                                                                     |
| 14x -44   | 8.35 ms +1.87 ms | SELECT SUM(isi.quantity) AS `quantity` FROM `inventory_source_stock_link` AS `issl` INNER JOIN `inventory_source` AS `is` ON issl.source_code = is.source_code INNER JOIN `inventory_source_item` AS `isi` ON issl.source_code = isi.source_code WHERE (issl.stock_id = ?) AND (is.enabled = ?) AND (isi.sku = ?) AND (isi.status = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |

[ ] Bugfix
[ ] Feature
[ ] Code style update (formatting, local variables)
[ ] Refactoring (no functional changes, no api changes)
[ ] Build related changes
[ ] CI related changes
[ ] Documentation content changes
[x] Performance optimization

What is the current behavior?

Fixes: #1

What is the new behavior?

Does this PR introduce a breaking change?

[ ] Yes
[x] No

Other information

🙏🏻 Kindly please add information to Release Notes "Optimized by SwiftOtter.com" when the change gets merged.

[lbajsarowicz]

@rhoerr The issue here is 2-fold, as "By Default" the Tokenbase Plugin is executed each time you load Sales Grid. The downside is that the same collection is applied for the Admin Dashboard order aggregation.

I'll investigate further in my personal time, as the issue turned out to be more complex than I anticipated. There's one thing I'm sure - The module requires optimization, as it significantly affects the Admin grids performance.

[rhoerr]

Thanks for calling out the dashboard aggregation as well. That would not have occurred to me.

For sure, the performance of the current plugin needs improvement. Thank you.

[rhoerr]

Resolved by 02a11b7c03a867e7bbd1926200d12646d712800e . I removed the plugin on Order\Collection, and moved the plugin from Order to Order\Payment such that the extension attributes are initialized on reference, but only after the payment object is otherwise loaded.

In testing, fetching 100 orders by REST API on slow hardware:

Before: 12.44s 11.03s 11.17s
After:   9.52s  9.21s  8.47s

So it definitely does help, albeit slightly. For very large collection loads the difference would be more substantial.

Thanks Lukasz