CODEWITHSUNDEEP
pythondjango
Kritz
Kritz

Reputation: 7331

Reducing the number of queries in Django

I'm creating a Django app that tracks awards that a person receives. Below is a simplified representation of two models that I have:

class AwardHolder(models.Model):
    name = models.CharField()

    def get_total_awards(self):
        entries = self.award_set.all()
        calc = entries.aggregate(sum=Sum('units_awarded'))
        return calc.get('sum') or 0


class Award(models.Model)
    date_awarded = models.DateField()
    units_awarded = models.IntegerField()
    award_holder = models.ForeignKey(AwardHolder)

I created a summary page for the award holder where he can see his total awards. I use the get_total_awards function above. That all works well, but then I created an overview page to display the total awards per award holder. I use the function below to get the total awards per award holder:

def get_all_awards():
    qs = AwardHolder.objects.all()
    awards = []
    for ah in qs:
        awards.append((ah, ah.get_total_awards())
    return awards

This generates a large number of queries since it hits the db every loop. Is there a way I can use prefetch_related or some other db trick to reduce the number of db queries, without having to rewrite get_all_awards()?

The actual code I use has a lot more fields and is more complicated than this example. There's about 10 functions similar to get_all_awards(), so rewriting it will be quite some work. However, for 100 award holders my code generated a whopping 18000 queries, so I know I have to fix this somehow.

Upvotes: 1

Views: 203

Answers (2)

Moses Koledoye
Moses Koledoye

Reputation: 78554

get_all_awards does not necessarily have to call get_total_awards. You're repeating the same query for each entity instead of using a functionality Django already provides: annotate.

You can modify get_all_awards to use annotate and use get_total_awards only when the award applies to a single entity.

def get_all_awards():
    qs = AwardHolder.objects.annotate(sum=Sum('award__units_awarded'))
    awards = []
    for ah in qs:
        awards.append((ah, ah.sum))
    return awards

You may even drop the for loop and use the result from the queryset qs directly.

So you get the advantage of an optimized query when multiple objects and their related objects are being fetched, as opposed to running individual queries for each entity.

Upvotes: 1

OregonTrail
OregonTrail

Reputation: 9049

Use a model manager to always annotate AwardHolders

class AwardHolderQuerySet(models.QuerySet):
    def all(self):
        return self.annotate(Count('award__units_awarded'))

class AwardHolder(models.Model):
    name = models.CharField()
    objects = AwardHolderQuerySet.as_manager()
    ...

Upvotes: 1

Related Questions