Created on 2020-02-21.14:44:25 by ced, last changed 15 months ago by roundup-bot.
New changeset 5bc5d9aaf5c8 by Cédric Krier in branch 'default': Add code to template as variant prefix code https://hg.tryton.org/tryton-env/rev/5bc5d9aaf5c8
New changeset 88f15ac40dbe by Cédric Krier in branch 'default': Add code to template as variant prefix code https://hg.tryton.org/modules/product/rev/88f15ac40dbe
OK, if we want to add a unique constraint on code it is worth materializing it.
Indeed I said that (everyone has contradiction) but there we were talking about synchronizing all the template field to the product. Here it is only one code. Now about the benchmark. I find that above 20ms such simple query should be considered as slow (usually we configure logging at this threshold). So for me, all your queries are too slow for production use case. If you create the proper index: create index product_product_code_index ON product_product(code text_pattern_ops); The code search become really fast. I have a factor of 1000 between both queries and with '=' operator it is 10000. You got similar result with the standard index and '=' or 'in' operator which will be used more often in the web shop context. And if you increase the factor of variant, it become worst. I get a factor of 10 with your benchmark with 100000 template and 50x variant. And I got a factor of 10000 with the proper index. So of course it will always be faster if we get a proper index. But the materialized view will not be easy to implement and will still require a join which will be performance killing if the query has large result (or clause that can not be injected to the materialized view subquery). Another point is the ability to create a unique constraint on product code, that's why I linked the issue9004. In conclusion, I think it worth the data duplication in this case considering that product and template code are fields that will be very rarely changed.
Why not using the solution that uses the Function field? We always try to avoid data syncronization. See some comments about it: https://discuss.tryton.org/t/merge-products-variants-into-one-database-table-one-view-one-list/2241/10 I've done some tests with on my laptop. Generate products and 5 variants for each: insert into product_template (active, code, name, type, default_uom) select true, code::varchar, Concat('Product', code), 'goods', 1 from generate_series(1, 100000) as code; insert into product_product (template, active, suffix_code, code) select id, true, num, CONCAT(code, num) from product_template inner join generate_series(1,5) as num on true; Select a random product with join and without it: select p.id from product_template t inner join product_product p on p.template = t.id where concat(coalesce(t.code, ''), coalesce(p.suffix_code)) like '55534%' select id from product_product where code like '55534%'; with 10.000 products and 50.000 variants: SELECT WITH JOIN Execution Time: 50.102 ms SELECT WITH CODE Execution Time: 18.457 ms with 100.000 products, 500.000 variants: SELECT WITH JOIN Execution Time: 260.993 ms SELECT WITH CODE Execution Time: 81.735 ms With 1M products and 5M variants: SELECT WITH JOIN Execution Time: 1861.759 ms SELECT WITH CODE Execution Time: 246.477 ms The searcher starts to become slower when the number of products is bigger. I do not see a big deal for setups managing 5M of variants to materialize the colum and override the function searcher to use the materialized column.
Following https://discuss.tryton.org/t/sql-query-to-compute-sku-for-template/2352 Having a code on template is useful to get a SKU for web shop that display variant as a single product (see issue9010). My proposal is to add a prefix_code on template and a suffix_code on variant and to keep up to date the field code with the concatenation of both codes. This design allow to have both codes stored in the database in the proper table which can have an index for searching. Another option would have been to use a Function field that concatenate both codes but this means that we need to join the both table when doing a search which may not be very efficient with a lot of variants.
|2020-03-19 00:29:10||roundup-bot||set||messages: + msg56385|
|2020-03-19 00:29:05||roundup-bot||set||status: testing -> resolved|
nosy: + roundup-bot
messages: + msg56384
|2020-02-29 01:41:18||reviewbot||set||messages: + msg55636|
|2020-02-21 18:31:32||pokoli||set||messages: + msg55512|
|2020-02-21 17:22:44||ced||set||messages: + msg55511|
messages: + msg55510
messages: + msg55509
|2020-02-21 14:51:56||ced||link||issue9010 superseder|
|2020-02-21 14:51:45||ced||link||issue9004 superseder|
|2020-02-21 14:49:42||ced||set||status: in-progress -> testing|
keyword: + review