ORMs provides an abstraction between your database and your application. It will translate some code like user.objects.filter(name__contains='joe') into something like SELECT * FROM user WHERE name LIKE '%joe%' without knowledge of the underlying database specificity (it is therefor easy to switch from a database to another for dev and prod for example).

As some code is translated into SQL requests, it happens frequently that a huge number of requests are made without noticing it (especially when working with relations). Django Debug Toolbar can be used to see executed query, either via the toolbar on each page:

Django Debug Toolbar

Or via the debugsqlshell command:

In [1]: User.objects.all()
Out[1]: SELECT "auth_user"."id",
FROM "auth_user" LIMIT 21 [265.09ms]
[<User: admin>]

This tool allows detect unexpected or slow SQL queries but it does not solve the problems. Here is how to solve two common “bad” patterns when working with ManyToManyField.

Use annotate to aggregate data

If you have multiple objects (like a list of author) and you want to display the number of book they have written (book model is a many to many relations of author) don’t count objects using the count template tag:

{% for author in authors %}
<p>{{ author }} wrote {{ author.books.all|count }} book(s)</p>
{% endfor %}

This snippet will do a request for every author to get all author’s books and then count it via python.

A better strategy is to directly count the books for every author using SQL COUNT() instruction. To do so with Django’s ORM, annotate is used:


Then in the template, a books__count attribute can be used for every author to display the result.

{% for author in authors %}
<p>{{ author }} wrote {{ author.books__count }} book(s)</p>
{% endfor %}

This time a single request is made (all the SQL JOIN are made automatically).

Checkout Django annotate documentation for details (to give a custom name or to have multiple aggregator at once)

Bulk create relations

Another thing that may slow down your app is when you have to create relations for many objects. Let’s stay with the previous Author / Book models, imagine you want to add a book co-written by many authors of your database. The common approach is to loop over each author which make an UPDATE per author. A better way to handle this is to create the book, request all the author at once and then use the relation table (through) to bulk insert all the relations.

authors = Author.objects.all()  # Let's say all author collaborated

book = Book(title="Yet another best seller") 
book.save()  # Save the new book

through = Author.books.through
relations = []
for author in authors:
    relation = through(author=author, book=book)
# No request made for relations until this point

# Create all the relations at once

With this kind of method all the relations are created with a unique request instead of one request per author. Through models field names are the one specified in the ManyToManyField if any or the linked models name.