As Odoo implementations grow, the amount of data stored in the database increases rapidly. What initially starts as a system with a few thousand records can eventually grow to hundreds of thousands or even millions across different models. Transaction-heavy models such as sale.order, account.move, stock.move, and logging tables usually grow the fastest.
When datasets become large, inefficient queries, unnecessary record loading, and poorly structured data access patterns begin to affect system performance. Developers may notice slower list views, delayed search results, and heavier backend operations. Because of this, handling large datasets properly is an important aspect of data model optimization in Odoo 19.
By following a few development best practices, it is possible to maintain good performance even when the database grows significantly.
Avoid Loading Unnecessary Records
One of the most common issues when working with large datasets is loading more records than required. Developers sometimes perform a search() that retrieves thousands of records even though only a small portion of them is actually needed.
For example:
moves = self.env['stock.move'].search([('state', '=', 'done')])If the model contains a large number of records, this query will load all matching records into memory.
If only a limited number of records are required, it is better to use the limit parameter.
moves = self.env['stock.move'].search(
[('state', '=', 'done')],
limit=100
)
Fetching only the required records reduces memory usage and improves response time.
Process Records in Batches
Another common scenario involves performing operations on a large number of records. Processing thousands of records in a single operation can consume a large amount of memory and slow down the system.
Instead, records can be processed in smaller batches.
records = self.env['stock.move'].search([])
batch_size = 1000
for i in range(0, len(records), batch_size):
batch = records[i:i + batch_size]
batch.write({'processed': True})
Batch processing ensures that the system works with manageable chunks of data rather than attempting to process everything at once.
Use Pagination for Large Queries
When retrieving large datasets for reports, integrations, or background operations, pagination helps keep queries efficient.
Odoo’s search() method supports both limit and offset, allowing records to be fetched in segments.
orders = self.env['sale.order'].search(
[('state', '=', 'sale')],
limit=200,
offset=400
)
This technique is particularly useful for data exports, scheduled operations, or integrations with external systems.
Store Expensive Computed Fields
Computed fields are powerful, but they can become expensive when calculations depend on large datasets. If the field is recalculated every time it is accessed, it may create unnecessary overhead.
In such cases, storing the computed value in the database can improve performance.
total_amount = fields.Float(
compute='_compute_total_amount',
store=True
)
With store=True, the computed value is saved in the database and recalculated only when its dependencies change.
Add Indexes for Frequently Searched Fields
When datasets grow large, searching records without proper indexing can slow down database queries. Indexes allow the database to locate records faster when filtering or searching.
For example:
partner_id = fields.Many2one(
'res.partner',
index=True
)
Adding indexes to fields that are frequently used in search domains or filters can significantly improve query performance.
Avoid Repeated Queries Inside Loops
Another common performance issue occurs when relational fields are accessed repeatedly inside loops. Each access can trigger additional database queries, which becomes inefficient when dealing with large datasets.
Instead of triggering queries inside loops, it is better to retrieve the required data in bulk using methods like mapped() or by reading fields in advance.
Reducing unnecessary database queries improves the overall efficiency of operations that process large recordsets.
Using read_group Instead of Loading Large Recordsets
In some cases, developers load large recordsets just to calculate totals or grouped values. This approach becomes inefficient when datasets grow.
For example:
orders = self.env['sale.order'].search([('state', '=', 'sale')])
total = sum(orders.mapped('amount_total'))This code loads all matching records into memory before computing the total.
A more efficient approach is to use read_group, which performs aggregation directly in the database.
result = self.env['sale.order'].read_group(
[('state', '=', 'sale')],
['amount_total:sum'],
[]
)
total = result[0]['amount_total_sum'] if result else 0
This reduces memory usage and allows the database to handle the aggregation efficiently.
Avoid Using len(recordset) for Large Data
Another small but important optimization is avoiding the use of len() on large recordsets.
For example:
orders = self.env['sale.order'].search([('state', '=', 'sale')])
count = len(orders)This approach loads all records before counting them.
Instead, it is better to use search_count().
count = self.env['sale.order'].search_count([
('state', '=', 'sale')
])
search_count() performs the count directly in the database, making it much more efficient.
Use read() When Only Specific Fields Are Needed
Sometimes a method requires only a few fields from each record, but the code loads the entire recordset.
When working with large datasets, it is better to fetch only the required fields.
orders = self.env['sale.order'].search([('state', '=', 'sale')])
data = orders.read(['name', 'amount_total'])Fetching only necessary fields reduces the amount of data transferred from the database and improves performance.
Consider SQL Queries for Extremely Large Tables
In most situations, the Odoo ORM provides sufficient performance. However, when working with extremely large tables such as logs or historical records, raw SQL queries can sometimes be more efficient for heavy aggregations.
For example:
self.env.cr.execute("""
SELECT partner_id, COUNT(*)
FROM sale_order
WHERE state = 'sale'
GROUP BY partner_id
""")
results = self.env.cr.fetchall()SQL queries run directly at the database level, making them useful for heavy reporting or background processing tasks. However, they should be used carefully to ensure compatibility with Odoo’s ORM and access rules.
As Odoo implementations grow, managing large datasets efficiently becomes increasingly important. Small inefficiencies in queries or record handling can eventually lead to noticeable performance issues when the database size increases.
By avoiding unnecessary record loading, processing records in batches, using efficient aggregation methods like read_group, and adding proper indexing, developers can ensure that Odoo 19 continues to perform smoothly even when working with large volumes of data.
To read more about How Field Indexing Improves Data Model Performance in Odoo 19, refer to our blog How Field Indexing Improves Data Model Performance in Odoo 19.