caching - Django: Slow Dashboard with Aggregated Data and Related Models - Stack Overflow

My Django dashboard page, which displays aggregated production data, is loading very slowly, sometimes taking up to 30 seconds or more. I'm trying to optimize its performance using caching and background tasks, but the data is not appearing on the page even though it's being correctly saved to the cache.

I tried several optimization techniques, expecting a significant improvement in page load time and correct display of the cached data:

Caching the entire view context: I used Django's caching framework to cache the entire context of the order_dashboard view. I expected this to drastically reduce database queries and page load time.

Using only('status'): I limited the fields retrieved from the Assignment model to only the status field, as I initially thought only this field from the model itself was needed in the template. I expected this to reduce the amount of data fetched from the database.

Background task for cache update: To avoid calculating aggregations on every page request, I implemented a background task using django-background-tasks to update the cache every 25 second. I expected this to move the heavy computation to the background and serve the pre-calculated data from the cache, resulting in near-instant page loads.

tasks.py

@background(schedule=25) 
def update_order_dashboard_cache():
    cache_key = "order_dashboard"
    cache_timeout = 20

    start_time = time.time()

    # Агрегация для Assignment
    assignments = Assignment.objects.annotate(
        total_packaged=Coalesce(Sum('packaging__quantity', distinct=True), Value(0)),
        total_ironed=Coalesce(Sum('iron__quantity', distinct=True), Value(0)),
        total_cleaned=Coalesce(Sum('cleaning__quantity', distinct=True), Value(0)),
        total_completed_operations=Coalesce(Sum('operationlog__operationitem__quantity'), Value(0)),
        total_actual_cutting=Coalesce(Sum('cutting__actual_quantity', distinct=True), Value(0)),
        total_planned_operations=Coalesce(
            Sum(F('cutting__order_item__quantity') *
                F('cutting__order_item__product__technological_map__operation__details_quantity_per_product')),
            Value(0)
        ),
        operation_progress=Case(
            When(total_planned_operations=0, then=Value(0.0)),
            default=(F('total_completed_operations') * 100.0) / F('total_planned_operations'),
            output_field=FloatField()
        ),
        total_defect=Coalesce(Sum('defect__quantity', distinct=True), Value(0)),
    ).only('status')

    # Расчет статусов
    assignment_statuses = assignments.values('status').annotate(count=Count('id'))
    completed_count = sum(status['count'] for status in assignment_statuses if status['status'] == 'completed')

    # Глобальные суммы
    total_paid_amount = Receipt.objects.aggregate(total=Sum('paid_amount'))['total'] or 0
    remaining_amount = CustomerDebt.objects.filter(debt_amount__gt=0).aggregate(total=Sum('debt_amount'))['total'] or 0
    active_orders_count = Order.objects.filter(status__in=['new', 'in_progress']).count()
    total_defect_count = assignments.aggregate(total=Sum('total_defect'))['total'] or 0

    # Формируем контекст
    context = {
        'assignments': list(assignments),
        'total_paid_amount': total_paid_amount,
        'remaining_amount': remaining_amount,
        'active_orders_count': active_orders_count,
        'completed_count': completed_count,
        'total_defect_count': total_defect_count,
        'total_actual_cutting': assignments.aggregate(total=Sum('total_actual_cutting'))['total'] or 0,
    }
    print(f"Context to be cached: {context}")

    # Сохраняем данные в кэш
    cache.set(cache_key, context, cache_timeout)
    total_time = time.time() - start_time
    print(f"Assignments in cache: {cache.get('order_dashboard')['assignments']}")
    print(f"Total execution time: {total_time:.2f} seconds")
    print(f"Cache key: {cache_key} - Data saved in cache")`

views.py
def order_dashboard(request):
    cache_key = "order_dashboard"
    context = cache.get(cache_key)

    if not context:
        # Если кэш пустой, вызываем функцию обновления синхронно один раз
        update_order_dashboard_cache(repeat=25, repeat_until=None)
        context = cache.get(cache_key)

        print('Кеш Пустой')

    return render(request, 'OrderDashboard.html', context)

Despite using caching and a background task, the data is not displayed on the page. The page remains blank. I've added logging to the background task to confirm that the data is being saved to the cache, and it is being saved correctly.

logs
Assignments in cache: [{'id': 7, 'status': 'in_progress_of_cutting', 'order__order_number': 'ЗАК 00001', 'order__customer__name': 'Ulukman', 'total_packaged': 0, 'total_ironed': 0, 'total_cleaned': 0, 'total_completed_operations
': 0, 'total_actual_cutting': 0, 'total_planned_operations': 0, 'operation_progress': 0.0, 'total_defect': 0}, {'id': 11, 'status': 'in_progress_of_cutting', 'order__order_number': 'ЗАК 00002', 'order__customer__name': 'Ulukman'
, 'total_packaged': 300, 'total_ironed': 0, 'total_cleaned': 0, 'total_completed_operations': 7680, 'total_actual_cutting': 50, 'total_planned_operations': 588000, 'operation_progress': 1.3061224489795917, 'total_defect': 10}, {
'id': 18, 'status': 'in_progress_of_cutting', 'order__order_number': 'ЗАК 00003', 'order__customer__name': 'ikboljon uldashvaev', 'total_packaged': 0, 'total_ironed': 10, 'total_cleaned': 0, 'total_completed_operations': 0, 'total_actual_cutting': 625, 'total_planned_operations': 3312000, 'operation_progress': 0.0, 'total_defect': 0}]
Total execution time: 0.03 seconds
Cache key: order_dashboard - Data saved in cache

OrderDashboard.html
{% for assignment in assignments %}
<tr>
    <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">
    <a href="{% url 'hrm:detail_order' assignment.order.id %}" class="text-blue-600 hover:underline">
        {{ assignment.order }}
    </a>
</td>
    <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_actual_cutting }}</td>
    <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">
         <div class="relative pt-1">
          <div class="flex mb-2 items-center justify-between">
           <div>
            <span class="text-xs font-semibold inline-block py-1 px-2 uppercase rounded-full text-purple-600 bg-purple-200">
             {{ assignment.operation_progress|default_if_none:"0"|floatformat:0}}%
            </span>
           </div>
          </div>
          <div class="overflow-hidden h-2 mb-4 text-xs flex rounded bg-purple-200">
           <div class="shadow-none flex flex-col text-center whitespace-nowrap text-white justify-center bg-purple-500" style="width: {{ assignment.operation_progress|floatformat:0 }}%">
           </div>
          </div>
         </div>
        </td>
     <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_cleaned }}</td>
     <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_ironed }}</td>
     <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_packaged}}</td>
</tr>
       {% endfor %}