Django’s database layer provides various ways to help developers get the most out of their databases. This documents gathers together links to the relevant documentation, and adds various tips, organized under an number of headings that outline the steps to take when attempting to optimize your database usage.
As general programming practice, this goes without saying. Find out what queries you are doing and what they are costing you. You may also want to use an external project like ‘django-debug-toolbar’, or a tool that monitors your database directly.
Remember that you may be optimizing for speed or memory or both, depending on your requirements. Sometimes optimizing for one will be detrimental to the other, but sometimes they will help each other. Also, work that is done by the database process might not have the same cost (to you) as the same amount of work done in your Python process. It is up to you to decide what your priorities are, where the balance must lie, and profile all of these as required since this will depend on your application and server.
With everything that follows, remember to profile after every change to ensure that the change is a benefit, and a big enough benefit given the decrease in readability of your code. All of the suggestions below come with the caveat that in your circumstances the general principle might not apply, or might even be reversed.
...including:
We will assume you have done the obvious things above. The rest of this document focuses on how to use Django in such a way that you are not doing unnecessary work. This document also does not address other optimization techniques that apply to all expensive operations, such as general purpose caching.
Understanding QuerySets is vital to getting good performance with simple code. In particular:
To avoid performance problems, it is important to understand:
As well as caching of the whole QuerySet, there is caching of the result of attributes on ORM objects. In general, attributes that are not callable will be cached. For example, assuming the example weblog models:
>>> entry = Entry.objects.get(id=1)
>>> entry.blog # Blog object is retrieved at this point
>>> entry.blog # cached version, no DB access
But in general, callable attributes cause DB lookups every time:
>>> entry = Entry.objects.get(id=1)
>>> entry.authors.all() # query performed
>>> entry.authors.all() # query performed again
Be careful when reading template code - the template system does not allow use of parentheses, but will call callables automatically, hiding the above distinction.
Be careful with your own custom properties - it is up to you to implement caching.
To make use of the caching behaviour of QuerySet, you may need to use the with template tag.
When you have a lot of objects, the caching behaviour of the QuerySet can cause a large amount of memory to be used. In this case, QuerySet.iterator() may help.
For instance:
If these aren't enough to generate the SQL you need:
A less portable but more powerful method is QuerySet.extra(), which allows some SQL to be explicitly added to the query. If that still isn't powerful enough:
Write your own custom SQL to retrieve data or populate models. Use django.db.connection.queries to find out what Django is writing for you and start from there.
Hitting the database multiple times for different parts of a single 'set' of data that you will need all parts of is, in general, less efficient than retrieving it all in one query. This is particularly important if you have a query that is executed in a loop, and could therefore end up doing many database queries, when only one was needed. So:
When you just want a dict/list of values, and don't need ORM model objects, make appropriate usage of QuerySet.values(). These can be useful for replacing model objects in template code - as long as the dicts you supply have the same attributes as those used in the template, you are fine.
Use defer() and only() if there are database columns you know that you won't need (or won't need in most cases) to avoid loading them. Note that if you do use them, the ORM will have to go and get them in a separate query, making this a pessimization if you use it inappropriately.
...if you only want the count, rather than doing len(queryset).
...if you only want to find out if at least one result exists, rather than if queryset.
But:
If you are going to need other data from the QuerySet, just evaluate it.
For example, assuming an Email class that has a body attribute and a many-to-many relation to User, the following template code is optimal:
{% if display_inbox %}
{% with user.emails.all as emails %}
{% if emails %}
<p>You have {{ emails|length }} email(s)</p>
{% for email in emails %}
<p>{{ email.body }}</p>
{% endfor %}
{% else %}
<p>No messages today.</p>
{% endif %}
{% endwith %}
{% endif %}
It is optimal because:
In total, this code does either one or zero database queries. The only deliberate optimization performed is the use of the with tag. Using QuerySet.exists() or QuerySet.count() at any point would cause additional queries.
Rather than retrieve a load of objects, set some values, and save them individual, use a bulk SQL UPDATE statement, via QuerySet.update(). Similarly, do bulk deletes where possible.
Note, however, that these bulk update methods cannot call the save() or delete() methods of individual instances, which means that any custom behaviour you have added for these methods will not be executed, including anything driven from the normal database object signals.
If you only need a foreign key value, use the foreign key value that is already on the object you've got, rather than getting the whole related object and taking its primary key. i.e. do:
entry.blog_id
instead of:
entry.blog.id
Jul 05, 2010