Odoo Implementation Services: Fix ORM Query Bottlenecks

A custom Odoo module can be functionally correct and still become unusable when the recordset grows.

For businesses planning an ERP rollout, this is where Odoo Implementation Services need to go beyond module configuration. Odoo implementations often involve custom workflows, integrations, data migration, and business-specific modules. Each of these can introduce database performance problems if the ORM is used inefficiently.

We commonly see this around computed fields, dashboards, imports, automated workflows, and integrations. A method loops through records and performs another database lookup for each one. With 50 records, nobody notices. With 1,000 records, the database can become the bottleneck.

This article focuses on one specific failure mode: repeated ORM queries inside recordset loops.

We will start with the naive implementation, profile it, replace per-record operations with batch operations, and then consider indexes only where the query pattern justifies them.

The examples use Python and Odoo's ORM with PostgreSQL.

1. Start by reproducing the query explosion

The first symptom is usually not an exception. It is a page that gets progressively slower as the number of records increases.

Consider a computed field that counts related records:

# Odoo Implementation Services: this naive pattern can issue one query per record.
def _compute_count(self):
    for record in self:
        domain = [('related_id', '=', record.id)]
        record.count = self.env['other.model'].search_count(domain)

The code is easy to understand. It is also expensive at scale.

The problem is the search_count() call inside the loop. If the recordset contains 1,000 records, the implementation can trigger a large number of separate database operations.

The important question is therefore not only:

Does this return the correct value?

Ask instead:

How many database operations does this Odoo Implementation Services perform for the complete recordset?

That distinction becomes important when an Odoo Implementation Services moves from functional testing into production.

2. Profile before changing the code

Once the query pattern is suspected, measure it instead of guessing.

Odoo provides integrated profiling tools that can record SQL queries and Python execution traces. Its SQL collector is useful for identifying repeated queries generated by application code.

A focused test can look like this:

# Record SQL activity around the operation so query-count changes can be measured.
from odoo.tools.profiler import Profiler

with Profiler(collectors=['sql']):
    records._compute_count()

For automated tests, Odoo also provides query-count assertions that can help prevent performance regressions.

The useful measurements are:

• Number of SQL queries
• Slowest SQL operations
• Number of records processed
• Cold versus warm cache behavior
• Python execution time
• Database execution time

Odoo's performance documentation demonstrates the impact of prefetching with a 1,000-record example. Without appropriate prefetching, accessing two fields can result in 2,000 queries. With prefetching, the same access pattern can be reduced substantially.

The important point is that this is an Odoo documentation example, not a benchmark from our infrastructure.

3. Replace per-record queries with a batch operation

Once profiling confirms repeated queries, change the algorithm rather than simply increasing server resources.

For the count example, _read_group() can calculate the counts for the complete recordset:

# Batch the aggregation so PostgreSQL handles the recordset in one grouped operation.
def _compute_count(self):
    domain = [('related_id', 'in', self.ids)]

    counts_data = self.env['other.model']._read_group(
        domain,
        ['related_id'],
        ['__count'],
    )

    mapped_data = dict(counts_data)

    for record in self:
        record.count = mapped_data.get(record, 0)

The architectural difference is important.

The first implementation asks the database the same question repeatedly:

How many records belong to this ID?

The second implementation asks:

Give me the counts for all these IDs.

That shift from per-record processing to batch processing is one of the most important performance patterns when developing custom Odoo modules.

The same principle applies to record creation.

Avoid this:

# Creating records individually prevents the ORM from processing the entire batch together.
for name in names:
    self.env['my.model'].create({'name': name})

Prefer:

# Pass all values to create() so the ORM can process the records as a batch.
values = [{'name': name} for name in names]
records = self.env['my.model'].create(values)

The ORM supports creating multiple records by passing a list of dictionaries to create().

4. Use prefetching before reaching for raw SQL

The previous section addressed aggregation. Another common problem appears when custom code browses individual records.

For example:

# Browsing one ID at a time can prevent the ORM from using the complete recordset efficiently.
for record_id in record_ids:
    record = self.env['res.partner'].browse(record_id)
    print(record.name)

Instead, browse the complete recordset:

# Fetch the complete recordset first so Odoo can prefetch field values efficiently.
records = self.env['res.partner'].browse(record_ids)

for record in records:
    print(record.name)

Odoo maintains a record cache and uses prefetching to reduce repeated database reads.

This is also why raw SQL should not be the first optimization.

Direct SQL can be useful when a query is difficult to express through the ORM or when profiling demonstrates a specific database-level requirement. However, raw SQL bypasses ORM security rules and requires careful handling of flushing and cache consistency.

For most business workflows, fix the ORM access pattern first.

5. Add indexes only after identifying the query pattern

Batching reduces unnecessary queries. Indexes solve a different problem: expensive searches over large tables.

For example, if profiling shows that a custom workflow frequently searches a particular field, an index may be appropriate:

# Add an index only when profiling shows that this field is a frequent search condition.
reference = fields.Char(index=True)

An index can improve lookup performance, but adding indexes everywhere is not free.

Indexes consume storage and add overhead to inserts, updates, and deletes. The decision should therefore come from actual query patterns rather than a blanket rule to index every custom field.

This is where database design becomes part of the wider Odoo Implementation Services.

First identify the expensive query. Then determine whether batching, prefetching, an algorithmic change, or an index addresses the actual bottleneck.

For teams evaluating the wider architecture around ERP, integrations, and custom applications, the Oodles engineering ecosystem provides context around the broader technologies and solutions that can work alongside Odoo.

Real-World Application

The trade-off above becomes especially relevant when an Odoo implementation combines business automation with external integrations.

We encountered this class of problem while working on CaptionLabs, an ERP system covering work orders, assignments, reviews, billing, payments, freelancers, and employee time cards.

The project involved Odoo Implementation Services with accounting automation and QuickBooks integration. In an ERP environment like this, a single user action can touch several related records and business workflows.

That makes ORM access patterns important.

A customization that performs repeated searches can increase database work as transaction volume and record counts grow. Instead of immediately moving to raw SQL or larger infrastructure, the implementation approach should first measure the query pattern and then optimize the ORM operations.

The documented Odoo Implementation Services prefetch example gives a useful illustration of the underlying principle: repeated individual field access can create thousands of queries across a 1,000-record dataset, while appropriate prefetching can substantially reduce database round trips.

For production projects, however, actual before-and-after query counts and request latency should be measured in the target environment rather than presenting documentation examples as project benchmarks.

• Batch ORM operations first. A loop containing search_count(), search(), or create() can multiply database work as record volume increases.
• Profile before optimizing. SQL and Python profiling can reveal whether the bottleneck comes from database work, application code, or repeated small queries.
• Use recordsets deliberately. Browsing and reading records as a batch allows Odoo's cache and prefetching mechanisms to work more effectively.
• Treat raw SQL as a targeted tool. It can solve specific performance problems, but it bypasses ORM security behavior and requires cache and flush considerations.
• Index based on evidence. An index should correspond to a real query pattern and measurable database cost.

If you have encountered a similar Odoo ORM bottleneck, share the query pattern and record volume you measured. If you are planning a customized Odoo Implementation Services and want to discuss the technical requirements, talk to the Oodles team.

FAQ

What should I check first during Odoo Implementation Services?

Start with the business workflow, then inspect the ORM operations generated by custom code. Repeated queries inside recordset loops are an important early performance signal.

Does Odoo automatically optimize every ORM loop?

No. Odoo provides caching, prefetching, and batch-oriented APIs, but custom code can still defeat those mechanisms by performing database operations for individual records.

When should I use raw SQL in Odoo?

Use it for cases where the ORM cannot express the required query efficiently or where profiling demonstrates a specific need. Account for security rules, flushing, and cache consistency before doing so.

How can I design an Odoo implementation for long-term growth?

Start from specific business requirements, then define record relationships, access rules, integration boundaries, and expected data volumes. This helps your team effectively leverage Odoo for long-term success without optimizing only for the initial dataset.