[11.0][Poor Performance] when open a product with 5000 variantes

[fmdl]

Impacted versions:
11.0 and maybe other

Steps to reproduce:

• create a template with 5000 variantes
• go to the kanban view of template --> big wait (6 secondes)
• open the template --> big wait (8 secondes)

Why:

• There are lot of compute fields with no store (but there are depends).
• Some compute (like sale_count, ...), are not optimized for lot of variantes.

[Yenthe666]

@xmo-odoo and @nseinlet probably relevant for you two.

[nseinlet]

Log all postgreSQL queries and analyse them with pgBadger. Most sql queries must run in less than 100ms. Add indices if necessary fir your use case.

[fmdl]

Please find the --log-level=debug_sql report

Archive.zip

[nseinlet]

https://www.odoo.com/slides/slide/tips-to-fine-tune-postgresql-to-boost-performances-542
Slide 25

Activate the sql logs on PostgreSQL
Analyze them with pgBadger

[fmdl]

Hi @nseinlet @odony @jco-odoo

I have made some investigation on this issues :
I have trace the time to compute value of this methode : https://github.com/odoo/odoo/blob/11.0/odoo/fields.py#L989
print('_compute_value',self,time.time()-t)

And the worst compute are :

_compute_value product.product.uom_id 0.1459331512451172
_compute_value product.product.uom_id 0.1666262149810791
_compute_value product.product.uom_id 0.25386691093444824
_compute_value product.product.uom_id 0.12479710578918457
_compute_value product.template.qty_available 1.1551520824432373
_compute_value product.product.sales_count 0.08360910415649414
_compute_value product.product.sales_count 0.05408883094787598
_compute_value product.product.sales_count 0.055345773696899414
_compute_value product.product.sales_count 0.04101371765136719
_compute_value product.template.sales_count 0.2633779048919678
_compute_value product.product.purchase_count 0.04912996292114258
_compute_value product.product.purchase_count 0.04426431655883789
_compute_value product.product.purchase_count 0.04714798927307129
_compute_value product.product.purchase_count 0.0342557430267334
_compute_value product.template.purchase_count 0.20733904838562012
_compute_value product.product.display_name 0.48386216163635254
_compute_value product.template.product_variant_count 0.2789759635925293

Analyse:

• product.template.qty_available is long because there lot of read_group for the 4000 variantes, and after it is sum for the product.templates. Maybe rewrite and use [('product_id.product_tmpl_id','in',self.ids)] in the domain. Fix for product.product : [FIX][Performance] stock: fast compute if lot of variante #23111

• product.template.product_variant_count it just len(self.product_variant_ids). It can fix by a read_group, and add store=True : fix here [FIX][Performance] product: slow compute number of variant if lot of variante #23112

• product.product.display_name it call for prefech, but it is not useless.

• product.product.uom_id i don't why it is so long and 4 times. But if you replace product.uom_id.rounding by product.product_tmpl_id.uom_id.rounding, product.template.qty_available take only 190 ms, and there no more call.

• product.template.product_variant_id take 500 ms (crazy to just get an ID), because there a name_get of the first 1000 product. Solution use store=True

• product.product.purchase_count and product.product.purchase_count, rewrite a read_group with product_tpl_id, same issue with product.template.qty_available. Fast compute of purchase : [FIX] purchase: Fast compute of purchase count for product #23101

Bug performance : useless compute of display_name
For product.product and product.template the compute of display name is complexe, that why, it useless to compute where we don't need.
When we access to a field many2one or one2many, or use read_group, it compute name_get here : -

• https://github.com/odoo/odoo/blob/11.0/odoo/fields.py#L1990
• https://github.com/odoo/odoo/blob/11.0/odoo/models.py#L1944
  , it is useless to make calculation after.

This explain why the creation of variante is so long when there are lot of existing variante : #23108

[odony]

• product.product.uom_id i don't why it is so long and 4 times. But if you replace product.uom_id.rounding by product.product_tmpl_id.uom_id.rounding, product.template.qty_available take only 190 ms, and there no more call.

For the record, this one might be solved by 10412d3. At least it's worth a re-test on an up-to-date 11.0.

[odony]

This perf investigation is interesting, but I don't think having 5000 variants for the same product makes any kind of sense, so please don't expect the system to behave nominally. Variants were simply not designed to be used for this, and won't be.

Imagine a shirt store with only one product template, and variants for every possible design, size and color combination. Clearly, each design should be a different product, and variants based only on size/color.

Maybe a rule of thumb would be to think of an e-commerce. Visitors are expected to find the basic product by navigating the store, and when they reach it, then they should select the variant through a simple UI. They cannot be expected to differentiate through thousands of variants.

If a product attribute has more than a few values (e.g. arbitrary dimensions), it should be configure NOT to create variants - that should be a make-to-order attribute.

[fmdl]

@odony for product.product.uom_id I allready use the latest code.

Sure a product with 5000 variants is not commun, but 50 product with 100 variants each is more likely, and the time to compute will be the same to show the kanban view.

This extreme case reveals that some stuff are not optimized.

[fmdl]

@alnslang

[sum1201]

@pedrobaeza @fmdl
Please See https://youtu.be/h95loQXS_8w

The point of the problem is not the number of product variants, but the fact that I only look at one of the variants in the Form View, and why I also calculate the properties of the other variants.

[moylop260]

@sum1201
You are using another language in your video and it doesn't have comments about what you are doing for each step.
Could you try to create a video in English and explain it, please?

[sum1201]

@moylop260
please See : https://youtu.be/tuoWjZPmHvg

[sum1201]

@Yenthe666 @xmo-odoo and @nseinlet
Why is there no one to continue discussing?
Is it put on hold?

[Yenthe666]

@sum1201 I'm just a contributor so I cannot do much more than look, label, ping the right people and try to help in general. This one has to be handled by somebody at Odoo itself. If you have an enterprise license you can also report it through odoo.com/help but there is nothing more I can do on this specific ticket, sorry!

[nseinlet]

@sum1201 Open a ticket through odoo.com/help with the enterprise contract, and I'll look at it more deeper.

[sum1201]

@nseinlet I have open a ticket : 1865111
But the reply is really slow..

[sswapnesh]

👋 Any new updates here?

[rogie69]

Hi,
Has there been any progress on this problem, we manufacture lighting, so our products are a variation on a theme with lots of dimensions ... ie.
Colour temperature - 5 options
Beam angle - 10 options
Dimming - 8 options
emergency - 2 options
glare reduction - 2 options
Colour filters - 6 options
and that is without the 72 different variations for the light fitting, so we will have 20,000 - 40,000 variants per product and probably 10 products in a range ....
our DB is getting slow but we cant think of another way to present our products well to our customers.