stock location content report not optimized

[Ian Li]

We are using OpenERP 6.0.3 with WEB-UI, we have roughly 15,000 products and 250 internal locations.
Every time we trying to generate report of content of one or some locations, the server will consuming a lot of memory ( around 1-1.5Giga), and some time we got timeout error of web-ui.

After a little investigation, we think it is because in _product_get_report() of stock.py, if product_ids is not provided, it will indiscriminately get all the product ids:
   if not product_ids:
            product_ids = product_obj.search(cr, uid, [], context={'active_test': False})

We think it may get related product ids with the location ids given first, will it be feasible?

[Ferdinand (office-chricar)]

Just 2 questions:

1) You are talking about
Menu > Warehouse > Inventory Control > Location Structure ->> Wizard "Stock Location Analysis" ?

2) Do you want to display products with both "real stock" =0 and "future stock" = 0 as I assume not all 15000 will be in every stock location?

[Eric Caudal - www.elico-corp.com (elicoidal)]

Hi Ferdinand,
1.we are talking about the reports in warehouse/configuration/location
"location inventory Overview" and "location content".
In those 2 reports, the above mentioned function gets all the products
and then calculate one by one all stocks for all locations (we have more
than 250 locations).
Ideally OpenERP should first select the products that are in a move
from/to the given location(s) and then calculate the stock for the
location(s).
2. this point is as well a p... in the a...: with 15000 products of
which 800 are not null at a given moment, getting a stock list is a
challenge in OpenERP. Having the possibility to filter (and sort!) the
products with real and/or virtual stock would be great.

openerp

*Eric CAUDAL*, CEO
<email address hidden> <mailto:<email address hidden>>
Cell: + 86 186 2136 1670
*Elico Corp - OpenERP Ready Partner, Shanghai.*
http://www.openerp.net.cn

[Ferdinand (office-chricar)]

Hello Eric!

you may want to have a look into
http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/stock_location_product_not_null/

which requires our patched server version which allows server side filtering of function fields from
https://code.launchpad.net/~c2c/openobject-server/6.0-c2c-official

indeed we would be interested to hear if this works well (or not) for these very big amount of data.

obviously, if stock quantities have to be calculated for every request it's a burden and the only way out is to create materialized views (aggregates) and/or other workarounds

we are experimenting with account period sums
http://bazaar.launchpad.net/~c2c/c2c-rd-addons/trunk/files/head:/chricar_account_period_sum/
to avoid similar problems
this module uses postgres triggers to create and update period sums and worked well in a similar installation on another ERP-DB system for 1 million moves a day. it does not need any python code modifications hence it is quite independent and very fast.
I am very sure that OpenERP will need a similar solution for stock_move soon.

[Ferdinand (office-chricar)]

Hello Eric!
please read also this
http://www.camptocamp.com/en/blog/2011/10/sort-and-filter-options-for-function-fields-in-openerp/

[Eric Caudal - www.elico-corp.com (elicoidal)]

Thanks Ferdinand,
We will have a close look, test it for our installation and feedback asap.

It helps but does not solve original problem which is the "location
content" calculation is not optimized.

openerp

*Eric CAUDAL*, CEO
<email address hidden> <mailto:<email address hidden>>
Cell: + 86 186 2136 1670
*Elico Corp - OpenERP Ready Partner, Shanghai.*
http://www.openerp.net.cn

[Jignesh Rathod(OpenERP) (jir-openerp)]

Hello Ian Le,

I have checked this issue at my end but I did not face any problem. I have created a 100 +
products and assign in to same location, then I have printed a "Stock location content"
report and its working fine.

I have attached a video for your more reference, So would you please check it and notify us where you have faced the problem.
That is better if you can provide us your end database so we will check it properly.

Thanks and waiting for replay.

[Eric Caudal - www.elico-corp.com (elicoidal)]

This bug is a bug (we have 4 millions combinations not comparable with
100) and have been sorted by OPW with following patch.
Eric

[Rifakat Husen (OpenERP) (rha-openerp)]

Hello,

I have improved my patch from comment #8 and created another patch
to solve this problem.

Eric, could you please test this patch if it improves performance of your report?

Thanks,
Rifakat

[Naresh(OpenERP) (nch-openerp)]

A tentative patch was provided to the customer and also on the report. As this was not asked explicitly to be merged in the stable I am closing this bug report. However people can use the patch provided in comment#9 by Rifakat.

Thanks,
Naresh

[Eric Caudal - www.elico-corp.com (elicoidal)]

Hi,
It works fine like this.
Thanks
Eric CAUDAL
openerp

*Eric CAUDAL*, Elico Corp, Shanghai.
<email address hidden> <mailto:<email address hidden>>
Cell: + 86 186 2136 1670. Skype: elico.corp
*Premium Certified Training Partner - OpenERP Ready Partner.*

Premium CTP
http://www.openerp.net.cn