Using an ORM (Object-Relational Mapping) layer makes it easy to work with databases in today's apps, especially those built on the Odoo framework. Instead of having to deal with complicated SQL commands, programmers only need to know how to program in aaPython.
This can help things run more smoothly, but there are times when it can cause performance issues.
A line of code that looks like it works well might actually be running a lot of database queries behind the scenes. Over time, inefficient code makes applications run slowly and use a lot of resources, which makes the user experience bad. This is when ORM Query Optimisation becomes necessary.
ORM query optimisation is all about making database operations as efficient as possible.
1. Understanding the Problem
ORM hides SQL, but it doesn't get rid of it. Every operation in ORM still turns into database queries. This can happen if you don't use it carefully:
- Too many questions
- Fetching data over and over
- Response times that are too slow
The N+1 query problem is one of the most common issues.
2. The N+1 Query Problem
Consider this example:
orders = self.env['sale.order'].search([])
for order in orders:
print(order.partner_id.name)
This looks fine at first glance. But inside:
- One query gets all the orders.
- Then, for each order, a different query gets the partner that goes with it.
This leads to 101 queries if you have 100 orders.
Best Way to Do It:
Odoo ORM lets you prefetch, but you still need to write code that works with it:
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))
This reduces the number of queries significantly.
3. Avoiding Queries Inside Loops
A common mistake is calling search() or browse() inside loops:
for record in records:
partner = self.env['res.partner'].search([('id', '=', record.partner_id.id)])
This makes a query happen every time.
Better Approach:
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 makes things run better and puts less strain on the database.
4. Using read() Instead of Full Records
Sometimes you only need a few fields from an ORM record.
records = self.env['res.partner'].search([])
for rec in records:
print(rec.name)
This puts full records into memory.
Optimized Version:
data = self.env['res.partner'].search([]).read(['name'])
for rec in data:
print(rec['name'])
This is quicker and uses less memory.
5. Limiting Data with Domain and Fields
Another common problem is getting data that isn't needed.
records = self.env['sale.order'].search([])
This loads everything.
Better Approach:
records = self.env['sale.order'].search([
('state', '=', 'sale')
], limit=50)
Always:
- Use filters (domain)
- Limit results when possible
6. Using search_read() for Efficiency
Instead of calling search() and then read(), combine them:
data = self.env['res.partner'].search_read(
domain=[('customer_rank', '>', 0)],
fields=['name', 'email']
)
This cuts down ORM overhead and boosts performance.
7. Batch Processing
Your system may slow down if you try to process a lot of data at one time.
records = self.env['model'].search([])
for rec in records:
process(rec)
Optimized Batch Approach:
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
8. Avoiding Unnecessary Computations
Computed fields and @api.depends methods can cause additional queries.
Ensure that:
- Minimal dependencies
- Calculations are efficient
- Heavy logic is not done in compute methods
9. Using SQL When Necessary
ORM is very powerful but not always the fastest.
For complex aggregations or reports, raw SQL can be a better option performance-wise:
self.env.cr.execute("""
SELECT partner_id, COUNT(*)
FROM sale_order
GROUP BY partner_id
""")
result = self.env.cr.fetchall()10. Monitoring and Profiling Queries
Optimisation always has to be data-driven.
You may have to:
- Activate Odoo query logs
- Use debugging mode
- Analyse slow queries
This helps you find real bottlenecks, not just guess at them. ORM makes development faster and cleaner, but it’s easy to lose track of what’s going on behind the scenes.
Poorly optimised ORM queries can:
- Make your application slower
- Database load increase
- Scalability of affect
By following best practices including:
- Looped queries.
- Batch operations
- Cut down on unnecessary data fetching
These practices will help you get a big performance increase. Ultimately, optimising ORM queries is about writing better code, not just functional code. Minor tweaks to queries can have a major impact as your app scales up to the next level.
To read more about What Are ORM Query Logs in Odoo 19 and Why Do They Matter, refer to our blog What Are ORM Query Logs in Odoo 19 and Why Do They Matter.