reduce number of DB-Queries

[kloetpatra]

Hi!
I have the following models:

class Player(models.Model):
    name = models.CharField('Name', max_length=40)
 
    def __unicode__(self):
        return self.name
 
    def goals(self):
        g = 0
        ms = Match.objects.select_related().filter( Q(opp1=self)|Q(opp2=self) )
        for m in ms:
            if (m.opp1==self):
                g += m.opp1_goals
            else:
                g += m.opp2_goals
        return g

    def matches_won(self):
        ms = Match.objects.filter( (Q(opp1=self) & Q(opp1_goals__gt=F('opp2_goals'))) |
            (Q(opp2=self) & Q(opp2_goals__gt=F('opp1_goals'))) ).count()
        return ms
 
    def matches_draw(self):
        ms = Match.objects.filter( (Q(opp1=self) & Q(opp1_goals__exact=F('opp2_goals'))) |
            (Q(opp2=self) & Q(opp2_goals__exact=F('opp1_goals'))) ).count()
        return ms

    def match_points(self):
        p = 0
        p += 3 * self.matches_won()
        p += 1 * self.matches_draw()
        return p

    def matches_played(self):
        return Match.objects.filter(Q(opp1=self) | Q(opp2=self)).count()


class Match(models.Model):
    class Meta:
        verbose_name_plural = "Matches"
    opp1 = models.ForeignKey(Player, related_name="player1")
    opp2 = models.ForeignKey(Player, related_name="player2")
    opp1_goals = models.IntegerField("Goals P1")
    opp2_goals = models.IntegerField("Goals P2")
 
    def __unicode__(self):
        return "%s %d:%d %s"%(self.opp1.name, self.opp1_goals, self.opp2_goals, self.opp2.name)

My problem is when want to display overall player statistics I get about 1200 DB-Queries.
This is because for each player there will be 5 Queries due to matches_won, matches_draw, goals, matches_played functions.
How is it possible to retrieve those "calculated properties" without having so much queries?

[Martin J. Laubach]

My problem is when want to display overall player statistics I get about 1200 DB-Queries.
This is because for each player there will be 5 Queries due to matches_won, matches_draw, goals, matches_played functions.

  First easy optimisation: keep  Match.objects.select_related().filter( Q(opp1=self)|Q(opp2=self)) in an instance variable and use that QuerySet as base of all your calculations. Do NOT add additional filters for matches_draw/matches_won/matches_played, instead iterate over the results and do the calculations in Python. That way the QuerySet result will be cached the first time you're iterating over it and won't result in any more database accesses later on.

   Cheers,

        mjl

[kloetpatra]

Wow thanks!
That's really going fast now.
I would have never come to that idea :)

One more question:
Is this how you meant to set the instance variable or did you mean in another way?

    def __init__(self, *args, **kwargs):
        super(Player, self).__init__(*args, **kwargs)
        self.matches = Match.objects.select_related().filter( Q(opp1=self) | Q(opp2=self) )

[Martin J. Laubach]

Is this how you meant to set the instance variable or did you mean in another way?

    def __init__(self, *args, **kwargs):
        super(Player, self).__init__(*args, **kwargs)
        self.matches = Match.objects.select_related().filter( Q(opp1=self) | Q(opp2=self) )

  Yes, that's what I meant.

        mjl

 

[Stephen Anto]

Hi,

Try to use Django aggregation,

example given on http://f2finterview.com/web/Django/14/ for Aggregation

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/-ki6lNP0IDAJ.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

--
Thanks & Regards

Stephen S

Website: www.f2finterview.com

Blog:      blog.f2finterview.com

Tutorial:  tutorial.f2finterview.com

Group:    www.charvigroups.com

[kloetpatra]

I think aggregation can't be used because the calculations are a bit more complex than just the sum/average/count/min/max of one field.