Community: Framework mailing list archives

expert-framework@mail.odoo.com

Re: report models based on sql views and search_read performance issue

by
Ecosoft Co. LTD, Kitti Upariphutthiphong
- 04/09/2016 07:55:43

Ok!

Yes I am testing it and it works well, now no need to wait. ;)

But it will need the view to have a unique ID column, which is OK I think.

I think this module is so great, it make overall good user experiences.

Thanks,

PS: I found what may be a bug. https://github.com/petrus-v/server-tools/pull/2

On Apr 9, 2016 5:08 PM, "Sylvain LE GAL" <sylvain.legal@grap.coop> wrote:
Hi Kitti,

I don't use concurrently just because I don't know that feature ! And I currently don't need it. I just have a cron that refreshes the views, one time a night.

But it could be a good improvment ! Feel free to review anybox PR or make a PR.

Kind regards.


Sylvain LE GAL
Service informatique
GRAP - Groupement Régional Alimentaire de Proximité

3 Grande rue des Feuillants, 69001 Lyon
Bureau :
(+33) 09.72.32.33.17
Astreinte :
(+33) 06.81.85.61.43

GRAP sur le Web : Site Web | Facebook | Twitter
GRAP - service Informatique sur le Web : Twitter


2016-04-09 11:42 GMT+02:00 Kitti U. <kittiu@ecosoft.co.th>:
Hello Sylvain,

Thanks for you suggestion. It help make report run much faster.

Except during the time of REFRESH MATERIALIZED VIEW, which will lock the view during its execution.

Is it possible to use CONCURRENTLY together with the REFRESH?


As I test, it will require creating unique index on the material view.

Just curious why it is not being used. or it leads to other problem.

Kitti

On Thu, Apr 7, 2016 at 4:56 AM, Sylvain LE GAL <sylvain.legal@grap.coop> wrote:
Hi Alexandre,

If you look for sample with materialized views in odoo, I wrote a little module to display sale information when user has both canals ('sale' and 'point of sale'). As there are two models (pos.order and sale.order), 'union' is required, that generates very bad performance.

The module is here with a minimal abstract class to create many reports and functions to be called in a cron to refresh the view.
On my production database : ~ 200.000 Sale Order Lines + 1.000.000 PoS Order Line in multi company context.
- without materialized view : 50 / 60 sec
- with materialized view : 1 / 2 sec

I'm convinced that materialized view should be used in most of the report modules, because real time information is mostly unnecessary.

If you're interested by such features, please take a look on interesting pending V7 / V8 Pull requests realized by Anybox.


Regards.


Sylvain LE GAL
Service informatique
GRAP - Groupement Régional Alimentaire de Proximité

3 Grande rue des Feuillants, 69001 Lyon
Bureau :
(+33) 09.72.32.33.17
Astreinte :
(+33) 06.81.85.61.43

GRAP sur le Web : Site Web | Facebook | Twitter
GRAP - service Informatique sur le Web : Twitter


2016-04-06 23:12 GMT+02:00 Alexandre Fayolle <alexandre.fayolle@camptocamp.com>:
The view is a summary of various states and indicators for analytic accounts linked to departments over monthly periods. the current view is using max(analytic_account_id), with a

group by analytic_account.departement_id, date_trunc('month', analytic_account.create_date)::date

which is guaranteed to be unique, but is not useful for querying...

Someone else suggested materialized views (off list). I'll have to check with my customer if he is ok with non live data in this report (i.e. with a cron doing a periodic REFRESH)

2016-04-06 22:32 GMT+02:00 Leonardo Rochael Almeida <leorochael@gmail.com>:
Hi Alexandre,

Out of curiosity, what are you using for the id column, since you need aggregations?

Myself, I've been using "row_number() over ()" on views with aggregation, which I suppose would hit the same issues you mentioned, so probably not very helpful.

Ideally Odoo shoudn't even need to work with IDs in the case of report view. Instead it should assign row numbers itself as IDs on the python side, and use OFFSET and LIMIT to fetch partial results.

If you use a materialized view[1], you can then add indexes to your synthetic id column (in which case, even my "row_number() over ()" would be fast).


The issue with materialized views is that they get their data the moment they're created and are not updated unless you manually call "REFRESH MATERIALIZED VIEW yourview;"[2], which could be done from a trigger on the Postgres side or a cron or other automated action on the Oddo side.


One advantage of adding an index on id would be the ability to call "REFRESH MATERIALIZED VIEW CONCURRENTLY", which would allow the view to be queried while the refresh is ongoing.

Best regards,

Leo

On 6 April 2016 at 16:42, Alexandre Fayolle <alexandre.fayolle@camptocamp.com> wrote:
sorry....


In my current project, I have a report model class with _auto = False and which initializes an SQL view which is fairly complex. It worked fine with test data, but I'm hitting a performance limitation with my customers real data. After digging a bit, I found that the main issue is that the id column of the view is not indexed, and I have no easy way to map that column to an underlying indexed column which could help, because the view has a group by to perform some aggregations.

What happens is that search_read makes 2 SQL queries: first the search to find the ids first 80 rows of the table, this takes a couple of seconds, which is acceptable. Then the read makes a query with a where id in (list of ids) clause, which takes several minutes to run because the whole view needs to be processed as the domain from the search is no longer available.

I'm certainly not the first one to run into this, and I'm curious how people handle this.

Thanks for your insights.


2016-04-06 21:28 GMT+02:00 Alexandre Fayolle <alexandre.fayolle@camptocamp.com>:
Hello,

In my current project, I have a report model class with _auto = False and which initializes

--
Alexandre Fayolle
Chef de Projet
Tel : + 33 (0)4 58 48 20 30

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex
http://www.camptocamp.com




--
Alexandre Fayolle
Chef de Projet
Tel : + 33 (0)4 58 48 20 30

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex
http://www.camptocamp.com

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


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




--
Alexandre Fayolle
Chef de Projet
Tel : + 33 (0)4 58 48 20 30

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac Cedex
http://www.camptocamp.com

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


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




--
Mobile: +66-(0)8-1841-7480
Your ERP Partner => www.ecosoft.co.th

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


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