Multi tool use

I'm trying to optimize a DB query using prefetch_related without success.

prefetch_related

class Order(models.Model):
# some fields ...

@property
def last_operation(self) -> Optional['OrderOperation']:
try:
return self.orderoperation_set.latest()
except OrderOperation.DoesNotExist:
return None

@property
def total(self) -> Optional[Decimal]:
last_operation = self.last_operation
return last_operation.total if last_operation else None

class OrderOperation(TimeStampable, models.Model):
order = models.ForeignKey(Order)
total = DecimalField(max_digits=9, decimal_places=2)


orders = Order.objects.prefetch_related('orderoperation_set') # There are 1000 orders
result = sum([order.total for order in orders])
len(connection.queries)
>>> 1003


As we can see, there is one query per order.total, so 1000 queries, that makes the whole request very bad, with performance linear to the number of orders.

order.total

Trying to understand why this is happening, I found this in the prefetch_related Django doc:

Remember that, as always with QuerySets, any subsequent chained methods which imply a different database query will ignore previously cached results, and retrieve data using a fresh database query.

So, it seems normal that calling latest() each time run a new query.

latest()

How would you do to improve performance in this case? (making a few queries instead of N, with N is the number of orders).

2 Answers
2

Since OrderOperation only contains a single relevant field, total, a better approach would be to annotate the total of the latest operation in the original query using a subquery:

total

from django.db.models import OuterRef, Subquery
newest = OrderOperation.objects.filter(post=OuterRef('pk')).order_by('-created_at') # or whatever the timestamp field is
orders = Order.objects.annotate(newest_operation_total=Subquery(newest.values('total')[:1]))


I'm posting an answer here, can you tell me if this makes sense or not?

Instead of calling latest(), what if I simply get the first item in my queryset with [0] (or the last with len(qs)-1, supposing that order_operations are already ordered?

latest()

[0]

len(qs)-1

@property
def last_operation(self) -> Optional['OrderOperation']:
try:
qs = self.orderoperation_set.all()
return qs[len(qs) - 1]
except IndexError:
return None


orderoperation_set.all()

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.