Django view of reward points that heavily filters and sorts (many) database results

Question

My page (even when it has no data) takes 10+ seconds to load. That's just too long, considering when you finally get to it there's no data. When it has data, it takes even longer.

Here is my view, which I'm guessing is the problem:

class RewardPointsEarnedReport(TemplateView): template_name = 'admin/hr/reward_points_earned_report.html' @user_is_staff def dispatch(self, request, *args, **kwargs): return super(RewardPointsEarnedReport, self).dispatch(request, *args, **kwargs) def get_context_data(self, **kwargs): context = super(RewardPointsEarnedReport, self).get_context_data(**kwargs) employees = get_active_users(self) #THIS FUNCTION IS NOT THE PROBLEM page = self.request.GET.get('page') report_list = RewardPointsEarned.objects.all().order_by('-date_earned') academic_terms = AcademicTermType.objects.all() point_types = RewardPointsType.objects.all() if page is None: page = 1 try: header = point_types[int(page)-1] except: header = point_types[point_type_list.count()-1] reports_by_category = [report for report in report_list if report.reward_points_type == header] this_term = AcademicTerm.objects.get(date_start__lte=datetime.now(),date_end__gte=datetime.now()) current_reports = [report for report in reports_by_category if report.date_earned > this_term.date_end and report.date_earned < this_term.date_start or 0] point_type_list = [] for point_type in point_types: point_type_count = RewardPointsEarned.objects.filter(reward_points_type=point_type).filter(date_earned__gte=this_term.date_end).count() if point_type_count: point_type_list.append((point_type, point_type_count)) else: point_type_list.append((point_type, 0)) context['point_type_list'] = point_type_list context['current_reports'] = current_reports context['academic_terms'] = academic_terms context['this_term'] = this_term context['header'] = header return context 

If anything else could be causing the problem, please let me know. If there's also nothing I can do let me know as well. I'm pretty stuck, and any help is appreciated!!

I was told to add more code so here is the html:

{% extends "admin/hr/index.html" %} {% load i18n %} {% load in_group %} {% block active %} <li><a href="{% url 'reports' %}">Reports</a><span class="divider">/</span></li> <li class="active">Reward Points Earned</li> {% endblock %} <!-- Content --> {% block content %} {% if user|in_group:"aux-dept-urec-hr" or "aux-dept-urec-facilities-admins" or user.is_staff %} <!-- HR --> <div class="span4"> <div class="dropdown" style="margin-bottom:10px;"> <button class="btn btn-secondary dropdown-toggle" type="button" id="dropdownMenuButton" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false"> Choose Term </button> <div class="dropdown-menu" aria-labelledby="dropdownMenuButton"> <ul style="list-style:none;padding:3px;margin:0px;"> {% for term in academic_terms %} {% if term == this_term.academic_term_type %} <li><strong><a class="dropdown-item" href="{% url 'rewards_earned' %}">{{ term }}</a></strong></li> {% else %} <li><a class="dropdown-item" href="{% url 'rewards_earned' %}">{{ term }}</a></li> {% endif %} {% endfor %} </ul> </div> </div> <table class="table table-bordered table-striped"> <thead> <tr class="alert-success"> <th colspan="1">Point Type</th> <th colspan="1" style="align:center;">Total</th> </tr> </thead> <tbody> {% for point_type, point_type_count in point_type_list %} <tr> {% if header|stringformat:"s" == point_type.name %} <td><strong> <a href="{% url 'rewards_earned' %}?page={{ forloop.counter }}">{{ point_type }}</a> </strong></td> <td style="text-align:center;">{{ point_type_count }}</td> {% else %} <td> <a href="{% url 'rewards_earned' %}?page={{ forloop.counter }}">{{ point_type }}</a> </td> <td style="text-align:center;">{{ point_type_count }}</td> {% endif %} </tr> {% endfor %} </tbody> </table> </div> <div class="span8"> <table class="table table-bordered table-striped"> <thead> <tr class="alert-info"> <th>Employee</th> <th>Amount</th> <th>Type</th> <th>Date Earned</th> </tr> </thead> <tbody> {% for report in current_reports %} <tr> <td><a href="/helm/hr/employee/{{ report.employee.pk }}">{{ report.employee }}</a></td> <td>{{ report.points_value }}</td> <td>{{ report.reward_points_type }}</td> <td>{{ report.date_earned }}</td> </tr> {% empty %} <tr> <td style="color: gray;" colspan="4">(None)</td> </tr> {% endfor %} </tbody> </table> </div> {% endif %} {% endblock %} 

This view displays points earned by someone, and it's filterable by type. Here is a picture of the view:

Answer 1

So I figured out why it was so slow.

It was this:

reports_by_category = [report for report in report_list if report.reward_points_type == header] 

and/or

current_reports = [report for report in reports_by_category if report.date_earned > this_term.date_end and report.date_earned < this_term.date_start or 0] 

Instead, I filter the object directly; for example:

reports = RewardPointsEarned.objects.filter(date_earned__gte=session.date_end).filter(date_earned__lte=session.date_start).filter(reward_points_type=category).order_by('-date_earned') 

This is A LOT faster! Insanely big improvement ~