A common pattern in Tryton is to use
table_query to create a
Model for report/statistic purpose. This works very well and it is very flexible because the query can be customized by the context.
But due to the communication protocol between the client and the server, we need to have an
ID on every
ModelStorage. Most of the time, it is quite simple to generate one but sometime it is complex. In such case the result SQL query can not be very optimized by the database and it result of a query plan that compute the all tuples for the
table_query which are filtered after. This was exposed on the Odoo mailing list by Alexandre Fayolle.
Tryton has already the advantage to use a sub-query instead of view which in some way create a barrier that prevent the query planner to make some optimisation. But any way, if we have a complex
ID generation, a solution is to use materialized view with indexes (on back-end that support it).
We could have a
Mixin which will use the query from
table_query to create a materialized view (on supported back-end) and once the view is created it is returned by
table_query instead of the query.
Mixin should also have a method to refresh the view that could be called by a cron task, a wizard, a hook on other Models or even by the Model after a time-out. And it should also have an option to refresh concurrently or not.
Of course for back-end that doesn’t support materialized view, this should be disabled and the standard
table_query behaviour should be run.
Also the TableHandler should allow to create index on the materialized view so the
fields will be used (probably a test if
table_query returns a
Table or a
__register__ should recreate the view on each call to ensure the query is still synchronized.
A limitation is that such materialized view could not depend on the context.