Excessive SQL Queries for configureable products.

[pynej]

Steps to reproduce

Running Magneto 2.0.7, PHP 5.6, Mysql 5.6

• Wehave tested with Use Flat Catalog on and off. These results were with it on.
• Create a single category with a few products and multiple dimensions on them. We have 3 products with:
  • 15 colors, 5 sizes, and 3 embroidery locations.
  • This come out to around 700 configurations.
• Go to the category in the fronted.

Expected result

• A functional and speedy site.

Actual result

• Cripplingly slow category and product pages on any configurable products with more then a few versions when request not hitting a FPC.

My testing shows that loading the category page with 3 products and 676 configurations results in 6,914 SQL queries to be run. In addition going to the product details page for a product with 285 configurations triggered 4,816 SQL queries. I understand that this is not a one to one thing and that it is always going to be more querying to build the configurable product, but this is unusable for configureable products with moe then a few options. So many queries should not be needed to enumerate and handle the configurations.

In comparison a category page with 9 non-configurable items only runs 150 queries and it's details page 225 queries. This is much more reasonable and leads to a quick compile.

Granted that the configureable items will run more queries, as they need to check image, stock, etc for the variations, it should not be 30 times as many queries as a basic product as it destroys the performance.

**Note, We are aware of the FPC options and we use them, but in many sites there may be frequent content changes that invalidate the FPC and the end users end up waiting for the pages to rebuild on their next visit leading to poor performance. We are ending up with random page hangs for up to 20 seconds while these pages rebuild.

sql queries.zip

[okobchenko]

Hi, Pynej.

Thank you for reporting this issue. M2 Engineering team has already detected performance bottlenecks connected with high number of configurable options in product. Fix is already available in 2.1 release candidates and will be present in 2.1 GA version. Currently team is working on delivering fix for 2.0 within one of upcoming patch releases.

[pynej]

Seams to be fixed in 2.1

[ajaymscit]

Hello Team,

I am also facing same kind of problem.

I am running Magento 2, having 900 Configurable products ( 113 associated products with each configurable product). Total 1,01,700 products in current store.

Magento2 speed is too slow. We are loading 40-60 configurable products.

We tried to enable varnish with it but most of the time it gives 503 error and speed is not changed too much.

Any solution for getting better performance?

[pynej]

On 2.1 its a lot better. We still use varnish to brink 4-5s loads down to 2s. (We were at 20s before the patches)