Odoo Performance and Caching – Optimizing Backend Code

Odoo Performance: Kill the N+1 Query

The #1 Odoo performance killer is the N+1 query: looping over records and touching a related field each iteration, triggering one query per record. Odoo's ORM is built to batch — work with recordsets, not against them.

# ❌ N+1: one query per order to fetch its partner
for order in orders:
    print(order.partner_id.name)   # each access may hit the DB

# ✅ prefetch: read the field for ALL records in one query first
orders.mapped("partner_id.name")   # single batched query

Odoo automatically prefetches fields for a whole recordset when you access one — so iterating a recordset is fine, but breaking that batching (e.g. browse-ing ids one at a time in a loop) reintroduces N+1.

Other levers: use read_group to aggregate in SQL rather than pulling rows into Python; add index=True to fields you filter on constantly; mark a computed field store=True only if you search/sort by it (otherwise it recomputes). For expensive, rarely-changing lookups, the @tools.ormcache decorator caches results. Profile with the developer mode's query counter before optimizing — measure, don't guess.

N+1 Query Problem

The N+1 problem occurs when iterating a recordset and reading a relational field one record at a time:

# BAD: N+1 queries — one SELECT per loan in the loop
loans = self.env['library.loan'].search([])
for loan in loans:
    print(loan.member_id.name)  # triggers SELECT for each member_id

# GOOD: Odoo prefetch fetches all member names in 1 extra SELECT
# Just access the field — ORM batches automatically within the recordset
for loan in loans:
    print(loan.member_id.name)  # only 2 queries total: loans + members

Odoo's ORM automatically prefetches fields for the entire current recordset when you access a field on one record. This means reading loan.member_id.name for all loans in the recordset costs only 2 queries, not N+1. The problem occurs when you call browse() inside a loop, which creates new single-record environments:

# BAD: browse() inside loop breaks prefetch batching
for loan_id in loan_ids:
    loan = self.env['library.loan'].browse(loan_id)  # new env each time
    print(loan.member_id.name)  # N queries

# GOOD: browse all IDs at once — ORM prefetches together
loans = self.env['library.loan'].browse(loan_ids)
for loan in loans:
    print(loan.member_id.name)  # 2 queries total

ormcache – Caching Model Method Results

@tools.ormcache caches the return value of a model method keyed on its arguments. The cache is invalidated when the model's records are written or deleted. Use it for expensive computations that return the same result for the same inputs:

from odoo import tools

class LibraryBook(models.Model):
    _name = 'library.book'

    @tools.ormcache('self.env.uid', 'category_id')
    def get_books_by_category(self, category_id):
        """Cached — result reused across requests until invalidated."""
        return self.search([('category_id', '=', category_id)]).ids

    def write(self, vals):
        result = super().write(vals)
        # Cache is automatically cleared on write by ORM
        return result

Cache key args should be simple scalars (IDs, booleans, strings). Include self.env.uid if the result differs per user (access rights). The cache lives in memory per worker process — it's not shared across workers.

Batch Operations

Use create([list]) and single-call write() over an entire recordset instead of looping:

# BAD: N individual INSERT queries
for item in data:
    self.env['library.loan'].create({'member_id': item['id'], ...})

# GOOD: 1 batched INSERT
self.env['library.loan'].create([
    {'member_id': item['id'], ...}
    for item in data
])

# BAD: loop write
for loan in loans:
    loan.write({'state': 'returned'})

# GOOD: single write on the whole recordset
loans.write({'state': 'returned'})  # 1 UPDATE WHERE id IN (...)

Stored vs Non-Stored Computed Fields

Stored computed fields are computed on write and stored in the database column — fast to read, computed only when dependencies change. Non-stored fields recompute on every read.

# Store it if it's read often and computation is non-trivial
total_amount = fields.Float(
    string='Total',
    compute='_compute_total_amount',
    store=True,  # stored in DB column, updated when line_ids changes
)

@api.depends('line_ids.subtotal')
def _compute_total_amount(self):
    for record in self:
        record.total_amount = sum(record.line_ids.mapped('subtotal'))

Profiling Queries

Enable SQL query logging in development by setting log_level = debug in odoo.conf or using the debug overlay. In code, use the profiler context manager:

import logging
_logger = logging.getLogger(__name__)

# Log query count around an operation
before = self.env.cr.sql_log_count if hasattr(self.env.cr, 'sql_log_count') else 0
loans = self.env['library.loan'].search([('state', '=', 'active')])
for loan in loans:
    _ = loan.member_id.name
after = getattr(self.env.cr, 'sql_log_count', 0)
_logger.info("Queries executed: %d", after - before)

In production, use Odoo's built-in profiler: open Odoo in debug mode, go to Settings → Technical → Profiler, start profiling, perform the slow action, and review the flamegraph.

Interview Questions

• What causes the N+1 query problem in Odoo?
• How does ORM prefetching help?
• What is @ormcache?
• When is read_group preferable?
• How do you profile a slow Odoo method?

Related Topics