Community mailing list archives

community@mail.odoo.com

RE: POS v8 start time

by
kaizen__
- 12/30/2014 06:27:38

The large blind WHERE product_template.id IN (…) list also seems to be taking up to 11seconds.

I wonder if a WHERE product_template.id BETWEEN 12234 AND 12433 would be more efficient

 

Would it be possible for you to do a quick test against your DB for these two queries?

 

I’m intrigued that the IDs in the IN aren’t entirely sequentially ordered.  I assume there is a non-ID sort order coming into the original list.

 

The other item to look at might be the auto_join attribute for the relational fields in product.product to the product.template table.

https://doc.odoo.com/trunk/server/06_misc_auto_join/

 

I guess if this would work appropriately, then it might allow the entire thing to be reduced down to use a JOIN instead of the individual SELECT from product_template queries.

 

 

 

Regards,

Bevan

 

From: alexis [mailto:alexis@via.ecp.fr]
Sent: Tuesday, 30 December 2014 9:17 PM
To: Community
Subject: POS v8 start time

 

Dear OpenERP community friends,

 

I am about to deploy in production a POS on v8 with 10 000 products and 

58 000 partners. With the current codebase, the POS takes 1 min 15 sec 

to start on an Ubuntu 14.04 LTS with Core i7 3.4 Ghz and 8 Gb of RAM.

 

Following the IRC meeting on POS on December 19, I have been 

investigating the POS start time. Frederic Van Der Essen suggested that 

a lot of time was spent in the pricelist, and that I should not 

configure a pricelist on the pos.config (it would then use the Sale 

price of the product), which is in fact not possible on v8 because the 

pricelist is a required field on pos.config.

 

I made some measures, and I found out that it only spends 4 seconds (out 

of 1 min 15s) to compute the pricelists, so the pricelist code is really 

optimized now. I made a patch to by-pass the pricelists (4 lignes of 

code) and I only win the 4 seconds, not more.

 

Then, I set :

log_statement = 'all'

in /etc/postgresql/9.3/main/postgresql.conf and looked at the SQL 

requests in /var/log/postgresql/postgresql-9.3-main.log

 

By looking at the SQL requests, I first figured out there was a LOT of 

SQL requests on ir_translation for product.product, even when my user 

has lang=English. As my customer only uses French, I developped a small 

module "product_no_translation" (cf 

https://github.com/akretion/odoo-usability/tree/8.0/product_no_translation) 

that sets translate=False on the fields of product.product + 

product.template, and I saved 10 seconds on the start time -> 1 min 5 

sec.

 

Now, when I activate firebug in Firefox to get the exact load time of 

each function, I get:

22 sec for the search_read of res.partner

42 sec for the search_read of product.product

 

When I look at the SQL requests when loading products, I found out that 

it first starts to load each field of product.product/product.template 

that are declared in odoo/addons/point_of_sale/static/src/js/models.js 

line 270, and that's quite fast because each SELECT request loads a high 

number of IDS, for example:

 

2014-12-30 09:11:25 CET LOG:  statement:  SELECT 

product_template."list_price",product_template."id" FROM 

"product_template"

                    WHERE product_template.id IN (12288, 12289, 12290, 

12291, 12292, 12293, 12294, 12295, 12296, 12297, 12298, 12299, 12300, 

12301, 12302, 12303, 12304, 12305, 12306, 12307, 12308, 12309, 12310, 

12311, 12312, 12313, 12314, 12315, 12316, 12317, 12318, 12319, 12320, 

12321, 12322, 12323, 12324, 12325, 12326, 12327, 12328, 12329, 12330, 

12331, 12332, 12333, 12334, 12335, 12336, 12337, 12338, 12339, 12340, 

12341, 12342, 12343, 12344, 12345, 12346, 12347, 12348, 12349, 12350, 

12351, 12352, 12353, 12354, 12355, 12356, 12357, 12358, 12359, 12360, 

12361, 12362, 12363, 12364, 12365, 12366, 12367, 12368, 12369, 12370, 

12371, 12372, 12373, 12374, 12375, 12376, 12377, 12378, 12379, 12380, 

12381, 12382, 12383, 12384, 12385, 12386, 12387, 12388, 12389, 12390, 

12391, 12392, 12393, 12394, 12395, 12396, 12397, 12398, 12399, 12400, 

12401, 12402, 12403, 12404, 12405, 12406, 12407, 12408, 12409, 12410, 

12411, 12412, 12413, 12414, 12415, 12416, 12417, 12418, 12419, 12420, 

12421, 12422, 12423, 12424, 12425, 12426, 12427, 12428, 12429, 12430, 

12431, 12432, 12433, 12234, 12235, 12236, 12237, 12238, 12239, 12240, 

12241, 12242, 12243, 12244, 12245, 12246, 12247, 12248, 12249, 12250, 

12251, 12252, 12253, 12254, 12255, 12256, 12257, 12258, 12259, 12260, 

12261, 12262, 12263, 12264, 12265, 12266, 12267, 12268, 12269, 12270, 

12271, 12272, 12273, 12274, 12275, 12276, 12277, 12278, 12279, 12280, 

12281, 12282, 12283, 12284, 12285, 12286, 12287) AND 

((("product_template"."company_id" = 17)  OR  

"product_template"."company_id" IS NULL ))

                    ORDER BY name

which seems OK. But, after that

 

 

BUT, after those high-volume SELECT requests, I have one SELECT request 

for each product.template that loads all the fields of the object 

product.template. As those SELECT requests only get 1 template by 1 

template, it takes 21 seconds to do that on my 9200 product templates 

available in POS ! Here is the SELECT request that is repeated 9200 

times:

 

 

2014-12-30 09:11:36 CET LOG:  statement:  SELECT 

product_template."warranty",product_template."track_all",product_template."uos_id",product_template."exclude_from_intrastat",product_template."mo_price",product_template."track_production",product_template."color",product_template."message_last_post",product_template."write_uid",product_template."mes_type",product_template."uom_id",product_template."mass_request_type_id",product_template."description_purchase",product_template."list_price",product_template."expense_pdt",product_template."id",product_template."income_pdt",product_template."uos_coeff",product_template."create_uid",product_template."weight",product_template."purchase_ok",product_template."product_manager",product_template."track_outgoing",product_template."company_id",product_template."name",product_template."state",product_template."loc_rack",product_template."origin_country_id",product_template."uom_po_id",product_template."manufacturer_id",product_template."type",product_template."track_incoming",product_template."is

_accessory_cost",product_template."description",product_template."must_print_barcode",product_template."weight_net",product_template."volume",product_template."tax_receipt_ok",product_template."donation",product_template."write_date",product_template."description_sale",product_template."active",product_template."collection_id",product_template."book_more_info",product_template."loc_row",product_template."intrastat_id",product_template."sale_ok",product_template."rental",product_template."sale_delay",product_template."loc_case",product_template."produce_delay",product_template."available_in_pos",product_template."categ_id",product_template."table_of_contents",product_template."pos_categ_id",product_template."create_date",product_template."mass",product_template."to_weight",product_template."author_label" 

FROM "product_template"

                     WHERE product_template.id IN (9754) AND (TRUE)

                     ORDER BY name

 

So, if we find a way (in the ORM ? in the code ?) to avoid those SELECT 

request on each product_template, we would reduce the POS start time by 

30% !

 

Eventually, I managed to reduce POS start time by a few seconds by not 

loading a few unused fields :

- I removed "state_id" on res.partner (models.js line 171), which 

reduced start time by a few seconds, because it avoids a lot of requets 

on ir_translation for the name of the state.

- I removed 'list_price', 'uos_id', 'uos_coeff', 'mes_type' for 

product.product (models.js line 270) because they are not used in the JS 

code of POS, and I earned a few more seconds.

 

With these changes, the start time of POS is now 1 min 0 sec. If you 

have other experiences on POS start time on v8 and other ideas to reduce 

it, don't hesitate to share them here !

 

Regards,

 

Alexis de Lattre

_______________________________________________
Mailing-List: https://www.odoo.com/groups/community-59
Post to: mailto:community@mail.odoo.com
Unsubscribe: https://www.odoo.com/groups?unsubscribe