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

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

i'm trying create view outputs 2d table relating 2 models "through" 3rd intermediate model. runs fine on relatively little querysets becomes increasingly slow larger ones. there improve way of implementing view speed up?

i've given analogous version of models , view simplicity if needed can post actual models/view if cause slowdown. please forgive me if i've included minor errors, actual view/models work.

thank you.

desired output:

artist 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="lang-py prettyprint-override">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") grouping = models.foreignkey(group, related_name="membership") date_joined = models.datefield() invite_reason = models.charfield(max_length=64)

analogous view:

class="lang-py prettyprint-override">people = person.objects.all().select_related() groups = group.objects.all().select_related() group_names = [group.name grouping in groups] table = [["artist"] + group_names] person in people: row = [person.name] grouping 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 of import reply why you're view slow based on output tools profiling , django-debug-toolbar. it's hard tell how prepare without knowing slowdown is.

in case since we're dealing manytomany relationship assume in slowdown due huge number of queries. in order solve need remember how manytomany's represented in database (but seem understand that). in brief there no direct relationship between person , grouping , instead membership has both person , group.

that means can't utilize select_related cutting number of queries down. right approach going come downwards querying membership table instead of person table. right approach going depend on particulars of situation. if you're after membership info may able go membership.objects.all().select_related() (or variation). if you're after people info like:

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

this cutting hundreds or thousands of queries downwards 2 queries. cost of python code less efficient.

bear in mind i'm assuming big number of queries problem, have other major issues can't see; such needing index, cpu intensive python, big tables, etc.

django django-views