Django ORM - MySQL query count optimization for related models

Question

I have two related models, that looks like below:

class Enterprise(models.Model):
    id = models.AutoField(primary_key=True)
    subsystem_id = models.IntegerField()
    name = models.CharField(max_length=255, unique=True)
    modif_date = models.DateTimeField(auto_now=True)
    active = models.BooleanField(default=True)


class Project(models.Model):
    id = models.AutoField(primary_key=True)
    subsystem_id = models.IntegerField()
    name = models.CharField(max_length=255)
    modif_date = models.DateTimeField(auto_now=True)
    enterprise = models.ForeignKey('Enterprise'
    on_delete = CASCADE)
    active = models.BooleanField(default=True)

In my view, need to get all active Enterprises and list them. I'm doing it this way:

enterprise_list = Enterprise.objects.annotate(project_count=Count('project')).filter(
    Q(active=True) | Q(subsystem_id=-1), project_count__gt=0
)

serializer = EnterpriseSerializer(enterprise_list, many=True)

Then, my serializer is displaying project list with some additional query:

class EnterpriseSerializer(serializers.ModelSerializer):
    id = serializers.IntegerField(required=False)
    name = serializers.CharField(max_length=255, required=False)
    project_list = serializers.SerializerMethodField()

    def get_project_list(self, enterprise):
        project_list = Project.objects.filter(Q(active=True) | Q(subsystem_id=-1),
                                              enterprise=enterprise)
        serializer = ProjectSerializer(project_list, many=True)
        return serializer.data

    class Meta:
        model = Enterprise
        fields = ('id', 'name', 'project_list')

This code is working fine, but it has very serious issue - performance. The first query for Enterprise returns list of ~1500 object. Then, for every object, serializer executes single query to fetch additional data for project which results to ~1500 queries.

I've tried prefetch_related and select_related but either I'm doing something wrong or it doesn't work in my case.

In the other hand I can get list of project first. This could eliminate my count annotation. But I should group them by enterprise, but as far as I know Django ORM for MySQL doesn't support such operations. I don't think parse the data in python and pass it to serializer as a dict is a good idea.

Can you give me some tips how to limit queries in my case? Maybe prefetch/select_related will be helpful in my case, but how to use them properly here? I'm using MySQL database.

Pranav Aggarwal · Accepted Answer · 2017-10-22 23:28:19Z

1

You can use prefetch_related the following way:

from django.db.models import Prefetch

enterprise_list = Enterprise.objects.annotate(project_count=Count('project')).filter(
    Q(active=True) | Q(subsystem_id=-1), 
    project_count__gt=0).prefetch_related(
        Prefetch('project_set', 
            queryset=Project.objects.filter(Q(active=True) | Q(subsystem_id=-1)), 
            to_attr='projects'
        )
    )

serializer = EnterpriseSerializer(enterprise_list, many=True)

In serializer.py

class EnterpriseSerializer(serializers.ModelSerializer):
    ...

    def get_project_list(self, enterprise):
        project_list = enterprise.projects
        serializer = ProjectSerializer(project_list, many=True)
        return serializer.data

answered Oct 22, 2017 at 23:28

Pranav Aggarwal

6454 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Djent Over a year ago

Oh, so I've used prefetch_related completely wrong. Thanks for clarifying.

Collectives™ on Stack Overflow

Django ORM - MySQL query count optimization for related models

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related