Optimizing Database Queries in Django for the N+1 Problem

You're experiencing a classic N+1 query problem, where for each item in your list, additional queries are being executed to fetch related data. Let's address this issue by working at the appropriate level - the database level rather than Python level.

The Problem

Your current implementation has two methods that trigger additional queries:

1. get_stock_quantity(stock) - Queries for a specific stock item
2. get_stock_quantity_total() - Aggregates quantities across all stocks

Even though you're using prefetch_related("stock_items"), the methods are bypassing the prefetched data by creating new querysets with .filter() and .all().

Solution: Move Logic to the Database Level

1. Replace Instance Methods with Annotations

Instead of having methods that perform queries, annotate the queryset with the values you need:

PYTHONCopied
class StockBalanceView(ListView):
    model = models.Item
    template_name = 'stock_management/stock_balance.html'
    context_object_name = 'items'

    def get_queryset(self):
        qs = super().get_queryset()
        
        # Annotate with total quantity
        qs = qs.annotate(
            total_quantity=models.Sum('stock_items__quantity')
        )
        
        # If you need quantities for specific stocks, you can do:
        stocks = Stock.objects.all()
        for stock in stocks:
            qs = qs.annotate(**{
                f'quantity_in_stock_{stock.id}': models.Sum(
                    models.Case(
                        models.When(stock_items__stock=stock, then=models.F('stock_items__quantity')),
                        default=0,
                        output_field=models.PositiveIntegerField()
                    )
                )
            })
        
        # Add stocks to context for template access
        self.stocks = stocks
        
        return qs
    
    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['stocks'] = getattr(self, 'stocks', Stock.objects.all())
        return context

2. Update Your Template

In your template, instead of calling methods:

HTMLCopied
<!-- Instead of -->
{{ item.get_stock_quantity_total }}
{{ item.get_stock_quantity(stock) }}

<!-- Use -->
{{ item.total_quantity }}
{{ item.quantity_in_stock_stock.id }}

Why Your Annotation Only Reduced Queries by Half

Your annotation likely only addressed one of the two methods (get_stock_quantity_total), but not the other method that takes a stock parameter. Each method was generating its own set of queries.

Alternative Approach: Use F Expressions for Better Performance

If you still need to use instance methods, modify them to use the prefetched data:

PYTHONCopied
class Item(models.Model):
    name = models.CharField()
    
    def get_stock_quantity(self, stock):
        # Use the prefetched cache
        for stock_item in self.stock_items.all():
            if stock_item.stock_id == stock.id:
                return stock_item.quantity
        return 0
    
    def get_stock_quantity_total(self):
        # Use the prefetched cache
        return sum(item.quantity for item in self.stock_items.all())

However, the annotation approach is still more efficient as it pushes the work to the database level.

Best Practices for Django Performance

1. Work at the appropriate level: Database operations are generally faster than Python operations
2. Retrieve everything at once: Use select_related() and prefetch_related() appropriately
3. Use annotations and aggregations: Let the database do the heavy lifting
4. Use F expressions: For updating values based on existing fields to avoid race conditions

By implementing these changes, you should see a significant reduction in database queries and improved performance.