Django models DB queries optimisation (using select_related / prefetch_related)

48 views Asked by At

I have this one-to-many relation models [each domain can have several kpis]. i use MySQL DB.

class Domains(models.Model):
    class Meta:
        managed = True
        db_table = 'edison_domains'

    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, unique=True)
    display_name = models.CharField(max_length=50)
    description = models.TextField(blank=True, null=True)

class Kpis(models.Model):
    class Meta:
        managed = True
        db_table = 'edison_kpis'

    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, unique=True)
    MORE FIELDS...
    domain_id = models.ForeignKey(Domains, on_delete=models.CASCADE, db_column='domain_id')

What i want is to present for each domain, the list of its KPIs. I tried lots of combinations of select_related and prefetch_related but it always end up that the DB perform a query for each domain, instead of 1 or 2 queries. I tried to read about it here.

My main goal is to reduce the number of DB calls and improve the overall performance time.

My current code:

(views.py)
class DomainsAndKpisDescriptionsPage(View):
    def __init__(self):
        self.resource = 'domains_kpis_descriptions'

    def get(self, request, *args, **kwargs):
        final_list = []
        domains_list = Domains.objects.all().prefetch_related('kpis_set').order_by('display_name')
        kpis = Kpis.objects.select_related('domain_id').values('id', 'display_name', 'number_display_format', 'description', 'calculation_type').order_by('display_name')
        for domain in domains_list:
            # For each domain, get all related KPIs
            domain_kpis = kpis.prefetch_related('domain_id').filter(domain_id=domain.id).order_by('domain_display_order')
            final_list.append({'name':domain.name,
                               'display_name':domain.display_name,
                               'description':domain.description,
                               'kpis':domain_kpis})
        context = {'domains_and_kpis_data':final_list}
        return render(request,
                      'tests_details/domains_kpis_descriptions.html',
                      context=context)

And my HTML template

<div class="row">
        <div class="col-lg-3 col-xlg-2 col-md-4">
            <div class="stickyside">
                <h3> Domains </h3>
                <div class="list-group" id="top-menu">
                    {% for domain in domains_and_kpis_data %}
                        <a href="#{{ domain.name }}" class="list-group-item {% if forloop.counter0 == 0 %} active {% endif %}">{{ domain.display_name }}</a>
                    {% endfor %}
                </div>
            </div>
        </div>
        <div class="col-lg-9 col-xlg-10 col-md-8">
            <div class="card">
                <div class="card-body">
                    {% for domain in domains_and_kpis_data %}
                        <div style="padding:10px;">
                            <h3 class="card-title text-themecolor kpi_header" id="{{ domain.name }}">{{ domain.display_name }} <font size="3"> ({{ domain.kpis|length }} KPIs) </font></h3>
                            <h6 class="text-themecolor">{{ domain.description }}</h6>
                        </div>
                        <table class="table table-bordered">
                            <thead>
                                <tr>
                                    <th width="30%">KPI</th>
                                    <th width="10%">Type</th>
                                    <th width="60%">Description</th>
                                </tr>
                            </thead>
                            {% for kpi in domain.kpis %}
                                <tr>
                                    <th> {{ kpi.display_name }} </th>
                                    <th> {{ kpi.calculation_type }} </th>
                                    <th> {{ kpi.description }} </th>
                                </tr>
                            {% endfor %}
                        </table>
                    {% endfor %}
                </div>
            </div>
        </div>
    </div>

Say i have 14 domains - the DB performs 14 queries. attached is an image from Django Debug Toolbar: enter image description here

0

There are 0 answers