I am wondering what the best practices are for optimizing view performance.
I have this view that takes over 90 seconds to load (with 5 queries, 2 of which take the bulk of time). It has some complex queries, which a few Subqueries and a bunch of calculations.
Should I try to split all these calculations in different views, use model @property, use async for the calculations or make some intermediary calculations and save them as fields on models? (I am fairly new to programming and Django)?
This view is supposed to calculate the (salary) base amount and the bonus for each Dispatcher. A dispatcher can have BonusCustomers, for which they receive an extra bonus.
The base amount (per dispatcher) is the average weekly miles from the last 2 weeks * the set rate per location.
The bonus (per dispatcher) is the set bonus per customer (field of Dispatcher) * sum of relevant jobs (loads) from dispatcherās BonusCustomers. If the company weekly rate per mile for the last 2 weeks (billable amount / total_miles) is higher than the customer rate per mile for the last week, then relevant jobs are all loads from past week for all BonusCustomers for this specific Dispatcher.
def dispatchers_list(request):
tenant = get_tenant(request)
if request.method == 'GET':
if request.GET.get('date_range') is not None:
date_range = request.GET.get('date_range')
date_start = datetime.datetime.strptime(date_range.split('-')[0].strip(), '%m/%d/%Y')
date_end = datetime.datetime.strptime(date_range.split('-')[1].strip(), '%m/%d/%Y')
custom_week_nr = get_week_number(date_end.date())
year = int(date_end.strftime("%Y"))
date_start_string = date_start.strftime('%m/%d/%Y')
date_end_string = date_end.strftime('%m/%d/%Y')
else:
date_start = start_week_nr(CURRENT_YEAR, CURRENT_WEEK_CUSTOM-1)
date_start = datetime.datetime.combine(date_start, datetime.datetime.min.time())
date_end = date_start + datetime.timedelta(days=7)
custom_week_nr = get_week_number(date_end.date())
year = int(date_end.strftime("%Y"))
date_start_string = date_start.strftime('%m/%d/%Y')
date_end_string = date_end.strftime('%m/%d/%Y')
date_range = f"{date_start_string} - {date_end_string}"
date_start_miles = datetime.datetime.combine(date_start - datetime.timedelta(days=7), datetime.datetime.min.time())
date_end_miles = date_start_miles + datetime.timedelta(days=14)
# Calculate company rate per mile for last 2 weeks
company_loads_last_2_weeks = Load.objects.filter(drop_date__gte=date_start_miles, drop_date__lt=date_end_miles).exclude(load_status='Cancelled').aggregate(billable_amount=Sum('billable_amount'), total_miles=Sum('total_miles'))
if company_loads_last_2_weeks['billable_amount'] is not None and company_loads_last_2_weeks['total_miles'] is not None:
company_rate_per_mile = company_loads_last_2_weeks['billable_amount'] / company_loads_last_2_weeks['total_miles']
else:
company_rate_per_mile = 0
loads_subquery = Load.objects.filter(
customer=OuterRef('customer'), # Reference the customer field of the current BonusCustomer
drop_date__gte=date_start_miles,
drop_date__lt=date_end_miles
).exclude(load_status='Cancelled'
).values('customer'
).annotate(
total_revenue=Sum('billable_amount', default=0),
total_miles=Sum('total_miles', default=0)
)
loads_subquery_all_jobs = Load.objects.filter(
customer=OuterRef('customer'),
drop_date__gte=date_start,
drop_date__lt=date_end
).exclude(load_status='Cancelled'
).values('customer'
).annotate(
all_jobs=Count('id')
)
loads_subquery_all_jobs_pick_city = Load.objects.filter(
customer=OuterRef('customer'),
pick_city=OuterRef('pick_city'),
drop_date__gte=date_start,
drop_date__lt=date_end
).exclude(load_status='Cancelled'
).values('customer'
).annotate(
all_jobs=Count('id')
)
loads_subquery_jobs_excl_dispatcher = Load.objects.filter(
customer=OuterRef('customer'),
drop_date__gte=date_start,
drop_date__lt=date_end
).exclude(load_status='Cancelled'
).exclude(sales_agent=OuterRef('dispatcher__name')
).values('customer'
).annotate(
jobs_excl_dispatcher=Count('id')
)
loads_subquery_jobs_excl_dispatcher_pick_city = Load.objects.filter(
customer=OuterRef('customer'),
pick_city=OuterRef('pick_city'),
drop_date__gte=date_start,
drop_date__lt=date_end
).exclude(load_status='Cancelled'
).exclude(sales_agent=OuterRef('dispatcher__name')
).values('customer'
).annotate(
jobs_excl_dispatcher=Count('id')
)
# Annotate the BonusCustomer queryset with the calculated sum
bonus_customers = BonusCustomer.objects.select_related('dispatcher').annotate(
customer_revenue=Subquery(loads_subquery.values('total_revenue'), output_field=DecimalField(max_digits=9, decimal_places=2)),
customer_miles=Subquery(loads_subquery.values('total_miles'), output_field=DecimalField(max_digits=9, decimal_places=2)),
customer_rpm = F('customer_revenue') / F('customer_miles'),
pick_city_null = ExpressionWrapper(Q(pick_city=''), output_field=BooleanField()),
all_jobs = Coalesce(
Case(
When(pick_city_null=True, then=Subquery(loads_subquery_all_jobs.values('all_jobs'), output_field=DecimalField(max_digits=9, decimal_places=2))),
When(pick_city_null=False, then=Subquery(loads_subquery_all_jobs_pick_city.values('all_jobs'), output_field=DecimalField(max_digits=9, decimal_places=2)))
), 0, output_field=DecimalField(max_digits=3, decimal_places=0)
),
jobs_excl_dispatcher = Coalesce(
Case(
When(pick_city_null=True, then=Subquery(loads_subquery_jobs_excl_dispatcher.values('jobs_excl_dispatcher'), output_field=DecimalField(max_digits=9, decimal_places=2))),
When(pick_city_null=False, then=Subquery(loads_subquery_jobs_excl_dispatcher_pick_city.values('jobs_excl_dispatcher'), output_field=DecimalField(max_digits=9, decimal_places=2)))
), 0, output_field=DecimalField(max_digits=3, decimal_places=0)
),
jobs_for_bonus = Coalesce(
Case(
When(customer_rpm__gte=Decimal(company_rate_per_mile), then='jobs_excl_dispatcher'),
When(customer_rpm__lt=Decimal(company_rate_per_mile), then='all_jobs')
), 0, output_field=DecimalField(max_digits=3, decimal_places=0)
),
).order_by('customer')
dispatchers = Dispatcher.objects.select_related('company', 'location').annotate(
num_of_loads=Count('load', filter=Q(load__drop_date__gte=date_start, load__drop_date__lt=date_end_miles)&~Q(load__load_status='Cancelled')),
loads_miles = Sum(F('load__total_miles')/2, filter=Q(load__drop_date__gte=date_start_miles, load__drop_date__lt=date_end_miles)&~Q(load__load_status='Cancelled'), default=0),
base_rate = Subquery(BaseRate.objects.filter(location=OuterRef('location')).values('rate')),
base_amount_model_null = ExpressionWrapper(Q(base_amount__isnull=True), output_field=BooleanField()),
base_amt = Case(
When(base_amount_model_null=False, then='base_amount'),
When(base_amount_model_null=True, then=F('loads_miles')*F('base_rate'))
),
bonus_jobs=Coalesce(
Subquery(bonus_customers.filter(dispatcher=OuterRef('pk')).order_by().values('dispatcher').annotate(jobs_sum=Sum('jobs_for_bonus')).values('jobs_sum')), 0, output_field=DecimalField(max_digits=3, decimal_places=0)
),
bonus_amount_new=Coalesce(
F('bonus_jobs')*F('bonus_per_customer'), 0, output_field=DecimalField(max_digits=3, decimal_places=0))
)
context = {
'tenant': tenant,
'dispatchers': dispatchers,
'bonus_customers': bonus_customers,
'date_range': date_range,
'date_start': date_start_string,
'date_end': date_end_string
}
return render(request, template_name='dispatchers/dispatcher-list.html', context=context)
The models:
class Dispatcher(models.Model):
name = models.CharField(max_length=50, blank=True, null=True, editable=True, unique=True)
location = models.ForeignKey(Location, on_delete=models.CASCADE, blank=True, null=True)
base_amount = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
bonus_amount = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
bonus_per_customer = models.DecimalField(max_digits=8, decimal_places=2, blank=True, null=True, default=25.00)
class BonusCustomer(models.Model):
customer = models.CharField(max_length=150, blank=True, null=True)
dispatcher = models.ForeignKey(Dispatcher, on_delete=models.DO_NOTHING, blank=True, null=True)
pick_city = models.CharField(max_length=150, blank=True, null=True)
class Load(models.Model):
sales_agent = models.ForeignKey(Dispatcher, on_delete=models.DO_NOTHING, to_field='name', blank=True, null=True)
customer = models.CharField(max_length=320, null=True, blank=True)
pick_date = models.DateTimeField(null=True, blank=True)
drop_date = models.DateTimeField(null=True, blank=True)
pick_city = models.CharField(max_length=120, null=True, blank=True)
load_status = models.CharField(max_length=120, null=True, blank=True)
billable_amount = models.DecimalField(max_digits=9, decimal_places=2, blank=True, null=True)
empty_miles = models.FloatField(null=True, blank=True)
total_miles = models.DecimalField(max_digits=9, decimal_places=2, null=True, blank=True, editable=False)
class BaseRate(models.Model):
location = models.ForeignKey(Location, on_delete=models.DO_NOTHING)
rate = models.DecimalField(max_digits=6, decimal_places=3, verbose_name='Base rate')
class Location(models.Model):
location = models.CharField(max_length=50, blank=False, null=False, unique=True)
