So, I just wanted to share a few straightforward tips about database access optimization. For the most those tips are not something you should go to your code base and start refactoring. Actually for existing code it’s a better idea to profile first (with Django Debug Toolbar for example). But those tips are very easy to start applying, so keep them in mind when writing new code!
Accessing Foreign Key Values
If you only need the ID of the Foreign Key:
post.author_id
post.author.id
If you have a foreign key named author, Django will automatically store the primary key in the property
author_id, while in the author property will be stored a lazy database reference. So if you access the id via
the author instance, like this post.author.id
, it will cause an additional query.
Bulk Insert on Many to Many Fields
user.groups.add(administrators, managers)
user.groups.add(administrators)
user.groups.add(managers)
Counting QuerySets
If you only need the count:
users = User.objects.all()
users.count()
# Or in template...
{{ users.count }}
users = User.objects.all()
len(users)
# Or in template...
{{ users|length }}
Empty QuerySets
If you only need to know if the queryset is empty:
groups = Group.objects.all()
if groups.exists():
# Do something...
groups = Group.objects.all()
if groups:
# Do something...
Reduce Query Counts
review = Review.objects.select_related('author').first() # Select the Review and the Author in a single query
name = review.author.first_name
review = Review.objects.first() # Select the Review
name = review.author.first_name # Additional query to select the Author
Select Only What You Need
Let’s say the Invoice model has 50 fields and you want to create a view to display just a summary, with the number, date and value:
# views.py
# If you don't need the model instance, go for:
invoices = Invoice.objects.values('number', 'date', 'value') # Returns a dict
# If you still need to access some instance methods, go for:
invoices = Invoice.objects.only('number', 'date', 'value') # Returns a queryset
# invoices.html
<table>
{% for invoice in invoices %}
<tr>
<td>{{ invoice.number }}</td>
<td>{{ invoice.date }}</td>
<td>{{ invoice.value }}</td>
</tr>
{% endfor %}
</table>
# views.py
invoices = Invoice.objects.all()
# invoices.html
<table>
{% for invoice in invoices %}
<tr>
<td>{{ invoice.number }}</td>
<td>{{ invoice.date }}</td>
<td>{{ invoice.value }}</td>
</tr>
{% endfor %}
</table>
Bulk Updates
from django.db.models import F
Product.objects.update(price=F('price') * 1.2)
products = Product.objects.all()
for product in products:
product.price *= 1.2
product.save()