How to Optimize ORM Queries in Odoo 19: Complete Step by Step Tutorial
Using an ORM (Object-Relational Mapping) layer makes it easy to work with databases in modern Odoo applications. Instead of writing complicated SQL commands, developers interact with the database through Python objects and methods. This convenience, however, can introduce hidden performance problems. A single line of code that appears efficient may trigger dozens or hundreds of database queries behind the scenes. Over time, inefficient ORM code slows down application response times, increases server load, and degrades the user experience. This is where ORM query optimization becomes essential. This complete step by step tutorial teaches you the ten most effective techniques to optimize ORM queries in Odoo 19, from understanding the N+1 problem to using raw SQL for complex aggregations and profiling queries for data-driven optimization.
What You'll Learn:
- How the N+1 query problem silently degrades Odoo performance
- How to use prefetching and mapped() to eliminate loop-based queries
- How to avoid repeated search() and browse() calls inside for-loops
- How to use read() and search_read() for selective field fetching
- How to restrict data with domain filters and result limits
- How to implement batch processing for large data sets
- How to minimize computed field overhead with @api.depends
- When and how to fall back to raw SQL for complex reporting
- How to enable and interpret Odoo query logs for profiling
Understanding the N+1 Query Problem
The ORM layer abstracts SQL, but it does not eliminate it. Every ORM operation still translates into database queries behind the scenes. When used carelessly, this can lead to an excessive number of queries, repeated fetching of the same data, and unacceptably slow response times. The most common and destructive pattern is the N+1 query problem.
The N+1 problem occurs when your code executes one query to fetch a set of records and then executes an additional query for each record to fetch related data. If you have 100 sale orders, your code issues 1 query for the orders and then 100 individual queries for the related partners, resulting in 101 total database round-trips. As your data set grows, this pattern quickly becomes a major bottleneck.
10 Techniques to Optimize ORM Queries
Technique 1: Eliminate N+1 with Prefetching and mapped()
The most impactful optimization you can make is to eliminate N+1 queries by prefetching related data before entering a loop. Odoo's ORM provides built-in prefetching, but you must write code that works with it rather than against it.
Consider this problematic example:
orders = self.env['sale.order'].search([])
for order in orders:
print(order.partner_id.name)
# 1 query for orders + N queries for partners
# = 101 total queries for 100 orders
The optimized approach prefetches all related partner data in a single batch before the loop:
orders = self.env['sale.order'].search([])
partners = orders.mapped('partner_id')
partner_names = {p.id: p.name for p in partners}
for order in orders:
print(partner_names.get(order.partner_id.id))
# 1 query for orders + 1 query for all partners
# = 2 total queries for 100 orders
Technique 2: Avoid search() and browse() Inside Loops
Calling search() or browse() inside a for-loop is one of the most common performance mistakes in Odoo development. Each invocation triggers a separate database query, turning a clean-looking loop into a performance disaster.
for record in records:
partner = self.env['res.partner'].search([('id', '=', record.partner_id.id)])
# Triggers a full database query in every loop iteration
The correct approach collects all IDs first, then fetches related records in one batch:
partner_ids = records.mapped('partner_id').ids
partners = self.env['res.partner'].browse(partner_ids)
partner_map = {p.id: p for p in partners}
for record in records:
partner = partner_map.get(record.partner_id.id)
# Batch fetching: 2 queries total regardless of record count
Technique 3: Use read() for Selective Field Fetching
When you only need a few fields from a record set, fetching full records wastes memory and processing time. The read() method returns only the columns you specify, reducing data transfer between the database and the application.
records = self.env['res.partner'].search([])
for rec in records:
print(rec.name)
# Loads all fields into memory for every record
data = self.env['res.partner'].search([]).read(['name'])
for rec in data:
print(rec['name'])
# Faster execution and significantly lower memory usage
Technique 4: Restrict Data with Domains and Limits
Fetching more data than necessary is a common source of poor performance. Always apply domain filters and limit clauses to your search() calls to minimize the data the ORM must retrieve and process.
records = self.env['sale.order'].search([])
# Loads every sale order in the entire database
records = self.env['sale.order'].search([
('state', '=', 'sale')
], limit=50)
# Domain filters reduce result set; limit caps total rows returned
Always apply domain filters to scope your queries and use the limit parameter when you only need a subset of results, such as for dashboards, preview panes, or paginated views.
Technique 5: Combine search() and read() with search_read()
When you need both filtered results and selective fields, search_read() combines search() and read() in a single ORM call, reducing overhead and improving readability.
data = self.env['res.partner'].search_read(
domain=[('customer_rank', '>', 0)],
fields=['name', 'email']
)
# Single ORM operation: filter + field selection + fetch
# Eliminates the need for separate search() then read() calls
This method is ideal for list views, export operations, and API endpoints where you need filtered data with a predefined set of columns. It cuts ORM overhead and returns a list of dictionaries directly, which is often more convenient for serialization.
Technique 6: Implement Batch Processing for Large Data Sets
Processing thousands of records in a single loop consumes excessive memory and can trigger database timeouts. Batch processing breaks the workload into smaller, manageable chunks using the limit and offset parameters.
records = self.env['model'].search([])
for rec in records:
process(rec)
# Loads all records into memory at once — crashes on large tables
batch_size = 100
offset = 0
while True:
records = self.env['model'].search([], limit=batch_size, offset=offset)
if not records:
break
for rec in records:
process(rec)
offset += batch_size
self.env.cr.commit() # Release locks periodically
# Memory-safe: only 100 records in memory at any time
Technique 7: Minimize Computed Field Overhead
Computed fields decorated with @api.depends automatically recompute whenever their dependencies change. While convenient, they can trigger cascading recomputations that generate many additional queries across large record sets.
Follow these guidelines to keep computed fields efficient:
# Minimize the number of field dependencies
@api.depends('line_ids.price_subtotal', 'line_ids.product_id')
def compute_amount_total(self):
for rec in self:
rec.amount_total = sum(rec.line_ids.mapped('price_subtotal'))
# Use stored computed fields for frequently accessed values
amount_total = fields.Monetary(
compute='_compute_amount_total',
store=True # Cache to avoid recomputation on every read
)
# Move heavy logic out of compute methods into cron tasks
@api.depends('date_order', 'state')
def compute_quarterly_report(self):
# Defer complex aggregations to scheduled actions
pass
Technique 8: Use Raw SQL for Complex Aggregations
While the ORM is powerful for standard CRUD operations, it is not always the fastest option. For complex aggregations, multi-table joins, and reporting queries, raw SQL can deliver significantly better performance by eliminating ORM overhead.
Use self.env.cr.execute() and fetchall() when you need grouped or aggregated results:
self.env.cr.execute("""
SELECT partner_id, COUNT(*) as order_count, SUM(amount_total) as total_revenue
FROM sale_order
WHERE state IN ('sale', 'done')
GROUP BY partner_id
HAVING COUNT(*) > 5
ORDER BY total_revenue DESC
""")
result = self.env.cr.fetchall()
for partner_id, count, revenue in result:
print(f"Partner {partner_id}: {count} orders, ${revenue:.2f}")
# Single SQL query vs. potentially hundreds of ORM calls
Raw SQL is especially valuable for dashboards, custom reports, data migration scripts, and batch update operations where the ORM's object-oriented overhead is unnecessary.
Technique 9: Monitor and Profile Queries
Optimization decisions should always be data-driven. Guessing which queries are slow leads to wasted effort. Odoo provides built-in tools to enable query logging and identify real bottlenecks in your code.
# In odoo.conf or command-line:
--log-level=debug_sql
# Or programmatically within a method:
import logging
_logger = logging.getLogger(__name__)
# Wrap suspicious code with query count tracking
from odoo import api
query_count_before = len(self.env.cr._queries) # Odoo 18+
# ... your code here ...
query_count_after = len(self.env.cr._queries)
_logger.info(f"Queries executed: {query_count_after - query_count_before}")
# Use Developer Mode > Debug > View SQL Query
# to inspect individual ORM operations
Technique 10: Choose the Right Fetching Strategy
Different use cases demand different ORM methods. Choosing the wrong fetching strategy is a common source of unnecessary queries. Match your approach to the task at hand for maximum efficiency.
| Use Case | Recommended Method | Why |
|---|---|---|
| Full record update | search() + browse() | Needs full ORM record with write() capability |
| Read-only data export | search_read() | Filter + select fields in one call |
| Display a few fields | mapped() or read() | Minimal memory footprint |
| Dashboard aggregation | Raw SQL | GROUP BY, HAVING, complex joins |
| Batch cron job | search() with limit/offset | Memory-safe batch processing |
| Single record lookup | browse() | Direct ID access, no search overhead |
Key Takeaways for ORM Optimization
Poorly optimized ORM queries can make your application slower, increase database load, and affect scalability. By following these best practices — eliminating loop queries, using batch operations, cutting down unnecessary data fetching, and leveraging raw SQL when appropriate — you will achieve significant performance gains. Optimization is about writing better code, not just functional code. Minor tweaks to queries can have a major impact as your application scales.
Frequently Asked Questions
What is the N+1 query problem in Odoo ORM?
The N+1 problem occurs when one query fetches a parent record set and N additional queries fetch related data for each record. For 100 sale orders, this creates 101 queries. Use mapped() to prefetch related data in batch and reduce it to 2 queries total.
When should I use raw SQL instead of Odoo ORM?
Use raw SQL for complex aggregations, multi-table joins with GROUP BY and HAVING clauses, and custom reporting where ORM overhead is unnecessary. Dashboards, data migration scripts, and batch updates are common candidates for raw SQL optimization.
How can I track the number of ORM queries my code executes?
Enable query logging with --log-level=debug_sql in odoo.conf, or count queries programmatically by checking len(self.env.cr._queries) before and after your code. You can also use Developer Mode and click View SQL Query to inspect individual ORM operations.
What is the difference between read() and search_read() in Odoo?
read() fetches specified fields from an existing record set and returns a list of dictionaries. search_read() combines filtering and field selection in one call, applying a domain before fetching only the requested fields. search_read() is more efficient when you need both filtered results and selective columns.
How does batch processing improve ORM query performance?
Batch processing divides large data sets into smaller chunks using limit and offset parameters, preventing memory exhaustion and database timeouts. Processing 10,000 records in batches of 100 keeps memory usage constant and allows periodic commits to release database locks during long-running operations.
Need Help with Odoo 19 Development?
Our Odoo development experts can help you optimize your ORM queries, improve application performance, set up custom modules, and build scalable Odoo solutions tailored to your business needs.
About the author
Head of Odoo Practice
Leads Braincuber's Odoo implementations across the US, India, and EU. Shipped 50+ Odoo deployments. Specializes in NetSuite and SAP Business One migrations.