DB querying performance optimization working with model methods.

Using Django Using the ORM
1

Hi everyone.
I’m having excessive DB calls number caused “n+1” problem whilce accesing instance’s method in template (which make some DB calls as well).
Will try to explaing in details:

Models :

class Stock(models.Model):
    title = models.CharField()
    type = models.ForeignKey(StockType)

class Item(models.Model):
    name = models.CharField()
    
    def get_stock_quantity(self, stock):
        stock_item = self.stock_items.filter(stock=stock).first()
        return stock_item.quantity if stock_item else 0

    def get_stock_quantity_total(self):
        """ Returns total quantity of Item among all stocks """
        items = self.stock_items.all().only("quantity")
        return items.aggregate(sum_qty=models.Sum('quantity'))\
                    .get("sum_qty") or 0

class StockItem(models.Model):
    item = models.ForeignKey(Item)
    stock = models.ForeignKey(Stock)
    quantity = models.PositiveIntegerField()

View:

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()

        return qs.prefetch_related("stock_items")

The methods get_stock_quantity and get_stock_quantity_total produces huge amount DB calls.
As I understand the flow: for every Item in queryset ORM performs DB call to fetch all StockItem instances related for this item. Howevert using .prefetch_related() for stock_items, stock_items__stock won’t reduce number of DB calls. Even after I set .only("quantity") did nothing in terms of performance.

In DjDT toolbar I’m getting somethin like:

+ **SELECT** ••• **FROM** "stock_management_stockitem" **WHERE** "stock_management_stockitem"."item_id" = 165
**406 similar queries.** **Duplicated 2 times.** 0.40

+ **SELECT** ••• **FROM** "stock_management_stockitem" **WHERE** "stock_management_stockitem"."item_id" = 165
**406 similar queries.** **Duplicated 2 times.**

Refactoring code with .annotate() reduces DB calls by half (to 203). Why half ?

What I’m missing here?

2

What is it that you’re really trying to calculate here?

Are you trying to calculate the sum of the StockItem.quantity field for each Item?
If so, you’re doing a lot more database work than necessary.
(If not, then can you clarify what it is that you’re trying to calculate?)

Also note: You haven’t posted the complete view here - or if it is the complete view, then you need to provide the template, because you aren’t showing anywhere that get_stock_quantity or get_stock_quantity_total are being called.

3

Hello, @KenWhitesell.
You are right. Method calculates the StockItem q-ty in each Stock through Item reverse relation.
I managed to reduce all queries produced by get_stock_quantity_total replacing method call with .annotate() and Sum. (query reduced from 406 to 5) but still having performance issue with get_stock_quantity.

View:

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()
        return qs.select_related("maker", "item_type")\
                 .annotate(
                     total=Sum('stock_items__quantity')
                 )

    def get_context_data(self, **kwargs):
        context = super().get_context_data()
        context['stocks'] = models.Stock.objects.all()\
                                                .select_related("stock_type", "entity")
        return context

Custom tag:

@register.filter
def get_stock_quantity(item: Item, stock) -> int:
    return item.get_stock_quantity(stock)

Template:

        <table class="table table-bordered sortable shadow-sm align-middle">
            <thead class="table-secondary align-middle">
                <tr class="text-center">
                    <th>Title</th>
                    <th>Maker</th>
                    <th>Category</th>

                    {% for stock in stocks %}
                    <th>
                        <span><a href="{{ stock.get_absolute_url }}" class="link-success" style="text-decoration: none;">{{ stock.name }}</a></span>
                        <br>
                        <span>{{stock.stock_type}} ({{stock.entity}})</span>
                    </th>
                    {% endfor %}

                    <th class="text-center">Total</th>
                </tr>
            </thead>
            <tbody>
                {% for item in items %}
                    <tr class="text-center">
                        <td class="text-start">{{ item.name }}</td>
                        <td>{{ item.maker }}</td>
                        <td>{{ item.item_type }}</td>
                        {% for stock in stocks %}
                            {% with quantity=item|get_stock_quantity:stock  %}
                            <td class="{% if quantity < 0 %}bg-warning bg-gradient{% endif %}">{{ quantity }}</td>
                            {% endwith %}
                        {% endfor %}
                        <td class="{% if item.total < 0 %}bg-warning bg-gradient{% endif %}">{{ item.total }}</td>
                    </tr>
                {% endfor %}
            </tbody>
        </table>

I guess there is a way with aggregation but can’t figure out for now how to associate Item with particular Stock in the table head. I guess Case/When the way but still no strict solution.

4

You should be able to reduce the number of queries by using a prefetch_related in both queries being executed to “pre-fetch” the related entries of the StockItem model.

Also, there may be some “notational” benefit if you defined the ManyToManyField between Stock and Item with StockItem as the through model.

Also, it looks like you’re iterating through all Stock for every Item - even if there’s no relationship between the two. Is that correct? (Is that the desired behavior?)