Optimization of database requests to reduce page load speeds

Using Django Using the ORM
1

Hi Django Community,

I’m working on optimizing a slow query on a task management dashboard that loads a lot of data before the page fully renders, and then takes even more time when filtering the results using a form (e.g., filtering by date, team, category, etc.). The slowness seems to be coming from the backend query, and I’m looking for advice on how to further optimize it.

def base_queryset(self) -> models.TaskQuerySet:
    return (
        self.model.objects.all()
        .order_by("-timestamp")
        .select_related("user__profile__team")
        .prefetch_related("activities")
    )

def get_queryset(self) -> models.TaskQuerySet:
    qs = self.base_queryset().only('id', 'user', 'timestamp', 'status')  # Limit fields
    
    if not self.form.is_valid():
        return qs

    form_data = self.form.cleaned_data
    filters = {}

    start_date = form_data.get("start_date")
    if start_date:
        filters["latest_activity__date__gte"] = start_date

    end_date = form_data.get("end_date")
    if end_date:
        filters["latest_activity__date__lte"] = end_date

    exclude_finished = form_data.get("exclude_finished")
    if exclude_finished:
        filters["completed_at__isnull"] = True

    category = form_data.get("category")
    if category:
        filters["activities__category"] = category

    region = form_data.get("region")
    if region:
        filters["user__location__grid__region_id"] = region

    team = form_data.get("team")
    if team and team != self.form_class.ALL_TEAMS:
        filters["user__profile__team__name"] = team

    status = form_data.get("status")
    if status:
        filters["status"] = status

    if form_data.get("only_manual_override") == "True":
        filters["manual_override"] = True

    qs = qs.filter(**filters)

    search_query = form_data.get("search_query")

    if search_query:
        # Add the search filters in a single query rather than multiple
        search_filter = Q(user__id=search_query.upper()) | \
                        Q(user__tasks__task_id=search_query) | \
                        Q(user__region__location_id=search_query) | \
                        Q(user__address__postcode=search_query) | \
                        Q(user__profile__associated_users__last_name__icontains=search_query)
        qs = qs.filter(search_filter)

    return qs

What I’ve tried so far:

  • I’m using select_related and prefetch_related to reduce the number of query hits.
  • I’ve limited the fields fetched with .only() to load just the necessary ones.
  • Filters are applied through a dictionary to avoid excessive individual queries, and Q() objects are used for search queries.

The problem:

Even with these optimizations, the dashboard still takes a long time to load the initial data and is sluggish when applying filters. I suspect that there could be bottlenecks either in the way the related models are queried or in the joins happening with the select_related and prefetch_related.

What I’m looking for:

  • Suggestions on how to further optimize this query, particularly in handling related model joins and large datasets.
  • Any best practices for improving performance in Django for dashboards with complex filtering.
  • Recommendations for handling large datasets more efficiently in this context.

Any guidance or advice would be greatly appreciated!

Thanks in advance!

2

Welcome @Shiembo !

I think for us to be able to offer any tangible advice, we’d need to have more information about the situation.

  • What are the definitions of all the models involved?
  • What are the sizes (number of rows) of these models?
  • How many rows are being returned by this query?
  • What exactly do you consider “slow”?
  • How are you verifying that it’s the query itself causing the issue?
  • Have you examined the query being generated?
  • Have you run one of these queries outside the context of Django to verify that it is the query that is slow?

From looking at this code, it seems to me like this code isn’t going to be evaluating the query - the queryset itself is likely being resolved somewhere else. What is the view doing with this data after being retrieved?