SQL getter for Function fields

As shown in Should create_uid/write_uid use lazy loading? object instantiation can be an important part of the total execution time of some processes.

Further tests, show that the performance improvement of not instantiating Many2One objects also has a huge impact of simple data loading of the list of sales in some situations, due to the time taken to compute some function fields.

This made me think of how we can further improve the situation by preventing object instantiation in other circumstances.

Currently all models have a rec_name Function field which forces the instantiation of the object because it uses active record to load the data. This function field and others could benefit if we allowed a special kind of getter that returned a python-sql expression that could be executed by Postgres and returned without the need of calling the standard getter attribute.

It rings a bell that something in this line was already suggested, but couldn’t find it in bugs.tryton.org nor discuss.

It would look like this:

rec_name = fields.Function(fields.Char('Record Name'),
        sql_getter='get_sql_rec_name', searcher='search_rec_name')

@classmethod
def get_sql_rec_name(cls, name, table):
    rec_name = cls._rec_name
    if rec_name not in cls._fields:
        rec_name = 'id'
    return getattr(table, rec_name)

ModelStorage.read() would check if Function fields have an sql_getter implemented and add that python-sql code to the SQL query.

When sql_getter would be implemented, a default implementation could be used as searcher that used the python-sql expression in the where clause of search().

In the rec_name case, there would be a default implementation of existing ‘get_rec_name’ that would simply return the value as returned by the SQL query, so there would be no need to modify all existing modules and get_rec_name() would just work.

It seems to me that this could prevent the instantiation of thousands of objects in thousands of RPC requests. For example, in many list views we show m2o fields which show the rec_name of many records and in most cases get_rec_name simply returns the name field.

Thoughts?

I think you are talking about Issue 4369: New type of field for SQL expression - Tryton issue tracker.
For me, it should be a new type of field and not an extension of Function because it requires a different API. Indeed it is about having customer _sql_column etc.

Surely I don’t have enough knowledge of the API required for this, but the advantage I see with adding that functionality to Function fields is that it allows the first implementation of the field to be as fast as possible by using SQL, but still allows inheriting modules to make more complex expressions that cannot be covered by plain SQL.

Not with your proposal as you have two different attributes for the getter. So now override will be allowed that change the type of getter otherwise it breaks the modularity.