Slow search products with cost_price field and multicompany
Scenario:
- A database we have 6 companies
- A product.template has 145 product.product (variants)
When get cost_price from product, get ALL cost_price from ALL companies (total: 145 * 6 = 1000 product.product).
I think in this case, we need to filter by company and get only company records.
Current query:
SELECT "a"."create_uid" AS "create_uid", "a"."product" AS "product", "a"."create_date" AS "create_date", "a"."company" AS "company", "a"."write_uid" AS "write_uid", "a"."write_date" AS "write_date", "a"."id" AS "id", "a"."cost_price" AS "cost_price" FROM "product_cost_price" AS "a" WHERE ((("a"."id" >= 3) AND ("a"."id" <= 67)) OR (("a"."id" >= 1465) AND ("a"."id" <= 1529)) OR (("a"."id" >= 1769) AND ("a"."id" <= 1833)) OR (("a"."id" >= 2072) AND ("a"."id" <= 2136)) OR (("a"."id" >= 2155) AND ("a"."id" <= 2184)) OR (("a"."id" >= 2253) AND ("a"."id" <= 2372)) OR (("a"."id" >= 2375) AND ("a"."id" <= 2438)) OR (("a"."id" >= 2458) AND ("a"."id" <= 2514)) OR (("a"."id" >= 2529) AND ("a"."id" <= 2675)) OR (("a"."id" >= 2678) AND ("a"."id" <= 2741)) OR (("a"."id" >= 2791) AND ("a"."id" <= 2817)) OR (("a"."id" >= 2832) AND ("a"."id" <= 2978)) OR (("a"."id" >= 2980) AND ("a"."id" <= 3044)) OR (("a"."id" >= 6029) AND ("a"."id" <= 6041)) OR ("a"."id" IN (88, 89, 2455, 2758, 2759, 2760)))
Optimization query:
SELECT "a"."create_uid" AS "create_uid", "a"."product" AS "product", "a"."create_date" AS "create_date", "a"."company" AS "company", "a"."write_uid" AS "write_uid", "a"."write_date" AS "write_date", "a"."id" AS "id", "a"."cost_price" AS "cost_price" FROM "product_cost_price" AS "a" WHERE ((("a"."company" >= COMPANY_ID) AND ("a"."id" >= 3) AND ("a"."id" <= 67)) OR (("a"."id" >= 1465) AND ("a"."id" <= 1529)) OR (("a"."id" >= 1769) AND ("a"."id" <= 1833)) OR (("a"."id" >= 2072) AND ("a"."id" <= 2136)) OR (("a"."id" >= 2155) AND ("a"."id" <= 2184)) OR (("a"."id" >= 2253) AND ("a"."id" <= 2372)) OR (("a"."id" >= 2375) AND ("a"."id" <= 2438)) OR (("a"."id" >= 2458) AND ("a"."id" <= 2514)) OR (("a"."id" >= 2529) AND ("a"."id" <= 2675)) OR (("a"."id" >= 2678) AND ("a"."id" <= 2741)) OR (("a"."id" >= 2791) AND ("a"."id" <= 2817)) OR (("a"."id" >= 2832) AND ("a"."id" <= 2978)) OR (("a"."id" >= 2980) AND ("a"."id" <= 3044)) OR (("a"."id" >= 6029) AND ("a"."id" <= 6041)) OR ("a"."id" IN (88, 89, 2455, 2758, 2759, 2760)))
I think Value.search([]),in multivalue_records method [1] [2], we need to filter only records by company when has a company in context.
- Search 4091 products with cost_price field: more 30 minutes.
- Search 4091 products without cost_price field: < 2 minutes.
note:
Same as list_price product field because is multivalue field in product.template.
[1] https://bitbucket.org/tryton/company/src/18caacd04e17c5a05e541320522799f7cc060b8b/model.py#lines-11
[2] https://bitbucket.org/tryton/trytond/src/b97407494ce3d63c89631a3e3c7876f2fe536927/trytond/model/multivalue.py#lines-18