bi_sql_editor: have non materialized views by default

[gurneyalex]

I think having bi_sql_view generate materialized views by default is not the best setting, and I would like to have the default value for this field to be False

The reasons behind this:

• refreshing a materialized view has a cost, especially on the database server (there are some locks which are taken during the refresh, with ACCESS EXCLUSIVE level, and these block SELECT queries, which is not nice at all)
• I see views created by non technical users with the refresh cron set to run every 5 min 😑

By having the default set to "non materialized view" we get a "real" action to setup the refresh machinery,

What we could have as an improvement in the sql view workflow is a check of the time needed to get the data of the view. If that time is greater than a threshold (2s for instance), then maybe a suggestion to switch to a materialized view, explaining the tradeoffs could be displayed.

Any opinion on this?

[gurneyalex]

@legalsylvain as maintainer of the module, your feedback is precious 💚

[legalsylvain]

Hi @gurneyalex. Thanks for your question.

I have not a clear point of view here.

• I developped that module because non materialized views was too slow in my -instance. At the time (version 7/8), when the request was too long, this could cause performance problems on the server.
• here, you say that having materialized view can generate also problem. Your argument make sense.
• I never gave access to such module to "non technical users", only admin users have access this module in my use case (I'm not sure to understand how a non technical user can write SQL valid queries ;-), but anyway)

In an UX point of view, what about to add an extra text to explain in each case (materialized or not) what are the consequences (good and bad) ?
Something like in product form :

What we could have as an improvement in the sql view workflow is a check of the time needed to get the data of the view. If that time is greater than a threshold (2s for instance), then maybe a suggestion to switch to a materialized view, explaining the tradeoffs could be displayed.

Interesting. I'd like to avoid complexity. Maybe we can get (and store) the duration of the last execution of this function : https://github.com/OCA/reporting-engine/blob/16.0/sql_request_abstract/models/sql_request_mixin.py#L280-L295

What do you think ?

[gurneyalex]

Thanks @legalsylvain

Going for a clearer explanation: yes
I also like the suggestion about the timing recording.

Regarding the "how can non tech user produce valid SQL queries" question, it seems that some consultants around here give (some selected users) them the proper access rights and possibly the bi_sql_editor module can help navigate in the schema. Among our customers, we also have some techies who are able to do smart stuff and not always go through the integrator, so we find out when something hits the fan.

I will propose a PR to improve the things according to this discussion, likely when I'm back from holidays in 2 weeks. Or maybe someone else at camptocamp will beat me to it...