Optimize the very inefficient database queries in the deviation list view

[markkuriekkinen]

#1122 presented performance issues in the deviations list page. This new issue focuses on specific performance issues.

Using the Django Debug toolbar, we have noticed that the database queries in the deviations list page include some very inefficient parts.

• a-plus/deviations/templates/deviations/list_dl.html

  Line 66 in 8f85614

  {{ deviations.0.exercise.course_module }}

  • rendering the module's name causes new queries for each record because it needs to load the course instance for the module numbering settings (that set Arabic or Roman numerals). Has select_related() or prefetch_related() been used incorrectly since the code tries to prefetch the course instances?

  • a-plus/deviations/viewbase.py

    Lines 254 to 265 in 8f85614

    	ordered_deviations = (
    	all_deviations
    	.select_related(
    	'submitter', 'submitter__user',
    	'granter', 'granter__user',
    	'exercise', 'exercise__course_module',
    	)
    	# parent is prefetched because there may be multiple ancestors, and
    	# they are needed for building the deviation's URL.
    	.prefetch_related('exercise__parent')
    	.order_by('submitter', 'exercise__course_module')
    	)

  • does the variable in the template benefit from the prefetching in the queryset or is the queryset's cache somehow lost when the template is rendered in this case? Does the variable refer to the original queryset anymore at that stage?

• a-plus/deviations/viewbase.py

  Lines 27 to 29 in 8f85614

  	all_deviations = self.deviation_model.objects.filter(
  	exercise__course_module__course_instance=self.instance
  	)

  • when all deviations in the course are queried, the query includes a lot of unnecessary attributes. Loading them from the database consumes a lot of resources. Especially the large JSON fields in the exercise model consume a lot of bandwidth even though those attributes are not used at all in the deviations list.
  • Unwanted attributes can be avoided with the Django queryset methods only() and defer().
  • exercise.exercise_info is one of the unnecessary attributes in this case.

[ihalaij1]

Reopened since the page still doesn't load quickly enough.