1
.. _topics-db-optimization:
3
============================
4
Database access optimization
5
============================
7
Django's database layer provides various ways to help developers get the most
8
out of their databases. This documents gathers together links to the relevant
9
documentation, and adds various tips, organized under an number of headings that
10
outline the steps to take when attempting to optimize your database usage.
15
As general programming practice, this goes without saying. Find out :ref:`what
16
queries you are doing and what they are costing you
17
<faq-see-raw-sql-queries>`. You may also want to use an external project like
18
'django-debug-toolbar', or a tool that monitors your database directly.
20
Remember that you may be optimizing for speed or memory or both, depending on
21
your requirements. Sometimes optimizing for one will be detrimental to the
22
other, but sometimes they will help each other. Also, work that is done by the
23
database process might not have the same cost (to you) as the same amount of
24
work done in your Python process. It is up to you to decide what your
25
priorities are, where the balance must lie, and profile all of these as required
26
since this will depend on your application and server.
28
With everything that follows, remember to profile after every change to ensure
29
that the change is a benefit, and a big enough benefit given the decrease in
30
readability of your code. **All** of the suggestions below come with the caveat
31
that in your circumstances the general principle might not apply, or might even
34
Use standard DB optimization techniques
35
=======================================
39
* Indexes. This is a number one priority, *after* you have determined from
40
profiling what indexes should be added. Use :attr:`django.db.models.Field.db_index` to add
43
* Appropriate use of field types.
45
We will assume you have done the obvious things above. The rest of this document
46
focuses on how to use Django in such a way that you are not doing unnecessary
47
work. This document also does not address other optimization techniques that
48
apply to all expensive operations, such as :ref:`general purpose caching
54
Understanding :ref:`QuerySets <ref-models-querysets>` is vital to getting good
55
performance with simple code. In particular:
57
Understand QuerySet evaluation
58
------------------------------
60
To avoid performance problems, it is important to understand:
62
* that :ref:`QuerySets are lazy <querysets-are-lazy>`.
64
* when :ref:`they are evaluated <when-querysets-are-evaluated>`.
66
* how :ref:`the data is held in memory <caching-and-querysets>`.
68
Understand cached attributes
69
----------------------------
71
As well as caching of the whole ``QuerySet``, there is caching of the result of
72
attributes on ORM objects. In general, attributes that are not callable will be
73
cached. For example, assuming the :ref:`example weblog models
74
<queryset-model-example>`:
76
>>> entry = Entry.objects.get(id=1)
77
>>> entry.blog # Blog object is retrieved at this point
78
>>> entry.blog # cached version, no DB access
80
But in general, callable attributes cause DB lookups every time::
82
>>> entry = Entry.objects.get(id=1)
83
>>> entry.authors.all() # query performed
84
>>> entry.authors.all() # query performed again
86
Be careful when reading template code - the template system does not allow use
87
of parentheses, but will call callables automatically, hiding the above
90
Be careful with your own custom properties - it is up to you to implement
93
Use the ``with`` template tag
94
-----------------------------
96
To make use of the caching behaviour of ``QuerySet``, you may need to use the
97
:ttag:`with` template tag.
102
When you have a lot of objects, the caching behaviour of the ``QuerySet`` can
103
cause a large amount of memory to be used. In this case,
104
:ref:`QuerySet.iterator() <queryset-iterator>` may help.
106
Do database work in the database rather than in Python
107
======================================================
111
* At the most basic level, use :ref:`filter and exclude <queryset-api>` to
112
filtering in the database to avoid loading data into your Python process, only
113
to throw much of it away.
115
* Use :ref:`F() object query expressions <query-expressions>` to do filtering
116
against other fields within the same model.
118
* Use :ref:`annotate to do aggregation in the database <topics-db-aggregation>`.
120
If these aren't enough to generate the SQL you need:
122
Use ``QuerySet.extra()``
123
------------------------
125
A less portable but more powerful method is :ref:`QuerySet.extra()
126
<queryset-extra>`, which allows some SQL to be explicitly added to the query.
127
If that still isn't powerful enough:
132
Write your own :ref:`custom SQL to retrieve data or populate models
133
<topics-db-sql>`. Use ``django.db.connection.queries`` to find out what Django
134
is writing for you and start from there.
136
Retrieve everything at once if you know you will need it
137
========================================================
139
Hitting the database multiple times for different parts of a single 'set' of
140
data that you will need all parts of is, in general, less efficient than
141
retrieving it all in one query. This is particularly important if you have a
142
query that is executed in a loop, and could therefore end up doing many database
143
queries, when only one was needed. So:
145
Use ``QuerySet.select_related()``
146
---------------------------------
148
Understand :ref:`QuerySet.select_related() <select-related>` thoroughly, and use it:
152
* and in :ref:`managers and default managers <topics-db-managers>` where
153
appropriate. Be aware when your manager is and is not used; sometimes this is
154
tricky so don't make assumptions.
156
Don't retrieve things you don't need
157
====================================
159
Use ``QuerySet.values()`` and ``values_list()``
160
-----------------------------------------------
162
When you just want a dict/list of values, and don't need ORM model objects, make
163
appropriate usage of :ref:`QuerySet.values() <queryset-values>`.
164
These can be useful for replacing model objects in template code - as long as
165
the dicts you supply have the same attributes as those used in the template, you
168
Use ``QuerySet.defer()`` and ``only()``
169
---------------------------------------
171
Use :ref:`defer() and only() <queryset-defer>` if there are database columns you
172
know that you won't need (or won't need in most cases) to avoid loading
173
them. Note that if you *do* use them, the ORM will have to go and get them in a
174
separate query, making this a pessimization if you use it inappropriately.
179
...if you only want the count, rather than doing ``len(queryset)``.
181
Use QuerySet.exists()
182
---------------------
184
...if you only want to find out if at least one result exists, rather than ``if
189
Don't overuse ``count()`` and ``exists()``
190
------------------------------------------
192
If you are going to need other data from the QuerySet, just evaluate it.
194
For example, assuming an Email class that has a ``body`` attribute and a
195
many-to-many relation to User, the following template code is optimal:
197
.. code-block:: html+django
199
{% if display_inbox %}
200
{% with user.emails.all as emails %}
202
<p>You have {{ emails|length }} email(s)</p>
203
{% for email in emails %}
204
<p>{{ email.body }}</p>
207
<p>No messages today.</p>
213
It is optimal because:
215
1. Since QuerySets are lazy, this does no database if 'display_inbox' is False.
217
#. Use of ``with`` means that we store ``user.emails.all`` in a variable for
218
later use, allowing its cache to be re-used.
220
#. The line ``{% if emails %}`` causes ``QuerySet.__nonzero__()`` to be called,
221
which causes the ``user.emails.all()`` query to be run on the database, and
222
at the least the first line to be turned into an ORM object. If there aren't
223
any results, it will return False, otherwise True.
225
#. The use of ``{{ emails|length }}`` calls ``QuerySet.__len__()``, filling
226
out the rest of the cache without doing another query.
228
#. The ``for`` loop iterates over the already filled cache.
230
In total, this code does either one or zero database queries. The only
231
deliberate optimization performed is the use of the ``with`` tag. Using
232
``QuerySet.exists()`` or ``QuerySet.count()`` at any point would cause
235
Use ``QuerySet.update()`` and ``delete()``
236
------------------------------------------
238
Rather than retrieve a load of objects, set some values, and save them
239
individual, use a bulk SQL UPDATE statement, via :ref:`QuerySet.update()
240
<topics-db-queries-update>`. Similarly, do :ref:`bulk deletes
241
<topics-db-queries-delete>` where possible.
243
Note, however, that these bulk update methods cannot call the ``save()`` or ``delete()``
244
methods of individual instances, which means that any custom behaviour you have
245
added for these methods will not be executed, including anything driven from the
246
normal database object :ref:`signals <ref-signals>`.
248
Don't retrieve things you already have
249
======================================
251
Use foreign key values directly
252
-------------------------------
254
If you only need a foreign key value, use the foreign key value that is already on
255
the object you've got, rather than getting the whole related object and taking
256
its primary key. i.e. do::