How to solve 'x similar queries' in Django

Question

I have a view that takes over 80 seconds to serve. The SQL tab from django-debug-toolbar says I have 422 total queries, from which 210 are similar.

The view calculates a weekly rate (revenue / miles) for 35 weeks of current year for each commodity and truck_type.

Any help on how I can optimise my queries is appreciated.

Here's the view:

def rates_weekly(request):
    tenant = request.tenant
    loads = Load.objects.all().exclude(load_status='Cancelled').values('billable_amount_after_accessorial', 'total_miles')
    from fleetdata.utils import start_week_nr

    def calculate_rate(year, week_num, commodity, truck_type):
        start_of_week = start_week_nr(year, week_num)
        end_of_week = start_of_week + datetime.timedelta(days=7)
        relevant_loads = loads.filter(drop_date__gte=start_of_week, drop_date__lt=end_of_week, truck_type=truck_type, commodity=commodity)
        revenue = relevant_loads.aggregate(Sum("billable_amount_after_accessorial"))['billable_amount_after_accessorial__sum']
        miles = relevant_loads.aggregate(Sum("total_miles"))['total_miles__sum']
        if revenue and miles is not None:
            rate = revenue / miles
        else:
            rate = 0
        return rate

    rates = {}
    for week in range(1, CURRENT_WEEK_CUSTOM+6):
        rate_ct_reefer = calculate_rate(CURRENT_YEAR, week, 'Reefer', 'CT')
        rate_ct_dryvan = calculate_rate(CURRENT_YEAR, week, 'DryVan', 'CT')
        rate_ct_flatbed = calculate_rate(CURRENT_YEAR, week, 'Flat Bed', 'CT')
        rate_oo_reefer = calculate_rate(CURRENT_YEAR, week, 'Reefer', 'OO')
        rate_oo_dryvan = calculate_rate(CURRENT_YEAR, week, 'DryVan', 'OO')
        rate_oo_flatbed = calculate_rate(CURRENT_YEAR, week, 'Flat Bed', 'OO')

        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"] = {}
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_reefer'] = rate_ct_reefer
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_dryvan']= rate_ct_dryvan
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_flatbed']= rate_ct_flatbed
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_reefer'] = rate_oo_reefer
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_dryvan']= rate_oo_dryvan
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_flatbed']= rate_oo_flatbed


    list_weeks = list(rates.keys())
    list_rates = list(rates.values())
    list_ct_reefer_rates = [ x['rate_ct_reefer'] for x in list_rates ]
    list_ct_dryvan_rates = [ x['rate_ct_dryvan'] for x in list_rates ]
    list_ct_flatbed_rates = [ x['rate_ct_flatbed'] for x in list_rates ]
    list_oo_reefer_rates = [ x['rate_oo_reefer'] for x in list_rates ]
    list_oo_dryvan_rates = [ x['rate_oo_dryvan'] for x in list_rates ]
    list_oo_flatbed_rates = [ x['rate_oo_flatbed'] for x in list_rates ]

    list_weeks_dt = [ datetime.datetime.strptime(date + '-1', '%Y-%W-%w') for date in list_weeks ]
    dict_reefer_dryvan_flatbed = { 
        'weeks': list_weeks_dt, 
        'rates_ct_reefer': list_ct_reefer_rates, 'rates_ct_dryvan': list_ct_dryvan_rates, 'rates_ct_flatbed': list_ct_flatbed_rates,
        'rates_oo_reefer': list_oo_reefer_rates, 'rates_oo_dryvan': list_oo_dryvan_rates, 'rates_oo_flatbed': list_oo_flatbed_rates
    }

    fig_ct = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_ct_reefer', 'rates_ct_dryvan', 'rates_ct_flatbed'])
    fig_ct.update_layout(
        xaxis_tickformat = '%Y-%W',
        xaxis = dict(tickmode = 'linear', dtick = 604800000)
        )
    fig_ct = fig_ct.to_html()

    fig_oo = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_oo_reefer', 'rates_oo_dryvan', 'rates_oo_flatbed'])
    fig_oo.update_layout(
        xaxis_tickformat = '%Y-%W',
        xaxis = dict(tickmode = 'linear', dtick = 604800000)
        )
    fig_oo = fig_oo.to_html()

context = {
    'tenant': tenant,
    'CURRENT_YEAR': CURRENT_YEAR,
    'CURRENT_WEEK_CUSTOM': CURRENT_WEEK_CUSTOM,
    'rates': rates,
    'fig_ct': fig_ct,
    'fig_oo': fig_oo,
}
return render(request, template_name='loads/rates-weekly.html', context=context)

Answer 1

So I solved the multiple queries issue by grouping the objects using .values() and annotating the aggregate function:

relevant_data = loads.filter(drop_date__gte=start_of_week, drop_date__lt=end_of_week).values('commodity', 'truck_type').annotate(revenue=Sum('billable_amount_after_accessorial')).annotate(total_miles=Sum('total_miles'))