How to improve slow django view involving two related models "through" an intermediate?

I'm trying to create a view that outputs a 2D table relating two models "through" a third intermediate model. It runs fine on relatively small querysets but becomes increa开发者_StackOverflow中文版singly slow for larger ones. Is there a better way of implementing the view to speed it up?

I've given an analogous version of my models and view for simplicity but if needed I can post the actual models/view if they could be a cause for a slowdown. Please forgive me if I've included minor errors, the actual view/models work.

Thank you.

Desired output:

artist                 The Foo Bars       FUBAR     Bas Bix
Joe Blow                 5/10/1975                 12/7/2010
Fred Noname             12/12/2012      10/2/2010   
Smith John               2/2/2002       

Analogous models:

class Person(models.Model):
    name = models.CharField(max_length=128)

class Group(models.Model):
    name = models.CharField(max_length=128)
    members = models.ManyToManyField(Person, through='Membership', related_name="group")

class Membership(models.Model):
    person = models.ForeignKey(Person, related_name="membership")
    group = models.ForeignKey(Group, related_name="membership")

    date_joined = models.DateField()
    invite_reason = models.CharField(max_length=64)

Analogous view:

people = Person.objects.all().select_related()
groups = Group.objects.all().select_related()
group_names = [group.name for group in groups]
table = [["artist"] + group_names]
for person in people:
    row = [person.name]
    for group in groups:
        membership = person.membership.all() & group.membership.all()
        if membership:
            membership = membership[0]
            row.append(membership.date_joined)
        else:
            row.append("")
    table.append(row)

It's important to answer why you're view is slow based on output from tools like profiling and django-debug-toolbar. It's hard to tell you how to fix it without knowing where the slowdown is.

In this case since we're dealing with a manytomany relationship I assume in that the slowdown is due to a huge number of queries. In order to solve that you need to remember how manytomany's are represented in the database (but you seem to understand that). In brief there is no direct relationship between person and group and instead a membership has both a person and a group.

That means that you can't use select_related to cut the number of queries down. The correct approach is going to come down to querying the Membership table instead of the Person table. The correct approach is going to depend on the particulars of your situation. If what you're really after is membership data you may be able to just simply go Membership.objects.all().select_related() (or some variation). If you're after people data do something like:

 memberships = Membership.objects.all()
 membership_dict = {}
 for m in membership:
      try:
          membership_dict[m.person].append(m)
      except KeyError:
          membership_dict[m.person] = [m,]
 people = Person.objects.all()
 for p in people:
      p.groups = membership_dict[p]

This will cut what could be hundreds or thousands of queries down to 2 queries. The cost of which is that the python code is slightly less efficient.

Bear in mind I'm simply assuming that a large number of queries is your problem, you could have other major issues that I can't see; such as needing an index, cpu intensive python, very large tables, etc.