1

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)
Valeriu
  • 11
  • 6

2 Answers2

0

One thing that leaps out is the following:

    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']

Here you are performing two separate aggregate operations on the same queryset which I believe will be evaluated separately. You can do them all in the initial queryset and then just rely on the key. That should be one less call per iteration.

    relevant_loads = loads.filter(
        drop_date__gte=start_of_week, 
        drop_date__lt=end_of_week, 
        truck_type=truck_type,
        commodity=commodity
   ).aggregate(
       Sum("billable_amount_after_accessorial"), Sum("total_miles")
   )
    revenue = relevant_loads['billable_amount_after_accessorial__sum']
    miles = relevant_loads['total_miles__sum']
SamSparx
  • 4,629
  • 1
  • 4
  • 16
  • Exactly what I was hoping for. Thank you, this tip reduced load time and queries in half. However, it's not possible to chain aggregation, but rather: .aggregate(Sum("billable_amount_after_accessorial"), Sum("total_miles") – Valeriu Jul 27 '23 at 06:33
  • So now there are 210 database requests, which means one database request per week/commodity/truck type (35 weeks * 3 commodities * 2 truck types). How do I reduce the queries? – Valeriu Jul 27 '23 at 06:37
  • Because your are basically making 210 different equations you might have to go a little further afield. Have a look at window functions (https://docs.djangoproject.com/en/dev/ref/models/expressions/#window-functions ). I don't have much experience in them, but you might be able to put all your annotations into your first call and then loop through the recordset doing the math for each new combination of week/commodity/truck – SamSparx Jul 28 '23 at 04:55
  • Thank you, SamSparx! So apparently I can group objects by calling .values() and then annotate the aggregate function. – Valeriu Jul 28 '23 at 12:55
0

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'))
Valeriu
  • 11
  • 6