Odoo Performance : Indexing, Savepoints, List of IDs vs Subqueries

In this tutorial, we explore Odoo Performance Indexing, Savepoints, and List of IDs vs Subqueries in Odoo while explaining each concept with clear examples and active code. We use these key phrases right away so that you can focus on performance optimization. Moreover, we will discuss how each of these techniques improves database operations in Odoo and why they are essential for scalable applications. In addition, we provide practical code samples, detailed explanations, and best practices. For more information about Odoo, visit Odoo Official Website.

---

Introduction

In modern business applications, performance matters. Odoo, a robust open-source framework for business apps, demands efficient coding practices to serve thousands of users and millions of records. Today, we dive deep into three crucial topics: Indexing, Savepoints, and List of IDs vs Subqueries. We cover how to optimize your code and manage your data queries in Odoo. We will use plain language and active voice throughout the tutorial. You will also find many transition words that help guide you step by step. This blog post will teach you how to reduce query time, avoid performance pitfalls, and write clean, maintainable code. Let’s begin by discussing each concept and then see real-world examples that you can copy and run in your Odoo environment.

---

---

Understanding Indexing in Odoo

Efficient indexing dramatically speeds up your queries. In Odoo, you work with large datasets. Hence, you must leverage indexing to filter and sort data quickly.

What is Indexing?

Indexing in a database allows the system to locate data faster by creating a structure that maps search keys to table rows. This technique reduces the time required to fetch records, especially when working with millions of records. Moreover, indexing ensures that the data retrieval process runs efficiently when you apply filters or domain searches.

Bad Practices: When Indexing is Absent

When you design a model in Odoo without an index on frequently queried fields, the database scans the entire table. This situation slows down the response time considerably.

For example, consider the following model without an index:

from odoo import models, fields

class SaleOrder(models.Model):
    _inherit = "sale.order"

    order_reference = fields.Char(string="Order Reference")

In this code, if you have millions of orders and you search by order_reference, the database will take a long time to retrieve the desired records. This inefficiency occurs because the system does not use an index to speed up the lookup process.

Good Practices: Enabling Indexing in Odoo

You can improve performance by adding an index to your fields. Odoo provides an index=True parameter for fields, which instructs the system to create an index at the database level.

Below is an improved version of the previous code:

from odoo import models, fields

class SaleOrder(models.Model):
    _inherit = "sale.order"

    order_reference = fields.Char(string="Order Reference", index=True)

With this change, the database uses an index to look up the order_reference field, which results in much faster query execution. Furthermore, if you want to enforce uniqueness along with indexing, you can add the unique=True attribute as follows:

order_reference = fields.Char(string="Order Reference", index=True, unique=True)

This modification ensures that each order reference remains unique while the database leverages the index for quick searches.

In summary, always add indexes on fields that are frequently used in search domains. This simple change can reduce query time dramatically and increase the overall efficiency of your Odoo application.

---

Exploring Savepoints in Odoo

Using savepoints in database transactions helps you manage errors effectively. In Odoo, you perform multiple operations within a single transaction. Savepoints let you roll back part of the transaction without discarding all the changes.

What are Savepoints?

Savepoints are markers in a transaction that allow you to partially roll back changes. When you use a savepoint, you isolate a portion of the transaction so that if something goes wrong, you can revert only that part rather than the entire transaction. This technique is particularly useful when performing batch operations where some parts may fail, but you still want to commit successful ones.

Bad Practices: Lack of Savepoints

Without savepoints, if an error occurs during a transaction, the whole transaction fails. For example, consider the following code:

from odoo import models

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def process_orders(self):
        for order in self:
            order.confirm_order()
            order.create_invoice()  # If an error occurs here, all orders lose their updates.

In this snippet, if one invoice creation fails, all previous operations in the loop are rolled back. This outcome is not ideal for batch processing and may lead to data inconsistency.

Good Practices: Using Savepoints Effectively

To handle errors gracefully, you should use savepoints. Savepoints allow you to isolate each operation within a loop, so if one fails, you only roll back that specific part.

Here is an improved version using savepoints:

from odoo import models, api

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def process_orders(self):
        cr = self.env.cr  # Get the current database cursor
        for order in self:
            order.confirm_order()
            try:
                # Set a savepoint before creating the invoice
                cr.execute("SAVEPOINT order_processing")
                order.create_invoice()
            except Exception as e:
                # Roll back to the savepoint if an error occurs
                cr.execute("ROLLBACK TO SAVEPOINT order_processing")
                _logger.error(f"Failed to create invoice for order {order.id}: {e}")

In this code, we use the database cursor (cr) to create a savepoint before attempting to create an invoice. If invoice creation fails, the code rolls back to the savepoint. Consequently, only the failed operation is undone while previous successful operations remain intact.

By using savepoints, you ensure that your batch operations are robust and that errors do not compromise the entire transaction. This method is essential when working on complex data import tasks or batch processing in Odoo.

---

List of IDs vs Subqueries in Odoo

Handling large sets of data efficiently is critical in Odoo. Developers often face the choice between using a list of IDs or writing subqueries. Each method has its trade-offs, and understanding these will help you write better-performing code.

Understanding the Difference

A list of IDs involves fetching a list of record IDs first and then performing operations based on that list. A subquery is a query embedded within another query. While subqueries can be powerful, they sometimes lead to performance issues if not used correctly.

Using a list of IDs is often more efficient in Odoo because you can retrieve and process the data in batches. Moreover, Odoo’s ORM offers methods like read_group() to help aggregate data without resorting to subqueries that run repeatedly.

Bad Practices: Relying on Subqueries in Loops

Consider the following example, which uses subqueries in a loop:

from odoo import models

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def get_top_customers(self):
        # This code runs a subquery for each order, leading to N+1 problems
        return self.env['res.partner'].search([
            ('id', 'in', self.env['sale.order'].search([('amount_total', '>', 1000)]).mapped('partner_id.id'))
        ])

This approach forces the system to run a query for each order to extract customer IDs, causing significant slowdowns when processing large datasets.

Good Practices: Using List of IDs and read_group()

A better strategy is to use a list of IDs with the read_group() method. This method performs the aggregation in a single database query and returns a list of grouped results.

Below is a revised version that uses list of IDs:

from odoo import models

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def get_top_customers(self):
        # Aggregate data using read_group for improved performance
        order_data = self.env['sale.order'].read_group(
            [('amount_total', '>', 1000)],
            ['partner_id'],
            ['partner_id']
        )
        # Create a list of customer IDs from the aggregated data
        partner_ids = [data['partner_id'][0] for data in order_data]
        # Use browse to get records in one go
        return self.env['res.partner'].browse(partner_ids)

This code efficiently aggregates the required customer IDs in one database call. By using read_group(), you minimize the number of queries and avoid the N+1 query problem. You also ensure that your code runs faster and scales better with more data.

---

Additional Performance Tips in Odoo

In addition to the three main topics, many other techniques can boost performance in Odoo. We now review a few more practices that align with the key principles from our video transcript.

Other Concepts: Functions as Properties and Hoisting Invariants

Functions as Properties

In Odoo, you often create computed fields that act like properties. You can use the @api.depends decorator to indicate dependencies. This way, the field updates automatically when its dependent fields change.

Example:

from odoo import models, fields, api

class ProductTemplate(models.Model):
    _inherit = "product.template"

    total_stock = fields.Float(
        string="Total Stock",
        compute="_compute_total_stock",
        store=True
    )

    @api.depends('stock_quant_ids.quantity')
    def _compute_total_stock(self):
        for record in self:
            record.total_stock = sum(record.stock_quant_ids.mapped('quantity'))

In this code, total_stock acts as a property. The system automatically recalculates it when the stock_quant_ids.quantity changes. This approach uses less memory and reduces redundant computations.

Hoisting Invariants

Hoisting invariants means moving calculations that do not change inside a loop to outside of it. This practice reduces the number of computations and speeds up processing.

Example:

def compute_answers(questions):
    result = expensive_computation()  # Move the invariant computation outside the loop
    for question in questions:
        question.answer = 42 + result

In this snippet, we calculate expensive_computation() once, instead of inside the loop. This simple move makes your code more efficient and easier to read.

---

Detailed Code Walkthrough

Now that we have introduced each concept with code examples, let us examine the code in detail. We explain each block line by line to help you understand how to implement these practices in your own Odoo modules.

Code Example for Indexing

Before:

from odoo import models, fields

class SaleOrder(models.Model):
    _inherit = "sale.order"

    order_reference = fields.Char(string="Order Reference")

Analysis:

• The order_reference field does not include indexing.
• When you search on this field, the database performs a full table scan.
• This approach slows down performance if you have many orders.

After:

from odoo import models, fields

class SaleOrder(models.Model):
    _inherit = "sale.order"

    order_reference = fields.Char(string="Order Reference", index=True)

Explanation:

• By adding index=True, we instruct the database to create an index.
• This change speeds up queries involving order_reference.
• The indexed field now retrieves data faster, which is crucial for scalability.

Code Example for Savepoints

Before:

from odoo import models

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def process_orders(self):
        for order in self:
            order.confirm_order()
            order.create_invoice()  # A failure here rolls back the entire transaction.

Analysis:

• The code does not use savepoints.
• An error in invoice creation affects all orders in the loop.
• This approach lacks robustness in batch processing.

After:

from odoo import models, api
import logging

_logger = logging.getLogger(__name__)

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def process_orders(self):
        cr = self.env.cr
        for order in self:
            order.confirm_order()
            try:
                cr.execute("SAVEPOINT order_processing")
                order.create_invoice()
            except Exception as e:
                cr.execute("ROLLBACK TO SAVEPOINT order_processing")
                _logger.error(f"Failed to create invoice for order {order.id}: {e}")

Explanation:

• We obtain the current cursor (cr) to manage savepoints.
• We set a savepoint before each critical operation.
• In case of error, we roll back only the faulty operation.
• This technique protects the integrity of the rest of the transaction.

Code Example for List of IDs vs Subqueries

Before (Using Subqueries in Loop):

from odoo import models

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def get_top_customers(self):
        return self.env['res.partner'].search([
            ('id', 'in', self.env['sale.order'].search([('amount_total', '>', 1000)]).mapped('partner_id.id'))
        ])

Analysis:

• This code uses a subquery inside a loop.
• It triggers multiple queries (N+1 problem).
• Performance degrades with a larger number of orders.

After (Using List of IDs):

from odoo import models

class SaleOrder(models.Model):
    _inherit = "sale.order"

    def get_top_customers(self):
        order_data = self.env['sale.order'].read_group(
            [('amount_total', '>', 1000)],
            ['partner_id'],
            ['partner_id']
        )
        partner_ids = [data['partner_id'][0] for data in order_data]
        return self.env['res.partner'].browse(partner_ids)

Explanation:

• We use read_group() to aggregate customer IDs in one query.
• We then build a list of IDs and browse them in one go.
• This method avoids the performance pitfalls of subqueries.
• The code runs efficiently even when processing many records.

---

Best Practices for Odoo Performance Optimization

Throughout this tutorial, we have seen how proper techniques can improve Odoo’s performance. Here, we summarize some best practices:

1. Always Index Frequently Queried Fields:
   Use index=True on fields that you often filter or search on. This change reduces query execution time.
2. Leverage Savepoints in Transactions:
   When processing multiple records, use savepoints to isolate parts of your transaction. This approach prevents a single failure from undoing all successful operations.
3. Favor List of IDs Over Subqueries:
   Use methods like read_group() to aggregate IDs. This technique minimizes the number of queries and enhances performance.
4. Use Computed Fields Wisely:
   Utilize @api.depends and store computed fields when necessary. Keep the logic lean to avoid heavy computations that run repeatedly.
5. Optimize Batch Operations:
   Always batch CRUD operations to avoid the N+1 query problem. Use list comprehensions to build data lists for bulk create or update operations.
6. Monitor and Profile Your Code:
   Use tools such as Odoo’s built-in logging and performance monitors to identify slow queries. Profiling helps you pinpoint bottlenecks in your code.
7. Follow the Odoo Framework Conventions:
   Stick to the Odoo ORM guidelines and leverage its methods (like search, browse, and read_group()) instead of writing raw SQL queries. This practice ensures better compatibility and easier maintenance.

---

Advanced Topics and Further Reading

Handling Complex Data Structures

When your application grows, you might need to optimize complex queries and handle large relational data. In such cases, consider:

• Caching Results:
  Cache frequently accessed data using Odoo’s caching mechanisms.
• Prefetching Data:
  Prefetch related records to reduce the number of queries during record processing.
• Minimizing External Requests:
  Move heavy external API calls outside of computed fields to ensure they do not block the database operations.

Transitioning from Development to Production

Before you deploy your Odoo application in production, review all database indices and transaction handling mechanisms. Transition smoothly by:

• Testing Under Load:
  Simulate high-load scenarios and check how your queries perform.
• Using Odoo’s Profiling Tools:
  Identify slow operations and optimize them.
• Regular Code Reviews:
  Review your code to ensure that all best practices are followed and that no inefficient queries are introduced over time.

External Resources

For more detailed discussions on database performance, visit these links:

• PostgreSQL Indexing Strategies
• Understanding Transaction Savepoints
• Odoo Developer Documentation

These resources provide in-depth knowledge that complements the techniques we have discussed in this tutorial.

---

Frequently Asked Questions (FAQ)

What is the benefit of indexing in Odoo?

Indexing speeds up the search and filter operations. It allows the database to locate records quickly without scanning the entire table, which is crucial for large datasets.

How do savepoints help in transaction management?

Savepoints let you roll back a part of a transaction instead of the whole transaction. This feature is vital when processing batch operations, as it isolates failures and preserves the integrity of successfully processed records.

Why should I prefer using a list of IDs over subqueries?

Using a list of IDs, especially with the read_group() method, minimizes the number of queries sent to the database. This approach avoids the N+1 query problem and leads to more efficient code execution.

How can I monitor performance issues in my Odoo modules?

You can use logging, performance profiling tools, and Odoo’s built-in monitoring features. Regular code reviews and testing under load also help identify and resolve performance issues.

---

Final Thoughts

By following the guidelines in this tutorial, you can significantly improve the performance of your Odoo applications. You now understand how to use Indexing, manage Savepoints, and decide between List of IDs vs Subqueries effectively. Each technique reduces overhead and increases the speed and reliability of your database operations.

Remember to use these techniques in combination with other best practices such as proper batching, caching, and profiling. Transition gradually and always test your changes to ensure that you do not inadvertently introduce new issues. With practice, you will write cleaner, faster, and more scalable code in Odoo.

As you refine your skills, revisit this tutorial and the external links provided to stay updated with the latest performance optimization strategies. You can also join the vibrant Odoo community for additional insights and support.

---

Conclusion and Best Practices Recap

In conclusion, you have learned the following key points:

• Indexing:
  • Apply indexing on fields that are frequently used in search operations.
  • Use index=True and, if necessary, unique=True for fields that require fast lookups and data integrity.
• Savepoints:
  • Use savepoints to manage transactions efficiently.
  • Isolate operations in loops using savepoints to avoid complete transaction failures.
• List of IDs vs Subqueries:
  • Prefer using a list of IDs along with the read_group() method over repetitive subqueries.
  • This method minimizes query overhead and avoids the N+1 query problem.

These practices, when implemented correctly, empower you to write highly efficient Odoo modules that scale well with your business needs.

We hope you find this tutorial helpful and that it inspires you to optimize your own Odoo projects. For further assistance or to share your experiences, feel free to comment below or join discussions on Odoo forums.

Happy coding and performance optimizing!

---

This blog post is provided as a detailed tutorial to help developers optimize their Odoo applications. The examples and explanations use active voice and clear language to ensure readability and practical application. For more advanced topics and ongoing updates, visit Odoo Official Website and subscribe to our newsletter.

---