How to avoiding n+1 queries in Odoo?


✅ What is the N+1 Query Problem?

The N+1 problem occurs when your code executes 1 query for a list, and then 1 additional query per item in that list. This results in many unnecessary database hits, severely affecting performance.



📌 Realistic Example: N+1 Issue in Computed Fields


❌ Inefficient Code (N+1 Issue):


class ResPartner(models.Model):

    _inherit = 'res.partner'

​

    sale_count = fields.Integer(compute='_compute_sale_count')

​

    def _compute_sale_count(self):

        for partner in self:

            partner.sale_count = self.env['sale.order'].search_count([

                ('partner_id', '=', partner.id)

            ])


For 100 partners, this will run 100 separate queries, one for each partner.id.


✅ Optimized Code (Avoiding N+1):


def _compute_sale_count(self):

    sale_data = self.env['sale.order'].read_group(

        [('partner_id', 'in', self.ids)],

        ['partner_id'],

        ['partner_id']

    )

    mapped_data = {item['partner_id'][0]: item['partner_id_count'] for item in sale_data}

    for partner in self:

        partner.sale_count = mapped_data.get(partner.id, 0)


  • Uses a single aggregated query.
  • Maps data by partner_id to set values in memory.
  • Significantly improves performance for large datasets.



📌 Another Example: N+1 in Loops


❌ Inefficient:

for order in self.env['sale.order'].search([]):

    print(order.partner_id.name)

  • Each order.partner_id.name triggers an additional query if not prefetched.


✅ Optimized:


orders = self.env['sale.order'].search([], prefetch_fields=False)

orders.with_context(prefetch_fields=['partner_id', 'partner_id.name'])

for order in orders:

    print(order.partner_id.name)

OR simply:

orders = self.env['sale.order'].search([], order='id')

for order in orders:

    print(order.partner_id.name)

​

  • By default, Odoo ORM does prefetching efficiently for related fields.
  • Avoid using .search() inside a loop — use read_group, mapped, or batch logic.


🔁 Best Practices to Avoid N+1


Use Case

Instead of

Use

Count related records

Loop with search_count()

read_group()

Access relational fields in loop

for rec in ...: rec.related.name

Ensure prefetching

Looping inside computed fields

Per-record query

Grouped/batch queries

Load related records

search() in loop

Use browse() or mapped()



✅ Summary Table


Symptom

Cause

Solution

Many DB hits in logs

search() inside loops

Use read_group()

Slow computed field

Query per record

Prefetch & aggregate queries

Loop with relational fields

Lazy loading per iteration

Prefetch or use .mapped()