Django Inline for ManyToMany generate duplicate queries

Question

I'm experiencing some major performing issue with my django admin. Lots of duplicate queries based on how many inlines that I have.

models.py

class Setting(models.Model):
    name = models.CharField(max_length=50, unique=True)

    class Meta:
        ordering = ('name',)

    def __str__(self):
        return self.name


class DisplayedGroup(models.Model):
    name = models.CharField(max_length=30, unique=True)
    position = models.PositiveSmallIntegerField(default=100)

    class Meta:
        ordering = ('priority',)

    def __str__(self):
        return self.name


class Machine(models.Model):
    name = models.CharField(max_length=20, unique=True)
    settings = models.ManyToManyField(
        Setting, through='Arrangement', blank=True
    )

    class Meta:
        ordering = ('name',)

    def __str__(self):
        return self.name


class Arrangement(models.Model):
    machine = models.ForeignKey(Machine, on_delete=models.CASCADE)
    setting = models.ForeignKey(Setting, on_delete=models.CASCADE)
    displayed_group = models.ForeignKey(
        DisplayedGroup, on_delete=models.PROTECT,
        default=1)
    priority = models.PositiveSmallIntegerField(
        default=100,
        help_text='Smallest number will be displayed first'
    )

    class Meta:
        ordering = ('priority',)
        unique_together = (("machine", "setting"),)

admin.py

class ArrangementInline(admin.TabularInline):
    model = Arrangement
    extra = 1


class MachineAdmin(admin.ModelAdmin):
    inlines = (ArrangementInline,)

If I have 3 settings added on inline form and 1 extra, I have about 10 duplicate queries

SELECT "corps_setting"."id", "corps_setting"."name", "corps_setting"."user_id", "corps_setting"."tagged", "corps_setting"."created", "corps_setting"."modified" FROM "corps_setting" ORDER BY "corps_setting"."name" ASC
- Duplicated 5 times

SELECT "corps_displayedgroup"."id", "corps_displayedgroup"."name", "corps_displayedgroup"."color", "corps_displayedgroup"."priority", "corps_displayedgroup"."created", "corps_displayedgroup"."modified" FROM "corps_displayedgroup" ORDER BY "corps_displayedgroup"."priority" ASC
- Duplicated 5 times.

Could someone please tell me what I'm doing wrong right here? I've spent 3 days trying to figure the problem out myself without luck.

The issue gets worse when I have about 50 settings inlines of a Machine, I will have ~100 queries.

Here is the screenshot

Answer 1

I've assembled a generic solution based on @makaveli's answer that doesn't seem to have problem mentioned in the comments:

class CachingModelChoicesFormSet(forms.BaseInlineFormSet):
    """
    Used to avoid duplicate DB queries by caching choices and passing them all the forms.
    To be used in conjunction with `CachingModelChoicesForm`.
    """

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        sample_form = self._construct_form(0)
        self.cached_choices = {}
        try:
            model_choice_fields = sample_form.model_choice_fields
        except AttributeError:
            pass
        else:
            for field_name in model_choice_fields:
                if field_name in sample_form.fields and not isinstance(
                    sample_form.fields[field_name].widget, forms.HiddenInput):
                    self.cached_choices[field_name] = [c for c in sample_form.fields[field_name].choices]

    def get_form_kwargs(self, index):
        kwargs = super().get_form_kwargs(index)
        kwargs['cached_choices'] = self.cached_choices
        return kwargs


class CachingModelChoicesForm(forms.ModelForm):
    """
    Gets cached choices from `CachingModelChoicesFormSet` and uses them in model choice fields in order to reduce
    number of DB queries when used in admin inlines.
    """

    @property
    def model_choice_fields(self):
        return [fn for fn, f in self.fields.items()
            if isinstance(f, (forms.ModelChoiceField, forms.ModelMultipleChoiceField,))]

    def __init__(self, *args, **kwargs):
        cached_choices = kwargs.pop('cached_choices', {})
        super().__init__(*args, **kwargs)
        for field_name, choices in cached_choices.items():
            if choices is not None and field_name in self.fields:
                self.fields[field_name].choices = choices

All you'll need to do is subclass your model from CachingModelChoicesForm and use CachingModelChoicesFormSet in your inline class:

class ArrangementInlineForm(CachingModelChoicesForm):
    class Meta:
        model = Arrangement
        exclude = ()


class ArrangementInline(admin.TabularInline):
    model = Arrangement
    extra = 50
    form = ArrangementInlineForm
    formset = CachingModelChoicesFormSet

Answer 2

EDIT 2020:

Check out the answer by @isobolev below who's taken this answer and improved on it to make it more generic. :)

---

This is pretty much normal behaviour in Django - it doesn't do the optimization for you, but it gives you decent tools to do it yourself. And don't sweat it, 100 queries isn't really a big problem (I've seen 16k queries on one page) that needs fixing right away. But if your amounts of data are gonna increase rapidly, then it's wise to deal with it of course.

The main weapons you'll be armed with are queryset methods select_related() and prefetch_related(). There's really no point of going too deeply into them since they're very well documented here, but just a general pointer:

• use select_related() when the object you're querying has only one related object (FK or one2one)

• use prefetch_related() when the object you're querying has multiple related objects (the other end of FK or M2M)

And how to use them in Django admin, you ask? Elementary, my dear Watson. Override the admin page method get_queryset(self, request) so it would look sth like this:

from django.contrib import admin

class SomeRandomAdmin(admin.ModelAdmin):
    def get_queryset(self, request):
        return super().get_queryset(request).select_related('field1', 'field2').prefetch_related('field3')    

EDIT: Having read your comment, I realise that my initial interpretation of your question was absolutely wrong. I do have multiple solutions for your problem as well and here goes that:

1. The simple one that I use most of the time and recommend: just replace the Django default select widgets with raw_id_field widgets and no queries are made. Just set raw_id_fields = ('setting', 'displayed_group') in the inline admin and be done for.

2. But, if you don't want to get rid of the select boxes, I can give some half-hacky code that does the trick, but is rather lengthy and not very pretty. The idea is to override the formset that creates the forms and specify choices for these fields in the formset so that they're only queried once from the database.

Here it goes:

from django import forms
from django.contrib import admin
from app.models import Arrangement, Machine, Setting, DisplayedGroup


class ChoicesFormSet(forms.BaseInlineFormSet):
    setting_choices = list(Setting.objects.values_list('id', 'name'))
    displayed_group_choices = list(DisplayedGroup.objects.values_list('id', 'name'))

    def _construct_form(self, i, **kwargs):
        kwargs['setting_choices'] = self.setting_choices
        kwargs['displayed_group_choices'] = self.displayed_group_choices
        return super()._construct_form(i, **kwargs)


class ArrangementInlineForm(forms.ModelForm):
    class Meta:
        model = Arrangement
        exclude = ()

    def __init__(self, *args, **kwargs):
        setting_choices = kwargs.pop('setting_choices', [((), ())])
        displayed_group_choices = kwargs.pop('displayed_group_choices', [((), ())])

        super().__init__(*args, **kwargs)

        # This ensures that you can still save the form without setting all 50 (see extra value) inline values.
        # When you save, the field value is checked against the "initial" value
        # of a field and you only get a validation error if you've changed any of the initial values.
        self.fields['setting'].choices = [('-', '---')] + setting_choices
        self.fields['setting'].initial = self.fields['setting'].choices[0][0]
        self.fields['setting'].empty_values = (self.fields['setting'].choices[0][0],)

        self.fields['displayed_group'].choices = displayed_group_choices
        self.fields['displayed_group'].initial = self.fields['displayed_group'].choices[0][0]


class ArrangementInline(admin.TabularInline):
    model = Arrangement
    extra = 50
    form = ArrangementInlineForm
    formset = ChoicesFormSet

    def get_queryset(self, request):
        return super().get_queryset(request).select_related('setting')


class MachineAdmin(admin.ModelAdmin):
    inlines = (ArrangementInline,)


admin.site.register(Machine, MachineAdmin)

If you find something that could be improved or have any questions, let me know.