Community: Framework mailing list archives

expert-framework@mail.odoo.com

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

by
Leonardo Rochael Almeida
- 04/06/2016 16:28:06
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