[2.2.4][Performance][Listing][CE/EE] Too much product collection loads

[IgorVitol]

Preconditions

1. Magento 2.2.4 Commerce/Community (same for previous 2.2.x versions, at least tested on 2.2.3)

Steps to reproduce

1. Install clean Magento 2.2.3+ Community/Commerce with sample data
2. Disable Full Page Cache
3. Add logging to one of these places (this will support repositories & direct collection calls) :

• Magento\Catalog\Model\ResourceModel\Product\Collection::_afterLoad()
• Observe catalog_product_collection_load_after event.

It can be very simple & dirty logging, since our objective here is only to fetch amount of collection loads:

\Magento\Framework\App\ObjectManager::getInstance()->get(\Psr\Log\LoggerInterface::class)->critical('product collection loaded!');

4. Visit any listing page which contain configurable products. For example:

/men/tops-men/tees-men.html

Expected result

1. 30 configurable products on the page = 1 line in the log (1 product collection load).
2. 15 configurable products on the page = 1 line in the log (1 product collection load).
3. 9 configurable products on the page = 1 line in the log (1 product collection load).

Actual result

1. 30 configurable products on the page = 31 lines in the log (31 product collection loads).
2. 15 configurable products on the page = 16 lines in the log (16 product collection loads).
3. 9 configurable products on the page = 10 lines in the log (10 product collection loads).

After some investigation, I noted that here is specific method, which loaded child product collection for each separate configurable product on the listing page:

Magento\ConfigurableProduct\Pricing\Price\LowestPriceOptionsProvider::getProducts(ProductInterface $product)
It's code - http://prntscr.com/jdsxq8

This method called from next places:

1. Magento\ConfigurableProduct\Pricing\Render\FinalPriceBox::hasSpecialPrice
2. Magento\ConfigurableProduct\Pricing\Price\ConfigurablePriceResolver::resolvePrice
3. Magento\ConfigurableProduct\Plugin\Catalog\Model\Product\Pricing\Renderer\SalableResolver::afterIsSalable

This looks strange, because first two places seems like already covered by price-index (?), and last one (afterIsSalable) just checks if here is some children available - this definitely can be handled in backend (e.g. we can index such things, disable/move to our/of stock parent if all children are not available, e.t.c.).

In result, here should be only 1 collection call, which will improve general listing page load performance. Especially if you plan to display many products on the page.

[thiagolima-bm]

@IgorVitol are you going to create a PR for this?

[IgorVitol]

@thiagolima-bm Hi! For now I have no free time to make such changes. So I decided at least to report this here.

[ghost]

@IgorVitol, thank you for your report.
We've acknowledged the issue and added to our backlog.

[pmclain]

It looks like this has already been fixed 59c0e31#diff-e737458adb2a6eb7bd565d7bf7d9d91aR47

[RicardsRikmanis]

Magento\ConfigurableProduct\Plugin\Catalog\Model\Product\Pricing\Renderer\SalableResolver::afterIsSalable has been fixed.

Magento\ConfigurableProduct\Pricing\Price\ConfigurablePriceResolver::resolvePrice and Magento\ConfigurableProduct\Pricing\Render\FinalPriceBox::hasSpecialPrice still calls LowestPriceOptionsProvider which results in excessive product collection calls in categories that contain configurable products.

It would make sense to to resolve the pricing on in the collection itself since it already contains simple products.

[IgorVitol]

@magento give me 2.3-develop instance

[magento-engcom-team]

Hi @IgorVitol. Thank you for your request. I'm working on Magento 2.3-develop instance for you

[magento-engcom-team]

Hi @IgorVitol, here is your Magento instance.
Admin access: https://i-15001-2-3-develop.instances.magento-community.engineering/admin
Login: admin Password: 123123q
Instance will be terminated in up to 3 hours.

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.magento.com/browse/AC-1024 is successfully created for this GitHub issue.

[m2-assistant]

✅ Confirmed by @engcom-Hotel. Thank you for verifying the issue.
Issue Available: @engcom-Hotel, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.