You have no excuses now, use this free credit to launch your projects now on Digital Ocean.
Table of contents
Differences between select_related and prefetch_related in Django
Differences between select_related and prefetch_related in Django
The select_related and prefetch_related methods are used to reduce the number of queries made to the database. This translates into response time for each view. In addition, using these methods is one of the actions to implement to improve the performance of a Django application
Just consider that there are more important things to optimize other than your app’s performance , but if you insist, dive into annotate and aggregate, and be careful with the nested subqueries of annotate because they can make your django queries go really slow
Differences between select_related and prefetch_related summarized
| select_related | prefetch_related | |
|---|---|---|
| Relationship | Foreign key or One to One | Many to Many |
| Number of queries | 1 | 2 |
| Union of objects | Directly by SQL | Using Python |
Need to learn everything Django has to offer?
AdTo master Django you must understand how it works, how a HTTP request goes to the router, which pass the request data to the appropiate function (or class) and then how you can Django's ORM to interact with the database and return a response if you want a detailed explanation of each step, educative has a course on that, certificate included, and Python is covered (in case you don't know Python) link below.
select_related
The select_related method is used to follow a relationship of type ForeignKey or OneToOneField to the respective objects it points to and obtain them..
When using select_related we will have a longer query, however, the advantage is that it will no longer be necessary to access the database again to obtain the objects of the related model.
Simplified diagram of how select_related works
Consider this example:
from django.db import models
class Main(models.Model):
name = models.CharField(max_length=256)
class Derivative(models.Model):
name = models.CharField(max_length=256)
main = models.ForeignKey(
"Main", related_name="derivatives", on_delete=models.CASCADE
)
If we try to access the object pointed to by the Foreign Key relationship, a new database query will be generated. select_related avoids that extra query for each object.
{% for object in queryset %}
<p>{{object.name}}</p>
<small>{{object.main.name}}</small>
{% endfor %}
For example, if we have three Derived objects related to a single main object:
- A main query that retrieves all objects Derivative
- Three queries, exactly the same, one for each time we access the main object from the Derived object.
Use in a query
To use select_related we call it from our query, passing it the name of the field that corresponds to our relationship with the other model.
Derivative.objects.select_related("main")
Internal operation of select_related
How select_related works internally, select_related replaces multiple queries being performed by a single INNER JOIN at the database level:
SELECT my_app_derivative.id,
my_app_derivative.name,
my_app_derivative.main_id
FROM my_app_derivative
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
WHERE my_app_main.id = '1'
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
WHERE my_app_main.id = '1'
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
WHERE my_app_main.id = '1'
This reduces multiple SQL queries to a single, longer query.
SELECT my_app_derivative.id,
my_app_derivative.name,
my_app_derivative.main_id,
my_app_main.id,
my_app_main.name
FROM my_app_derivative
INNER JOIN my_app_main
ON (my_app_derivative.main_id = my_app_main.id)
prefetch_related
If the select_related method retrieves a single object from a single relationship field, the prefetch_related method is used when we have a multiple relationship with another model, i.e. a relationship of type ManyToMany or a reverse ForeignKey.
Simplified diagram of how prefetch_related works
Consider this example, note the ManyToManyField field towards the Main model.
from django.db import models
class Main(models.Model):
name = models.CharField(max_length=256)
class ManyToManyModel(models.Model):
name = models.CharField(max_length=256)
ManyToManyRel = models.ManyToManyField("Main", related_name="multiples")
If we access the field that represents the multiple relation of our object, without using prefetch_related, we will be impacting the database with a new query.
{% for object in queryset %}
<p>{{object.name}}</p>
{% for main in object.ManyToManyRel.all %}
<!-- New query each iteration -->
<p><small>{{main.name}}</small></p>
{% endfor %}
{% endfor %}
Use in a query
To use the prefetch_related method call it at the end of our query, choosing the field that represents the many-to-many relationship in our object.
queryset = ManyToManyModel.objects.prefetch_related("ManyToManyRel")
Inner workings of prefetch_related
How does prefecth_related work internally? The prefetch_related method replaces the multiple SQL queries by only 2 SQL queries: one for the main query and the other for the related objects, then it will join the data using Python.
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
INNER JOIN my_app_manytomanyrel_main
ON (my_app_main.id = my_app_manytomanyrel_main.main_id)
WHERE my_app_manytomanyrel_main.manytomanyrel_id = '1'
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
INNER JOIN my_app_manytomanyrel_main
ON (my_app_main.id = my_app_manytomanyrel_main.main_id)
WHERE my_app_manytomanyrel_main.manytomanyrel_id = '2'
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
INNER JOIN my_app_manytomanyrel_main
ON (my_app_main.id = my_app_manytomanyrel_main.main_id)
WHERE my_app_manytomanyrel_main.manytomanyrel_id = '3'
SELECT my_app_main.id,
my_app_main.name
FROM my_app_main
INNER JOIN my_app_manytomanyrel_main
ON (my_app_main.id = my_app_manytomanyrel_main.main_id)
WHERE my_app_manytomanyrel_main.manytomanyrel_id = '4'
The multiple queries above are reduced to only 2 SQL queries.
SELECT my_app_manytomanyrel.id,
my_app_manytomanyrel.name
FROM my_app_manytomanyrel
SELECT (my_app_manytomanyrel_main.manytomanyrel_id) AS *prefetch_related*val_manytomanyrel_id,
my_app_main.id,
my_app_main.name
FROM my_app_main
INNER JOIN my_app_manytomanyrel_main
ON (my_app_main.id = my_app_manytomanyrel_main.main_id)
WHERE my_app_manytomanyrel_main.manytomanyrel_id IN ('1', '2', '3', '4')
