[FIX] mrp: optimize quantities computation

Previous implementation makes O(n) sql queries, which leads to performance
issues in products with many variants

Performance test
================

Test for product template with 400 variants

```
product_template.read(['qty_available'])

|        | Number of queries | Query time, sec | Remaining time, sec |
|--------+-------------------+-----------------+---------------------|
| Before |               413 |           0.306 |               0.357 |
| After  |                14 |           0.029 |               0.086 |
```

---

opw-2480303

closes odoo/odoo#68930

X-original-commit: 5e00b8b77eef7518f93291d85d30b0a1f21f74e1
Signed-off-by: Rémy Voet <ryv-odoo@users.noreply.github.com>
Signed-off-by: Ivan Yelizariev // IEL <yelizariev@users.noreply.github.com>

2 files changed, 32 insertions, 6 deletions

diff --git a/addons/mrp/models/mrp_bom.py b/addons/mrp/models/mrp_bom.py
index e141698a6a9..128f8532aae 100644
--- a/addons/mrp/models/mrp_bom.py
+++ b/addons/mrp/models/mrp_bom.py
@@ -186,6 +186,37 @@ class MrpBom(models.Model):
             return self.env['mrp.bom']
         return self.search(domain, order='sequence, product_id', limit=1)
 
+    @api.model
+    def _get_product2bom(self, products, bom_type=False):
+        """Optimized variant of _bom_find to work with recordset"""
+        products = products.filtered(lambda product: product.type != 'service')
+        if not products:
+            return {}
+        product_templates = products.mapped('product_tmpl_id')
+        domain = ['|', ('product_id', 'in', products.ids), '&', ('product_id', '=', False), ('product_tmpl_id', 'in', product_templates.ids)]
+        if self.env.context.get('company_id'):
+            domain = domain + ['|', ('company_id', '=', False), ('company_id', '=', self.env.context.get('company_id'))]
+        if bom_type:
+            domain += [('type', '=', bom_type)]
+
+        boms = self.search(domain, order='sequence, product_id')
+        template2bom = {}
+        variant2bom = {}
+        for bom in boms:
+            # Use "setdefault" to take only first bom if we have few ones for
+            # the same product
+            if bom.product_id:
+                variant2bom.setdefault(bom.product_id, bom)
+            else:
+                template2bom.setdefault(bom.product_tmpl_id, bom)
+
+        result = {}
+        for p in products:
+            bom = variant2bom.get(p) or template2bom.get(p.product_tmpl_id)
+            if bom:
+                result[p] = bom
+        return result
+
     def explode(self, product, quantity, picking_type=False):
         """
             Explodes the BoM and creates two lists with all the information you need: bom_done and line_done
diff --git a/addons/mrp/models/product.py b/addons/mrp/models/product.py
index d7a5c12f496..400ca6b017e 100644
--- a/addons/mrp/models/product.py
+++ b/addons/mrp/models/product.py
@@ -124,12 +124,7 @@ class ProductProduct(models.Model):
         This override is used to get the correct quantities of products
         with 'phantom' as BoM type.
         """
-        bom_kits = {
-            product: bom
-            for product in self
-            for bom in (self.env['mrp.bom']._bom_find(product=product, bom_type='phantom'),)
-            if bom
-        }
+        bom_kits = self.env['mrp.bom']._get_product2bom(self, bom_type='phantom')
         kits = self.filtered(lambda p: bom_kits.get(p))
         res = super(ProductProduct, self - kits)._compute_quantities_dict(lot_id, owner_id, package_id, from_date=from_date, to_date=to_date)
         for product in bom_kits: