CODEWITHSUNDEEP
pythondjangodjango-rest-frameworkdjango-views
dev-vsg
dev-vsg

Reputation: 97

Enhancing a queryset's performance

I'm learning DRF and experimenting with a queryset. I'm trying to optimize to work as efficiently as possible. The goal being to get a list of grades for active students who are majoring in 'Art'.

Based on database optimization techniques, I've ran some different updates and don't see a difference when I look at the Time returned via the console's Network tab. I DO however, see less logs in the Seq scan when I run the .explain() method on the model filtering. Am I accomplishing anything by doing that?

For example: Grades.objects.filter(student_id__in=list(student_list)).order_by()

Anything else I can do to improve the below code that I might be missing? - Outside of adding any Foreign or Primary key model changes.

class GradeViewSet(viewsets.ModelViewSet):
    serializer_class = GradesSerializer

    def retrieve(self, request, *args, **kwargs):
        active_students = Student.objects.filter(active=True)
        student_list = active_students.filter(major='Art').values_list('student_id')
        queryset = Grades.objects.filter(student_id__in=student_list)

        serializers = GradesSerializer(queryset, many=True)
        return Response(serializers.data)

SQL query I'm attempting to create in Django.

select * from app_grades g
join app_students s on g.student_id = s.student_id
where s.active = true and s.major = 'Art'

Upvotes: 0

Views: 58

Answers (2)

ivissani
ivissani

Reputation: 2664

Your code will execute two separate database queries, I suggest that you try the following query instead:

queryset = Grades.objects.filter(student__active=True, student__major='Art')

this code will retrieve the exact same records but performing only one query with the appropriate JOIN clause.

You probably want to take a look at this part of the documentation.

Because of the lack of model relations that forbids the use of lookups I suggest that you use an Exists subuery. In this specific case the query will be as follows:

queryset = Grades.objects.annotate(student_passes_filter=Exists(
    Student.objects.filter(id=OuterRef('student_id'), active=True, major='Art')
)).filter(student_passes_filter=True)

You will need to import Exists and OuterRef. Note that these are available from Django 1.11 onwards.

Upvotes: 2

vctrd
vctrd

Reputation: 518

You should probably regroup those lines to reduce the number of queries:

active_students = Student.objects.filter(active=True)
student_list = active_students.filter(major='Art').values_list('student_id')

Into:

active_students = Student.objects.filter(active=True, major=‘Art’)

And converting to list then

Upvotes: 1

Related Questions