Django one-to-many relation: optimize code to reduce number of database queries executed

Question

I have 2 models with a one-to-many relation on a MySQL DB:

class Domains(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, unique=True)
    description = models.TextField(blank=True, null=True)

class Kpis(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, unique=True)
    description = models.TextField(blank=True, null=True)
    domain_id = models.ForeignKey(Domains, on_delete=models.CASCADE, db_column='domain_id')

In order to bring ALL the domains with all their kpis objects, i use this code with a for loop:

final_list = []
domains_list = Domains.objects.all()
for domain in domains_list:
    # For each domain, get all related KPIs
    domain_kpis = domain.kpis_set.values()
    final_list.append({domain:domains_kpis})

The total number of queries i run is: 1 + the number of total domains i have, which is quite a lot.

I'm looking for a way to optimize this, preferably to execute it within only one query on the database. Is this possible?

willeM_ Van Onsem · Accepted Answer · 2020-12-23 18:44:58Z

1

You use .prefetch_related(…) [Django-doc] for this:

final_list = []
domains_list = Domains.objects.prefetch_related('kpis_set')
for domain in domains_list:
    # For each domain, get all related KPIs
    domain_kpis = domain.kpis_set.all()
    final_list.append({domain:domains_kpis})

This will make two queries: one to query the domains, and a second to query all the related Kpis with a single query into memory.

Furthermore please do not use .values(). You can serialze data to JSON with Django's serializer framework, by making use of .values() you "erode" the model layer. See the Serializing Django objects section of the documentation for more information.

answered Dec 23, 2020 at 18:44

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shayms8 Over a year ago

Thanks @WillemVanOnsem. Can you please elaborate why the .values() method is not suggested? Does it increase the total execution time?

willeM_ Van Onsem Over a year ago

@shayms8: no it is a primitive obsession antipattern: django-antipatterns.com/antipatterns/over_use_of_values.html

Charnel · Accepted Answer · 2020-12-23 19:03:07Z

Just wanted to add that you are asking a solution for "classic" N +1 queries problem. Here you can read a something about it and aslo find the examples for prefetch_related method adviced in Willem's answer.

Another thing worth mentioning is that probably you aren't suppose to use this dict final_list.append({domain:domains_kpis}), but instead you may want to map some field(s) from Domain to some field(s) from Kapis models and, if this is true, you can specify exact fields you'd like to have prefetched using Prefetch:

domains_list = Domains.objects.prefetch_related(Prefetch('kpis_set'), queryset=Kapis.objects.all().only('some_field_you_want_to_have'))
final_list = []
for domain in domains_list:
    domain_kpis = domain.kpis_set.all()
    final_list.append({domain.some_field:domains_kpis.prefetched_field})

This should give another boost to performance on big-volume table's.

Collectives™ on Stack Overflow

Django one-to-many relation: optimize code to reduce number of database queries executed

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related