Tryton - Issues

 

Issue9080

Title Add code to template as variant prefix
Priority feature Status resolved
Superseder Nosy List ced, pokoli, reviewbot, roundup-bot
Type behavior Components product
Assigned To ced Keywords review
Reviews 293101002
View: 293101002

Created on 2020-02-21.14:44:25 by ced, last changed by roundup-bot.

Messages
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
review293101002 updated at https://codereview.tryton.org/293101002/#ps293141002
msg55512 (view) Author: [hidden] (pokoli) (Tryton committer) (Tryton translator) Date: 2020-02-21.18:31:31
OK, if we want to add a unique constraint on code it is worth materializing it.
msg55511 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2020-02-21.17:22:43
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.
msg55510 (view) Author: [hidden] (pokoli) (Tryton committer) (Tryton translator) Date: 2020-02-21.16:36:22
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.
review293101002 updated at https://codereview.tryton.org/293101002/#ps263031002
msg55508 (view) Author: [hidden] (ced) (Tryton committer) (Tryton translator) Date: 2020-02-21.14:44:24
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.
History
Date User Action Args
2020-03-19 00:29:10roundup-botsetmessages: + msg56385
2020-03-19 00:29:05roundup-botsetstatus: testing -> resolved
nosy: + roundup-bot
messages: + msg56384
2020-02-29 01:41:18reviewbotsetmessages: + msg55636
2020-02-21 18:31:32pokolisetmessages: + msg55512
2020-02-21 17:22:44cedsetmessages: + msg55511
2020-02-21 16:36:23pokolisetnosy: + pokoli
messages: + msg55510
2020-02-21 15:14:03reviewbotsetnosy: + reviewbot
messages: + msg55509
2020-02-21 14:51:56cedlinkissue9010 superseder
2020-02-21 14:51:45cedlinkissue9004 superseder
2020-02-21 14:49:42cedsetstatus: in-progress -> testing
reviews: 293101002
keyword: + review
2020-02-21 14:44:25cedcreate