Optimizing API Response Times in Odoo: Integration Performance

GET /api/sale.order?partner_id=123

Response:
{
    "id": 1,
    "name": "SO00001",
    "partner_id": 123,
    "order_date": "2025-01-15",
    "amount_total": 1500,
    ... (50 more fields)
    "notes": "Long text field with 10KB data",
    "description": "More long text",
    "chatter_ids": [1, 2, 3, 4, ...],  # Loads all messages
    "attachment_ids": [1, 2, 3, 4, ...],  # Loads all files
}

Payload: 1.2MB
Time: 500ms
Database: 50+ field loads

GET /api/sale.order?partner_id=123&fields=id,name,order_date,state,amount_total

Response:
{
    "id": 1,
    "name": "SO00001",
    "order_date": "2025-01-15",
    "state": "sale",
    "amount_total": 1500
}

Payload: 150 bytes
Time: 15ms
Database: 5 field loads

from odoo import http
from odoo.addons.web.controllers.main import DataSet

class OrderAPI(http.Controller):
    @http.route('/api/sale.order', auth='user', type='json')
    def get_orders(self, **kwargs):
        """Get orders with optional field selection."""
        # Get parameters
        partner_id = kwargs.get('partner_id')
        fields = kwargs.get('fields', [])  # Caller specifies fields
        limit = int(kwargs.get('limit', 20))
        offset = int(kwargs.get('offset', 0))
        
        # Build domain
        domain = []
        if partner_id:
            domain.append(('partner_id', '=', int(partner_id)))
        
        # Search orders
        orders = self.env['sale.order'].search(
            domain,
            limit=limit,
            offset=offset
        )
        
        # If no fields specified, use defaults
        if not fields:
            fields = ['id', 'name', 'state', 'amount_total']
        
        # Return only specified fields
        return orders.read(fields)

# Get all fields (slow)
curl "https://your-odoo.com/api/sale.order?partner_id=123"

# Get only needed fields (fast)
curl "https://your-odoo.com/api/sale.order?partner_id=123&fields=id,name,state,amount_total"

GET /api/sale.order?state=done&order_date=2025-01

# Database scans all orders sequentially: 5 seconds

GET /api/sale.order?state=done&order_date_start=2025-01-01&order_date_end=2025-01-31

# Database uses index: 0.05 seconds
# 100x faster!

class SaleOrder(models.Model):
    _inherit = 'sale.order'
    
    # Frequently filtered in API
    state = fields.Selection(index=True)
    partner_id = fields.Many2one(index=True)
    order_date = fields.Date(index=True)

CREATE INDEX idx_sale_order_state ON sale_order(state);
CREATE INDEX idx_sale_order_partner ON sale_order(partner_id);
CREATE INDEX idx_sale_order_date ON sale_order(order_date);
CREATE INDEX idx_sale_order_composite 
  ON sale_order(state, order_date DESC);

@http.route('/api/sale.order', auth='user', type='json')
def get_orders(self, **kwargs):
    """Optimized order search with filtering."""
    domain = []
    
    # Add filters only if provided
    if kwargs.get('state'):
        domain.append(('state', '=', kwargs['state']))
    if kwargs.get('partner_id'):
        domain.append(('partner_id', '=', int(kwargs['partner_id'])))
    if kwargs.get('date_start'):
        domain.append(('order_date', '>=', kwargs['date_start']))
    if kwargs.get('date_end'):
        domain.append(('order_date', '<=', kwargs['date_end']))
    
    # Pagination (critical!)
    limit = min(int(kwargs.get('limit', 50)), 1000)  # Max 1000
    offset = int(kwargs.get('offset', 0))
    
    # Fields
    fields = kwargs.get('fields', [
        'id', 'name', 'state', 'amount_total'
    ]).split(',') if isinstance(kwargs.get('fields'), str) else []
    
    orders = self.env['sale.order'].search(
        domain,
        limit=limit,
        offset=offset
    )
    
    return orders.read(fields)

@http.route('/api/shipping/orders', auth='none', type='json', csrf=False)
def get_shipping_orders(self):
    """Get all orders for shipping."""
    # Load ALL orders with ALL fields
    orders = self.env['sale.order'].search([
        ('state', '=', 'sale')
    ])  # Loads 10,000 orders!
    
    # Return everything
    return [
        {
            'id': o.id,
            'name': o.name,
            'customer': o.partner_id.name,
            'email': o.partner_id.email,
            'phone': o.partner_id.phone,
            'address': o.partner_id.street,
            'city': o.partner_id.city,
            'state': o.state,
            'amount_total': o.amount_total,
            'items': [
                {
                    'product': line.product_id.name,
                    'qty': line.product_qty,
                    'price': line.price_unit,
                    'sku': line.product_id.default_code,
                    'barcode': line.product_id.barcode,
                }
                for line in o.order_line
            ],
            'notes': o.note,
            'attachment_ids': o.attachment_ids.mapped('id'),
        }
        for o in orders
    ]
    
    # Payload: 50MB
    # Time: 400ms
    # Database: 1,000+ queries

@http.route('/api/shipping/orders', auth='none', type='json', csrf=False)
def get_shipping_orders(self):
    """Get orders ready for shipping (optimized)."""
    # 1. Get only orders modified since last call
    last_sync = self.env['ir.config_parameter'].sudo().get_param(
        'shipping_last_sync', '2000-01-01'
    )
    
    # 2. Search with specific fields needed
    orders = self.env['sale.order'].search_read(
        domain=[
            ('state', '=', 'sale'),
            ('write_date', '>', last_sync),  # Only new/updated
        ],
        fields=[
            'id', 'name', 'state', 'amount_total',
            'partner_id', 'order_line',  # Minimal fields
        ],
        limit=100,  # Pagination
        order='write_date DESC'
    )
    
    # 3. Prepare minimal response
    result = []
    for order in orders:
        # Prefetch related data once
        order_obj = self.env['sale.order'].browse(order['id'])
        
        # Get lines with specific fields
        lines = order_obj.order_line.read([
            'product_id', 'product_qty'
        ])
        
        result.append({
            'id': order['id'],
            'name': order['name'],
            'state': order['state'],
            'customer_id': order['partner_id'][0],
            'amount_total': order['amount_total'],
            'items': [
                {
                    'product_id': line['product_id'][0],
                    'qty': line['product_qty'],
                }
                for line in lines
            ],
            'updated_at': order_obj.write_date.isoformat(),
        })
    
    # 4. Update sync timestamp
    self.env['ir.config_parameter'].sudo().set_param(
        'shipping_last_sync', fields.Datetime.now()
    )
    
    return result
    
    # Payload: 150KB
    # Time: 15ms
    # Database: 5 queries

from odoo.tools import ormcache

class ProductAPI(http.Controller):
    @http.route('/api/product', auth='none', type='json', csrf=False)
    def get_products(self, **kwargs):
        """Get products (cached)."""
        fields = ['id', 'name', 'default_code', 'list_price']
        products = self.env['product.product'].search_read(
            domain=[('active', '=', True)],
            fields=fields,
            limit=10000
        )
        return products

# Or with explicit caching
@ormcache()
def get_product_list(self):
    """Cached product list."""
    return self.env['product.product'].search_read(
        domain=[('active', '=', True)],
        fields=['id', 'name', 'list_price'],
    )

@http.route('/api/product', auth='none', type='json', csrf=False)
def get_products(self):
    """API with cache headers."""
    response = self.env['ir.http'].get_response()
    products = self.env['product.product'].search_read(
        domain=[('active', '=', True)],
        fields=['id', 'name', 'list_price'],
    )
    
    # Cache for 1 hour
    response.headers['Cache-Control'] = 'public, max-age=3600'
    response.data = json.dumps(products)
    return response

from functools import wraps
from datetime import datetime, timedelta

def rate_limit(calls_per_minute=60):
    """Rate limiter decorator."""
    def decorator(f):
        @wraps(f)
        def wrapper(*args, **kwargs):
            request = http.request.httprequest
            client_ip = request.remote_addr
            
            # Get rate limit data
            config = self.env['ir.config_parameter'].sudo()
            key = f'api_rate_{client_ip}'
            data = config.get_param(key, '0,0')
            count, timestamp = map(int, data.split(','))
            now = int(datetime.now().timestamp())
            
            # Reset if minute passed
            if now - timestamp > 60:
                count = 0
                timestamp = now
            
            # Check limit
            if count >= calls_per_minute:
                return {'error': 'Rate limit exceeded'}, 429
            
            # Update count
            config.set_param(key, f'{count + 1},{timestamp}')
            
            return f(*args, **kwargs)
        return wrapper
    return decorator

@http.route('/api/sale.order', auth='none', type='json', csrf=False)
@rate_limit(calls_per_minute=60)
def get_orders(self):
    """Rate limited API endpoint."""
    return self.env['sale.order'].search_read(
        domain=[('state', '=', 'done')],
        fields=['id', 'name', 'amount_total'],
        limit=50
    )

@http.route('/api/debug/stats', auth='user', type='json')
def get_api_stats(self):
    """API performance statistics."""
    query = """
    SELECT 
        path,
        COUNT(*) as calls,
        AVG(EXTRACT(EPOCH FROM duration)) * 1000 as avg_ms,
        MAX(EXTRACT(EPOCH FROM duration)) * 1000 as max_ms
    FROM http_request_log
    WHERE path LIKE '/api/%'
        AND create_date > NOW() - INTERVAL '24 hours'
    GROUP BY path
    ORDER BY avg_ms DESC
    """
    self.env.cr.execute(query)
    return self.env.cr.dictfetchall()