# How to best reduce number of queries

**URL:** <https://forum.djangoproject.com/t/how-to-best-reduce-number-of-queries/22582>\
**Category:** Using the ORM\
**Created:** [July 27, 2023, 11:11am UTC](https://forum.djangoproject.com/t/how-to-best-reduce-number-of-queries/22582 "2023-07-27T11:11:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![valeriuv](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/valeriuv/32/15025_2.png) [@valeriuv](https://forum.djangoproject.com/u/valeriuv)\
**Post date:** [July 27, 2023, 11:11am UTC](https://forum.djangoproject.com/t/how-to-best-reduce-number-of-queries/22582/1 "2023-07-27T11:11:03Z")

</div>

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?

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

```

---

<div class="post-metadata">

**Author:** ![Amro-yasser](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/amro-yasser/32/15026_2.png) [@Amro-yasser](https://forum.djangoproject.com/u/Amro-yasser)\
**Post date:** [July 27, 2023, 11:18am UTC](https://forum.djangoproject.com/t/how-to-best-reduce-number-of-queries/22582/2 "2023-07-27T11:18:07Z")

</div>

Try this :

```auto
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

```

---

<div class="post-metadata">

**Author:** ![valeriuv](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/valeriuv/32/15025_2.png) [@valeriuv](https://forum.djangoproject.com/u/valeriuv)\
**Post date:** [July 27, 2023, 12:09pm UTC](https://forum.djangoproject.com/t/how-to-best-reduce-number-of-queries/22582/3 "2023-07-27T12:09:57Z")

</div>

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

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![valeriuv](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/valeriuv/32/15025_2.png) [@valeriuv](https://forum.djangoproject.com/u/valeriuv)\
**Post date:** [July 28, 2023, 3:51pm UTC](https://forum.djangoproject.com/t/how-to-best-reduce-number-of-queries/22582/4 "2023-07-28T15:51:07Z")

</div>

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

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

```