1

I have few Django models: Domains, Kpis, Tests, TestsAndKpis

TestsAndKpis is many-to-many model with test_id and kpi_id as keys. Kpis model has a ForiegnKey of domain_id.

I want to efficiently get data from my DB (i use MySQL), meaning to have as less DB queries as possible.

Currently, to get what i need i run this

items = [{'kpi_type': t_k.type,
  'kpi_name': t_k.kpi_id.name,
  'kpi_display_name': t_k.kpi_id.display_name,
  'domain_display_name':t_k.kpi_id.domain_id.display_name}
   for t_k in TestsAndKpis.objects.filter(test_id=test_id).select_related('kpi_id')]

Which results my application to run LOTS of redundant queries [like SELECT ... FROM domains WHERE domains.id = 6 [total of 25 queries]

When i remove the 'domain_display_name' key that extract from domains table:

items = [{'kpi_type': t_k.type,
  'kpi_name': t_k.kpi_id.name,
  'kpi_display_name': t_k.kpi_id.display_name
   for t_k in TestsAndKpis.objects.filter(test_id=test_id).select_related('kpi_id')]

The application runs only 15 queries in total and returns MUCH faster.

My question: How can i extract the data efficiently, and avoid the redundant 10 queries? I tried to use chained select_related/prefetch_related but it did not work out for me.

shayms8
  • 671
  • 6
  • 13
  • 28
  • 1
    try using chained prefetch related https://stackoverflow.com/questions/50047815/how-would-i-do-these-multiple-joins-as-a-django-queryset/50051751#50051751 – Kallie Nov 16 '21 at 10:19
  • Thanks @Kallie. It solved my problem. i applied this: `for t_k in TestsAndKpis.objects.filter(test_id=test_id).select_related('kpi_id').select_related('kpi_id__domain_id')]` and it reduced the number of queries to 15 again. – shayms8 Nov 16 '21 at 12:19

0 Answers0