How to optimize these Django queries for faster execution?-CodePudding

I have List of models named campaign_list as shown below, in every model there are some common fields (like overall_score, audit_date, audit_id). I want to find the average score of for each month. I have written below code and it works but the execution time is more. I want to optimize the execution time. Execution time increases as the number of records increases(currently I have 200k records and adding).

# List of 40 Models 
campaign_list = [Model1, Model2, Model3, ....., ModelN]

# Method 1
campaign_score = sum([sum([j.overall_score for j in i.objects.filter(audit_date__range=[start_date, todays_date])]) for i in campaign_list])

# Method 2
campaign_score = 0
for i in campaign_list:
   score = sum([j.overall_score for j in i.objects.filter(audit_date__range=[start_date, todays_date])])
   campaign_score  = score

CodePudding user response：

If you really have a list of model classes, it's not trivial to do everything in a single query, but at least you can use the Sum aggregate to do the summing per model in the database.

# List of 40 Models
campaign_list = [Model1, Model2, Model3, ...]

campaign_score = 0
for campaign_model in campaign_list:
    res = campaign_model.objects.filter(
        audit_date__range=[start_date, todays_date]
    ).aggregate(score=Sum("overall_score"))
    if res.get("score"):
        campaign_score  = res["score"]

CodePudding user response：

I assume that you have different models with an Audit model foreign key and thus have the common field audit_id. So we can join the models using select_related ORM method. After joining the columns will be the different for each models as specially overall_score. We can aggregate the value using F of each column and will give you the total score.

Audit.objects.select_related('model2', 'model3', 'model4')
    .aggregate(
        score=F('overall_score')   F('model2__overall_score')   F('model3__overall_score')   F('model4__overall_score').get('score')
)

By using this query the only one query get execute. you can check by using

from django.db import connection, reset_queries
reset_queries()
Audit.objects.select_related('model2', 'model3', 'model4')
    .aggregate(
        score=F('overall_score')   F('model2__overall_score')   F('model3__overall_score')   F('model4__overall_score').get('score')
)
print(connection.queries)
reset_queries()

As @AKX told if you have huge data then you should go with index and save time.