How to reduce SQL queries in Django using prefetch_related?

Question

I am trying to optimize a Django project (vers. 1.8.6) in which each page shows 100 companies and their data at once. I noticed that an unnecessary amount of SQL queries (especially with contact.get_order_count) are performed within the index.html snippet below:

index.html:

{% for company in company_list %}
    <tr>
        <td>{{ company.name }}</td>
        <td>{{ company.get_order_count }}</td>
        <td>{{ company.get_order_sum|floatformat:2 }}</td>
        <td><input type="checkbox" name="select{{company.pk}}" id=""></td>
    </tr>
    {% for contact in company.contacts.all %}
        <tr>
            <td>&nbsp;</td>
            <td>{{ contact.first_name }} {{ contact.last_name }}</td>
            <td>Orders: {{ contact.get_order_count }}</td>
            <td></td>
        </tr>
    {% endfor %}
{% endfor %}

The problem seems to lie in constant SQL queries to other tables using foreign keys. I looked up how to solve this and found out that prefetch_related() seems to be the solution. However, I keep getting a TemplateSyntaxError about being unable the parse the prefetch, no matter what parameter I use. What is the proper prefetch syntax, or is there any other way to optimize this that I missed?

I've included relevant snippets of model.py below in case it's relevant. I got prefetch_related to work in the defined methods, but it doesn't change the performance or query amount.

model.py:

class Company(models.Model):
    name = models.CharField(max_length=150)

    def get_order_count(self):
        return self.orders.count()
    def get_order_sum(self):
        return self.orders.aggregate(Sum('total'))['total__sum']

class Contact(models.Model):
    company = models.ForeignKey(
        Company, related_name="contacts", on_delete=models.PROTECT)
    first_name = models.CharField(max_length=150)
    last_name = models.CharField(max_length=150, blank=True)

    def get_order_count(self):
        return self.orders.count()

class Order(models.Model):
    company = models.ForeignKey(Company, related_name="orders")
    contact = models.ForeignKey(Contact, related_name="orders")
    total = models.DecimalField(max_digits=18, decimal_places=9)

    def __str__(self):
        return "%s" % self.order_number

EDIT: The view is a ListView and defines the company_list as model = Company. I altered the view based on given suggestions:

class IndexView(ListView):
    template_name = "mailer/index.html"
    model = Company
    contacts = Contact.objects.annotate(order_count=Count('orders'))
    contact_list = Company.objects.all().prefetch_related(Prefetch('contacts', queryset=contacts))
    paginate_by = 100

Alasdair · Accepted Answer · 2017-11-08 22:23:35Z

1

Calling the get_order_count and get_order_sum methods causes one query every time the method is called. You can avoid this by annotating the queryset.

from django.db.models import Count, Sum
contacts = Contact.objects.annotate(order_count=Count('orders'), order_sum=Sum('orders'))

You then need to use a Prefetch object to tell Django to use your annotated queryset.

contact_list = Company.objects.all().prefetch_related(Prefetch("contacts", queryset=contacts)

Note that you need to add the prefetch_related to your queryset in the view, it is not possible to call it in the template.

Since you are using ListView, you should be overriding the get_queryset method, and calling prefetch_related() there:

class IndexView(ListView):
    template_name = "mailer/index.html"
    model = Company
    paginate_by = 100

    def get_queryset(self):
        # Annotate the contacts with the order counts and sums
        contacts = Contact.objects.annotate(order_count=Count('orders')
        queryset = super(IndexView, self).get_queryset()
        # Annotate the companies with order_count and order_sum
        queryset = queryset.annotate(order_count=Count('orders'), order_sum=Sum('orders'))
        # Prefetch the related contacts. Use the annotated queryset from before
        queryset = queryset.prefetch_related(Prefetch('contacts', queryset=contacts))
        return queryset

Then in your template, you should use {{ contact.order_count }} instead of {{ contact.get_order_count }}, and {{ company.order_count }} instead of {{ company.get_order_count }}.

edited Nov 8, 2017 at 22:23

answered Nov 8, 2017 at 14:50

Alasdair

310k59 gold badges605 silver badges534 bronze badges

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

10 Comments

Markus M. Over a year ago

I tried adding the annotate and prefetch lines you gave into the view and replaced the line in index.html, but now the order counts don't appear at all. The view uses the ListView and company_list is presumably defined in it as model = Company, in case that affects anything.

Alasdair Over a year ago

I can't tell what you've tried from that comment. Please edit your question. If you're using ListView, then you should be overriding get_queryset.

Markus M. Over a year ago

I clarified what I added to the view in my original question, and I replaced {{contact.get_order_count}} with {{contact.order_count}} in index.html. I tried to look up on how to override get_queryset, so I assume I should do that in views.py?

Alasdair Over a year ago

You shouldn't add the queries to the class iteself, you need to move it into the get_queryset method. See the docs on dynamic filtering for more info.

Markus M. Over a year ago

I tried to override get_queryset, but I get a TypeError saying get_queryset() was given two arguments. It seems to complain about super(IndexView, self).

|

Serg Anuke · Accepted Answer · 2017-11-08 14:51:17Z

0

Try this in views.py

company_list = Company.objects.all().prefetch_related("order", "contacts")

edited Nov 8, 2017 at 14:51

answered Nov 8, 2017 at 14:38

Serg Anuke

1682 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to reduce SQL queries in Django using prefetch_related?

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related