✅ 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.
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
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 Code (Avoiding N+1):
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.
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() |
