unclecatvn/agent-skills / skills/odoo-18.0
odoo-18
VerifiedOdoo 18 development reference for Python models and ORM (search, domain, read_group, compute fields), XML/CSV data and views, OWL/JS client code, QWeb reports, security (ACL, record rules, groups), cron and server actions, migrations and module upgrades, tests, i18n, and performance. Use this skill whenever work involves Odoo 18 or custom addons—even if the user only pastes a traceback, mentions addons/ or __manifest__.py, describes form/list/kanban/XML errors, HTTP controllers, or business rules on models—including building features, fixing bugs, refactoring, or reviewing addon code. Includes CSS/SCSS asset authoring and review for Odoo addons.
references/odoo-18-performance-guide.md
View on GitHub---
name: odoo-18-performance
description: Complete guide for writing performant Odoo 18 code, focusing on N+1 query prevention, batch operations, and optimization patterns.
globs: "**/*.{py,xml}"
topics:
- Prefetch mechanism (how it works, understanding groups)
- N+1 query prevention patterns
- Batch operations (create, write, unlink)
- Field selection optimization (search_read, load, bin_size)
- Aggregation optimization (_read_group core method, read_group for UI)
- Compute field optimization (store, precompute, avoiding recursion)
- SQL optimization (when to use, execute_query, SQL class)
- Clean code patterns (mapped, filtered, sorted)
when_to_use:
- Optimizing slow code
- Preventing N+1 queries
- Writing batch operations
- Optimizing computed fields
- Using _read_group() for aggregations
- Using direct SQL for aggregations
---
# Odoo 18 Performance Guide
## Coding Conventions
Prefer batch-aware ORM operations; do not commit Odoo's ambient transaction.
Complete guide for writing performant Odoo 18 code, focusing on N+1 query prevention and clean patterns.
## Table of Contents
1. [Prefetch Mechanism](#prefetch-mechanism)
2. [N+1 Query Prevention](#n1-query-prevention)
3. [Batch Operations](#batch-operations)
4. [Field Selection Optimization](#field-selection-optimization)
5. [Compute Field Optimization](#compute-field-optimization)
6. [SQL Optimization](#sql-optimization)
---
## Prefetch Mechanism
### How Prefetch Works
Odoo automatically prefetches records in batches to minimize queries.
```python
# Constants from Odoo base
PREFETCH_MAX = 1000 # Maximum records prefetched per batch
INSERT_BATCH_SIZE = 100
UPDATE_BATCH_SIZE = 100
```
**How it works**:
1. When you access a field on a recordset, Odoo loads that field for ALL records in the recordset
2. This happens per model, not per relation
3. Related records are also prefetched up to `PREFETCH_MAX`
```python
# GOOD: Automatic prefetch
orders = self.search([('state', '=', 'done')]) # 1 query for orders
for order in orders:
print(order.name) # 1 query for all names
print(order.partner_id.name) # 1 query for all partners
# Total: 3 queries regardless of number of orders
```
### Understanding Prefetch Groups
```python
# Orders with same partner_id - partner fetched once
orders = self.search([('partner_id', '=', partner_id)])
for order in orders:
print(order.partner_id.name) # 1 query for all orders
# Orders with different partners - partners fetched in batch
orders = self.search([]) # Many different partners
for order in orders:
print(order.partner_id.name) # Queries in batches of 1000
```
---
## N+1 Query Prevention
### Pattern 1: Search Inside Loop (BAD)
```python
# BAD: N+1 query
for order in orders:
payments = self.env['payment.transaction'].search([
('order_id', '=', order.id)
])
order.payment_count = len(payments)
# Result: 1 + N queries
# GOOD: Use read_group or search with IN domain
order_ids = orders.ids
all_payments = self.env['payment.transaction'].search_read(
[('order_id', 'in', order_ids)],
['order_id']
)
# Count by order
from collections import defaultdict
payment_counts = defaultdict(int)
for payment in all_payments:
payment_counts[payment['order_id'][0]] += 1
for order in orders:
order.payment_count = payment_counts.get(order.id, 0)
# Result: 1 query
```
### Pattern 2: One2many Traversal
```python
# GOOD: Use mapped() instead of loop
orders = self.search([('state', '=', 'done')])
# GOOD: Prefetch works automatically
for order in orders:
for line in order.line_ids:
print(line.product_id.name)
# Result: ~3 queries (orders, lines, products)
# BETTER: Preload with read() if you only need specific data
lines_data = orders.mapped('line_ids').read(['product_id', 'quantity'])
```
### Pattern 3: Computed Field with Related Access
```python
# BAD: Triggers query for each record
@api.depends('partner_id')
def _compute_partner_email(self):
for order in self:
order.partner_email = order.partner_id.email # N queries
# GOOD: Add partner_id.email to depends
@api.depends('partner_id', 'partner_id.email')
def _compute_partner_email(self):
for order in self:
order.partner_email = order.partner_id.email # 1 query prefetched
```
### Pattern 4: Conditional Computation
```python
# BAD: Check inside loop triggers queries
for order in orders:
if order.partner_id.customer_rank > 0:
order.is_customer = True
# Each access to partner_id.customer_rank triggers fetch
# GOOD: Use filtered()
customers = orders.filtered(lambda o: o.partner_id.customer_rank > 0)
customers.is_customer = True
```
---
## Batch Operations
### Batch Create (Odoo 18)
```python
# GOOD: Batch create (Odoo 18 standard)
records = self.create([
{'name': f'Record {i}', 'state': 'draft'}
for i in range(100)
])
# Single INSERT batch
# BAD: Create in loop
for i in range(100):
self.create({'name': f'Record {i}'})
# 100 INSERT statements
```
### Batch Write
```python
# GOOD: Write on recordset
self.search([('state', '=', 'draft')]).write({'state': 'cancel'})
# Single UPDATE batch
# BAD: Write in loop
for order in self.search([('state', '=', 'draft')]):
order.write({'state': 'cancel'})
# N UPDATE statements
```
### Batch Unlink
```python
# GOOD: Unlink recordset
self.search([('state', '=', 'cancel')]).unlink()
# Single DELETE batch
# BAD: Unlink in loop
for order in self.search([('state', '=', 'cancel')]):
order.unlink()
# N DELETE statements
```
---
## Field Selection Optimization
### Use search_read for Specific Fields
```python
# GOOD: search_read when you need dicts, not recordsets
data = self.search_read(
[('state', '=', 'done')],
['name', 'amount_total', 'date']
)
# Returns: [{'id': 1, 'name': ..., 'amount_total': ..., 'date': ...}, ...]
# BAD: search() + read() is slower
records = self.search([('state', '=', 'done')])
data = records.read(['name', 'amount_total', 'date'])
```
### Use _read_group() for Aggregations
**`_read_group()`** is the core aggregation method that `read_group()` calls internally. It returns raw tuples with proper recordsets for relational fields.
```python
# GOOD: _read_group() returns tuples: [(groupby_value, aggregate1, aggregate2, ...), ...]
for partner_id, amount_total, count in self._read_group(
domain=[('state', '=', 'done')],
groupby=['partner_id'],
aggregates=['amount_total:sum', 'id:count'],
):
# partner_id: recordset (Many2one field)
# amount_total: float
# count: int
print(f"{partner_id.name}: {amount_total} ({count} orders)")
```
#### Converting to Dictionary for Efficient Lookup
```python
# GOOD: Convert _read_group result to dict for O(1) lookup
# From Odoo base: account_move_line.py
matching2lines = dict(self._read_group(
domain=[('matching_number', 'in', matching_numbers)],
groupby=['matching_number'],
aggregates=['id:recordset'],
))
# Result: {matching_number: lines_recordset, ...}
```
#### Multiple Groupby Fields
```python
# GOOD: Multiple groupby fields
for matching_number, account, lines in self._read_group(
domain=[('matching_number', 'in', temp_numbers)],
groupby=['matching_number', 'account_id'],
aggregates=['id:recordset'],
):
# matching_number: string
# account: account.account recordset
# lines: account.move.line recordset
if all(move.state == 'posted' for move in lines.move_id):
# Process grouped lines
pass
```
#### read_group() vs _read_group()
| Feature | `_read_group()` | `read_group()` |
|---------|-----------------|----------------|
| Return type | List of tuples | List of dicts |
| API style | `domain, groupby, aggregates` | `domain, fields, groupby` |
| Metadata | ❌ No `__domain`, `__context` | ✅ Includes metadata |
| Lazy grouping | ❌ Not supported | ✅ Supported |
| Empty group fill | ❌ Not supported | ✅ Supported |
| Recordsets | ✅ Proper browse records | ✅ Proper browse records |
| Used internally | ✅ Core method | Wrapper that calls `_read_group()` |
| Use case | Data processing, internal logic | UI components, reports |
**Key Insight**: `_read_group()` is the **core method** that `read_group()` calls internally (see `odoo/models.py:2888`). Both return proper recordsets for relational fields.
```python
# _read_group() - Core method, simpler API
# Returns: [(groupby_val1, agg1, agg2), (groupby_val2, agg1, agg2), ...]
data = self._read_group(
domain=[('state', '=', 'done')],
groupby=['partner_id'],
aggregates=['amount_total:sum', '__count'],
)
# read_group() - Public API with metadata
# Returns: [{'partner_id': (1, 'Name'), 'amount_total': 100, '__domain': [...}, ...]
data = self.read_group(
domain=[('state', '=', 'done')],
fields=['amount_total'],
groupby=['partner_id'],
lazy=True,
)
```
**When to use `_read_group()`** (recommended for most cases):
- Data processing and aggregation
- Building internal data structures
- When you don't need `__domain` metadata
- When you want tuple unpacking for cleaner code
- **Used extensively in Odoo base code** (400+ files)
**When to use `read_group()`**:
- UI components that need `__domain` for drill-down
- Reports with lazy grouping
- When you need empty group filling
- Pivot graphs, kanban views
### Load Parameter for Read
```python
# GOOD: Use load='_classic_read' for simple fields
data = records.read(['name', 'date'], load='_classic_read')
# Use load=None to avoid computing fields
data = records.read(['name', 'state'], load=None)
```
### Bin Size for Binary Fields
```python
# GOOD: Get size instead of content
attachments.with_context(bin_size=True).read(['datas', 'name'])
# Returns: {'datas': 12345, ...} instead of base64 content
```
### Fetch Only What You Need
```python
# GOOD: Select only needed fields
products = self.env['product.product'].search_read(
[('active', '=', True)],
['id', 'name', 'default_code', 'lst_price']
)
# BAD: Fetch all fields
products = self.env['product.product'].search([('active', '=', True)])
```
---
## Compute Field Optimization
### Store Expensive Computations
```python
# GOOD: Store expensive aggregations
amount_total = fields.Float(
string='Total',
compute='_compute_amount_total',
store=True,
# Store allows search/group by
compute_sudo=True, # Compute as admin for performance
)
@api.depends('line_ids.price_subtotal')
def _compute_amount_total(self):
for order in self:
order.amount_total = sum(order.line_ids.mapped('price_subtotal'))
```
### Use Precompute for Form Performance
```python
# Use precompute when field can be computed before creation
sequence = fields.Integer(
string='Sequence',
compute='_compute_sequence',
precompute=True, # Computed at form init
store=False,
)
@api.depends('date_order')
def _compute_sequence(self):
for order in self:
if order.date_order:
order.sequence = self.env['ir.sequence'].next_by_code(...)
```
**Warning**: `precompute=True` can be counterproductive for:
- Statistics fields (count, sum over search)
- Fields that require database reads
- One-off record creation (not batch)
### Avoid Recursive Dependencies
```python
# BAD: Recursive dependency
field_a = fields.Float(compute='_compute_a', store=True)
field_b = fields.Float(compute='_compute_b', store=True)
@api.depends('field_b') # A depends on B
def _compute_a(self):
for rec in self:
rec.field_a = rec.field_b * 2
@api.depends('field_a') # B depends on A - INFINITE LOOP
def _compute_b(self):
for rec in self:
rec.field_b = rec.field_a / 2
# GOOD: Use a common base field
amount = fields.Float(string='Amount')
tax = fields.Float(compute='_compute_tax', store=True)
total = fields.Float(compute='_compute_total', store=True)
@api.depends('amount')
def _compute_tax(self):
for rec in self:
rec.tax = rec.amount * 0.1
@api.depends('amount', 'tax') # Both depend on amount only
def _compute_total(self):
for rec in self:
rec.total = rec.amount + rec.tax
```
---
## SQL Optimization
### When to Use Direct SQL
**Use SQL for**:
- Complex aggregations (count with grouping)
- Bulk data migration
- Reports with joins across many tables
- Performance-critical read operations
```python
def get_statistics(self):
"""Direct SQL for complex aggregation"""
self.env.cr.execute("""
SELECT
state,
COUNT(*) as count,
SUM(amount_total) as total
FROM sale_order
WHERE create_date >= %s
GROUP BY state
""", (fields.date.today(),))
return dict(self.env.cr.fetchall())
```
**Use SQL class for safety** (Odoo 18):
```python
from odoo.tools import SQL
def get_statistics(self):
query = SQL("""
SELECT state, COUNT(*), SUM(amount_total)
FROM %s
WHERE create_date >= %s
GROUP BY state
""", SQL.identifier('sale_order'), fields.date.today())
return self.env.execute_query_dict(query)
```
### Never Use SQL for Writes (unless necessary)
```python
# BAD: SQL write bypasses ORM (no compute, no cache, no triggers)
self.env.cr.execute("UPDATE sale_order SET state='done' WHERE id IN %s", (ids,))
# GOOD: Use ORM write
self.browse(ids).write({'state': 'done'})
```
### Use execute_query for Read (Odoo 18)
```python
from odoo.tools import SQL
# GOOD: execute_query handles flush automatically
def get_order_totals(self):
query = SQL("""
SELECT id, amount_total
FROM sale_order
WHERE state = %s
""", 'done')
return self.env.execute_query_dict(query)
```
---
## Clean Code Performance Patterns
### Use Mapped() Instead of List Comprehension
```python
# GOOD: Use mapped() for field access
partner_ids = orders.mapped('partner_id.id')
names = orders.mapped('name')
# GOOD: mapped() works with nested paths
countries = orders.mapped('partner_id.country_id')
# GOOD: mapped() removes duplicates
all_tags = orders.mapped('tag_ids') # Returns unique tags
```
### Use Filtered() for Conditional Operations
```python
# GOOD: Filter before processing
done_orders = orders.filtered(lambda o: o.state == 'done')
done_orders.action_invoice_create()
# GOOD: Chain filters
high_value = orders.filtered(lambda o: o.amount_total > 1000)
urgent = high_value.filtered(lambda o: o.priority == '2')
```
### Use Sorted() with Key
```python
# GOOD: Sort in memory for small sets
sorted_orders = orders.sorted(key=lambda o: o.amount_total, reverse=True)
# BAD: Don't sort in memory for large sets
# Use database order instead
orders = self.search([], order='amount_total DESC')
```
### Avoid Recomputation in Loops
```python
# BAD: Write in loop triggers recomputation each time
for order in orders:
order.write({'state': 'done'}) # Triggers compute each iteration
# GOOD: Batch write - single compute at end
orders.write({'state': 'done'})
# GOOD: Use with_context to prevent recomputation
orders.with_context(tracking_disable=True).write({'state': 'done'})
```
---
## Performance Checklist
- [ ] Avoid `search()` inside loops
- [ ] Use `mapped()` instead of list comprehension for field access
- [ ] Use `search_read()` when you need dicts, not recordsets
- [ ] Use `_read_group()` for aggregations (core method; use `read_group()` only when you need `__domain` metadata or lazy grouping for UI)
- [ ] Store expensive computed fields
- [ ] Add all dependencies to `@api.depends`
- [ ] Use `with_context(bin_size=True)` for binary fields
- [ ] Use `with_context(active_test=False)` when including archived
- [ ] Batch create/write/unlink operations
- [ ] Add indexes on frequently searched fields
- [ ] Use `filtered()` before operations
- [ ] Don't use SQL for writes (use ORM)
- [ ] Use direct SQL for complex read aggregations only
---
## Common Performance Anti-Patterns
### Anti-Pattern 1: Search without limit
```python
# BAD: Could fetch millions of records
all_records = self.search([('state', '=', 'draft')])
# GOOD: Use limit or pagination
records = self.search([('state', '=', 'draft')], limit=100)
```
### Anti-Pattern 2: Computing in loop
```python
# BAD: Compute method does expensive operation
@api.depends('order_id')
def _compute_order_total(self):
for line in self:
# Search in loop - N queries
line.order_total = self.search_count([
('order_id', '=', line.order_id.id)
])
```
### Anti-Pattern 3: Not using exists()
```python
# BAD: Fetches all records
records = self.search([('state', '=', 'done')])
if len(records) > 0:
# ...
# GOOD: Only checks existence
exists = self.search_count([('state', '=', 'done')]) > 0
# OR
if self.search([('state', '=', 'done')], limit=1):
# ...
```
### Anti-Pattern 4: Over-fetching
```python
# BAD: Fetches all fields then only uses one
records = self.search([('state', '=', 'done')])
for record in records:
print(record.name) # Only using name
# GOOD: Only fetch needed field
data = self.search_read([('state', '=', 'done')], ['name'])
for row in data:
print(row['name'])
```
---
## Flush & Recompute (Odoo 18)
### Understanding Flush
Odoo 18 uses lazy writes - changes are cached and flushed to database later.
```python
# Flush specific fields to database
self.flush_model(['amount_total', 'state'])
# Flush current recordset only
self.flush_recordset(['amount'])
# Flush before direct SQL query
self.flush_model() # Flush all pending changes
self.env.cr.execute("SELECT ...")
```
### flush_model() vs flush_recordset()
```python
# flush_model - flush for entire model (all records)
self.env['sale.order'].flush_model(['amount_total'])
# flush_recordset - flush only current records
orders.flush_recordset(['amount_total'])
```
### Recompute Control
```python
# Manual recompute of stored computed fields
self._recompute_model(['amount_total']) # Entire model
self._recompute_recordset(['amount_total']) # Current records
self._recompute_field(self._fields['amount_total']) # Specific field
# Recompute with specific ids
self._recompute_field(field, ids=[1, 2, 3])
```
### Batch Recompute Optimization
```python
# GOOD: Let Odoo handle recomputation automatically
orders.write({'state': 'done'})
# amount_total will be recomputed in batch automatically
# AVOID: Manual recomputation in loop
for order in orders:
order.write({'state': 'done'})
order.amount_total # Triggers individual recomputation
```
### Flush Before SQL Query (Odoo 18)
```python
from odoo.tools import SQL
# Mark fields to flush before SQL query
query = SQL("""
SELECT id, amount
FROM sale_order
WHERE state = %s
""", 'done')
# Option 1: Mark fields to flush (auto-flushes those fields)
query.to_flush = [self._fields['state']]
# Option 2: Use execute_query_dict (auto-flushes)
results = self.env.execute_query_dict(query)
```
### with_context for Performance
```python
# Disable tracking for bulk operations (faster)
records.with_context(tracking_disable=True).write({'state': 'done'})
# Use bin_size for binary fields
attachments.with_context(bin_size=True).read(['datas', 'name'])
# Disable active_test to include archived
all_records = self.with_context(active_test=False).search([])
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| rea-analysis-optimizationmorluto/reaOptimize REA providers and shared analysis workflows for agent context, latency, CPU, and memory while preserving correctness and Evidence. Use for analysis bottlenecks and cache or query design; CI/test-suite optimization belongs to rea-ci-tests-audit. | Stars: 472k | Updated | Claude CodeCodex | Verified | Install |
| rea-ci-tests-auditmorluto/reaAudit REA CI and test suites for correctness, fixture drift, false passes, and measured performance; use for suite or workflow investigations and efficiency changes, rather than ordinary feature testing or a single test failure. | Stars: 472k | Updated | Claude CodeCodex | Verified | Install |
| rea-tool-designmorluto/reaAudit, investigate, design, or change REA investigation tools, CLI/MCP contracts, provider capabilities, and Evidence semantics. | Stars: 472k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-auditDietrichGebert/ponytailQuality audit of the whole repo: bugs, security, real load, missing tests, speed, and what to delete. Most important first. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |