Odoo ORM Performance Tips

One of the most valuable talks from Odoo Experience 2017 I've watched to date has been the ORM Performance: Optimizations & Best Practices talk by Raphael Collet.

I've learnt quite a few new things from this talk and so I highly recommend watching it or at least skimming through the slides (if you already feel quite knowledgeable regarding Odoo ORM).

Below is just a tl;dr rehash of new stuff I've picked up from the talk.

Profiling

Use pyflame or Odoo's line profiler (Odoo v11+), eg.:

from odoo.tools.profiler import profile

class MyModel(models.Model):

    @api.multi
    @profile
    def my_expensive_method(self):
        # ...

Prefetching

3 rules of prefetching

  1. When you call browse(), you start off with a prefetching where the only thing you prefetch is the current recordset (of that browse()).
  2. When you access a relational field (eg. you access a many-to-one), the values of the many-to-one on all of the prefetched records will be added to the prefetching as well.
  3. When you iterate over records, every record you get in the iteration shares the same prefetching information as its, somehow, parent records.

Reusing prefetch

For each browse() a new prefetch record is initiated, so fields will not be prefetched at once if you are doing multiple browse(), eg. in a loop.

Keeping the same prefetch when you are doing many browse():

prefetch = self.env['base']._prefetch

actions = []
for record in ...:
    record = self.env[model].browse(id).with_prefetch(prefetch)
    actions.append((name, record))

for name, record in actions:
    # First time you access `partner_id` on the first record, all records will be prefetched (and not one by one).
    if record.partner_id.name = 'Jackie':
        ...

Avoid too much prefetch

Slicing a recordset which is not prefetched yet limits the prefetch to the slice only - use slices or indexes when you want to prefetch only a single record (or a few) at a time.

If you don't want to prefetch all the fields (it may be very costly, eg. on product.product):

# This will prefetch many records, but only one field at a time (as you access the field).
records.with_context(prefetch_fields=False)

Computed fields

Stored computed fields

When you have a relational field's field (eg. partner_id.name) as a dependency of a stored computed field on your record (eg. foo), when the partner_id.name field values changes, then all the records of your model which use that partner_id need to have foo recomputed (This makes sense if you think about it, but you need to think about it). So think about it before adding too many such dependencies (which rely on a relational field's value) - the cost is not on the current model, it's on the other models.

Non-stored computed fields

As the sorting is done by PostgreSQL, one cannot sort by non-stored computed field.

Delayed recomputation

Delayed recomputation when doing many changes which would trigger recomputation:

with self.env.norecompute():
    for line in order.line_ids:
        # this only marks fields to recompute on records
        line.unit_price = line.unit_price + 1

# recompute all marked fields
self.recompute()

Mail stuff

If you have a lot of updates on records which model inherits from mail.thread, you may want to consider turning off value tracking:

records.with_context(mail_notrack=True)

or disable all mail thread features (autosubscription, value tracking, etc.) altogether:

records.with_context(tracking_disable=True)

Other

  • The cursor has a query counter (sql_log_count) number of queries executed up to this point.
  • In Odoo v11 there is a nifty performance testing utility based on the expected number of queries to be executed.
  • If you want to execute some PostgreSQL query and continue (even if it fails) and not have your whole transaction rollbacked (and current transaction unusable), you can do your work in a savepoint using the savepoint() context manager on the cursor, eg.:

    try:
    with self.env.cr.savepoint():
        # ...
    except psycopg2.Error:
    # Everything up-to the savepoint was rollbacked, we are good to continue.
    pass