How to best reduce number of queries

Using Django Using the ORM
1

I have a view that takes 40 second to load. Django-debug-toolbar says I have 210 similar queries.

This view caculates a weekly rate (revenue/miles) for each combination of truck_type, commodity and week for the first 35 weeks of current year. So the total number of queries is 210 (35 weeks * 3 commodities and * 2 truck types).

How can I reduce the number of queries?

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

    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).aggregate(Sum("billable_amount_after_accessorial"), Sum("total_miles"))
        revenue = relevant_loads['billable_amount_after_accessorial__sum']
        miles = relevant_loads['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')
2

Try this :

from django.db.models import Sum, Q
from fleetdata.utils import start_week_nr

def rates_weekly(request):
    tenant = request.tenant
    CURRENT_YEAR = datetime.datetime.now().year
    CURRENT_WEEK_CUSTOM = datetime.datetime.now().isocalendar()[1]

    def calculate_rate(load_data):
        revenue = load_data['billable_amount_after_accessorial__sum']
        miles = load_data['total_miles__sum']
        if revenue and miles is not None:
            rate = revenue / miles
        else:
            rate = 0
        return rate

    week_rates = {}
    for week in range(1, CURRENT_WEEK_CUSTOM + 6):
        start_of_week = start_week_nr(CURRENT_YEAR, week)
        end_of_week = start_of_week + datetime.timedelta(days=7)

        relevant_loads = Load.objects.filter(
            drop_date__gte=start_of_week,
            drop_date__lt=end_of_week,
            load_status__ne='Cancelled',
        ).values(
            'truck_type',
            'commodity',
        ).annotate(
            billable_amount_after_accessorial__sum=Sum('billable_amount_after_accessorial'),
            total_miles__sum=Sum('total_miles'),
        )

        for truck_type, commodity in [('CT', 'Reefer'), ('CT', 'DryVan'), ('CT', 'Flat Bed'), ('OO', 'Reefer'), ('OO', 'DryVan'), ('OO', 'Flat Bed')]:
            load_data = relevant_loads.filter(
                truck_type=truck_type,
                commodity=commodity,
            ).first()

            rate = calculate_rate(load_data)
            week_rates.setdefault(week, {})[f'rate_{truck_type.lower()}_{commodity.replace(" ", "_").lower()}'] = rate

    return week_rates
3

Amro-yasser, thank you! I tried that with slight modifications because of errors, but still the same 210 queries and 40 seconds to calculate.

def calculate_rate(load_data):
        try:
            revenue = load_data['billable_amount_after_accessorial__sum']
            miles = load_data['total_miles__sum']
        except TypeError:
            revenue = 0
            miles = 0
        if revenue and miles is not None:
            rate = revenue / miles
        else:
            rate = 0
        return rate

    week_rates = {}
    for week in range(1, CURRENT_WEEK_CUSTOM + 6):
        start_of_week = start_week_nr(CURRENT_YEAR, week)
        end_of_week = start_of_week + datetime.timedelta(days=7)

        relevant_loads = Load.objects.filter(
            drop_date__gte=start_of_week,
            drop_date__lt=end_of_week
        ).exclude(load_status='Cancelled'
        ).values(
            'truck_type',
            'commodity',
        ).annotate(
            billable_amount_after_accessorial__sum=Sum('billable_amount_after_accessorial'),
            total_miles__sum=Sum('total_miles'),
        )

        for truck_type, commodity in [('CT', 'Reefer'), ('CT', 'DryVan'), ('CT', 'Flat Bed'), ('OO', 'Reefer'), ('OO', 'DryVan'), ('OO', 'Flat Bed')]:
            load_data = relevant_loads.filter(
                truck_type=truck_type,
                commodity=commodity,
            ).order_by('truck_type').first()
            print(load_data)

            rate = calculate_rate(load_data)
            week_rates.setdefault(week, {})[f'rate_{truck_type.lower()}_{commodity.replace(" ", "_").lower()}'] = rate

        print(week_rates)
4

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