ORMs provides an abstraction between your database and your application. It
will translate some code 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:
Or via the
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
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:
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.
This time a single request is made (all the SQL JOIN are made automatically).
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.
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.