Communicating with the database is one of the most important aspects of web development. Inefficient queries can slow down the web application and significantly degrade user experience. Bad queries not only slow down the relevant request, but can degrade overall performance of irrelevant features as well. Furthermore, writing good database queries is a fundamental requirement in technical job interviews so learning to write good queries can go a long way. Here are some tips on how you can improve your database queries in Django ORM.
1) Retrieve required values using the values() and values_list() methods
If you only need to retrieve a subset of all the columns/fields on a model, you can use these methods to optimise performance. Using the QuerySet.values() or QuerySet.values_list() methods will reduce the data required to pass from the database towards the server. This reduces memory, time and makes the overall retrieval lighter. Having large queries that return tens to hundreds of thousands of rows of mostly unwanted column can overload or crash the server in worse case scenarios, so limiting the query to only fetch the mandatory fields will optimise the operation and improve throughput.
from django.db import models
class ExampleModel(models.Model):
extra_field_1 = models.CharField(...)
important_field_1 = models.CharField(...)
important_field_2 = models.CharField(...)
extra_field_2 = models.CharField(...)
extra_field_3 = models.CharField(...)
...
ExampleModel.objects.filter(...).values("important_field_1", "important_field_2")
# Returns [ { "important_field_1": "some value", "important_field_2": "another value" } ... ]
ExampleModel.objects.filter(...).values_list("important_field_1", "important_field_2")
# Returns [ ["some value", "another value"], ... ]2) Add Database Indexes
This might seem obvious to some people but having correct indexes on your tables can go a long way. If you have frequently used queries that filter on a certain column or a group of column, having an index on that will significantly improve performance. Database indexes create new tables that are efficient to traverse and avoid full scans of the entire database tables. Compound indexes further expand this feature and add indexes on multiple columns, exponentially improving performance if multiple columns are filtered at once. The order matters when writing compound indexes so learning how to properly create indexes and how they work under the hood is essential and is the first step on how to improve your database queries.
When adding indexes, mind the type of field on which you are adding the indexes. Fields that have values that are common or on which the data can be grouped on will improve performance. On the other hand the fields where the values are unique will not improve performance but would rather degrade performance as the data cannot be grouped based on them.
from django.db import models
class ExampleChoices(models.TextChoices):
CHOICE_1 = 'choice-1', 'Choice 1'
CHOICE_2 = 'choice-2', 'Choice 2'
CHOICE_3 = 'choice-3', 'Choice 3'
class ExampleModel(models.Model):
# Unique value per row
uid = models.CharField(..., unique=True)
# One of 3 values only
choice = models.CharField(..., choices=ExampleChoices.choices)
other_field = models.CharField(...)
class Meta:
indexes = [
# Very bad index, unique values, no grouping
models.Index(fields=["uid"]),
# Good index, values can be grouped
models.Index(fields=["choice"]),
# Example of a compound index
models.Index(fields=["choice", "other_field"])
]3) Use prefetch_related() and select_related()
When trying to access a model through a foreign key, it can trigger a separate db query. This can become problematic when you are trying to iterate through a queryset because it will result in N+1 extra queries and overwhelm the system. This can be avoided by prefetching the related model if you need it for processing. Prefetching, joins the tables and pre-populates the values so they are accessible and do not trigger extra unwanted queries. The prefetch_related() method is to prefetch the foreign key reverse relations and selected_related() method can be used to fetch the direct relations.
from django.db import models
class MainModel(models.Model):
...
class RelatedModel(models.Model):
main = models.ForeignKey(MainModel, ...)
...
# Mind the prefetch_related
rows = MainModel.objects.filter(...).prefetch_related("relatedmodel_set")
for row in rows:
# Iterate through the related model
for related in row.relatedmodel_set.all():
...4) Process queries in chunks
When processing a very large number of rows, loading all the rows in the query set into memory is not possible. The memory requirements are too much and this can crash the system or result in the process being killed off by the OS. Instead we have the option fetch batches of the query set into memory and process the batches. This will result in multiple smaller queries that won’t overwhelm the system.
We can call the QuerySet.iterator() method with the chunk_size argument exactly for this. This will allow us to iterate through the rows by loading batches of sizes n into memory at a time. This prevents the process to hog resources and degrade performance. This is especially essential for writing reports or exports where we must iterate through a large number of items or items where each row has large amounts of data.
from django.db import models
class SomeModel(models.Model):
...
items = SomeModel.objects.filter(...)
# Fetch data from db in chunks of 1000
for row in items.iterator(chunk_size=1000):
...5) Use bulk operations for CREATEs and UPDATEs
When creating or updating rows, often business logic or hierarchically structured data force us to perform these operations in loops, which can be quite inefficient. This can cause the process to be very slow and hog resources. Here is an example of how a beginner might make this mistake:
from django.db import models
class ExampleModel(models.Model):
...
# Create the rows in a loop
for ... in ... :
ExampleModel.objects.create(...)There are better ways of doing this that Django has provided. Mainly through methods like bulk_create() and bulk_update(). These allow us to perform these operations in a single database query instead of multiple individual queries. However there are some nuances in using these methods, for example, they donot trigger signals in django. Also if you are working with hierarchical data, then this can also become tricky as you might need references of one model to fill ForeignKeys in other models.
# For creating in bulk
new_rows = [ ExampleModel(...) for _ in ... ]
ExampleModel.objects.bulk_create(new_rows)
# For updating in bulk
rows = ExampleModel.objects.filter(...)
for row in rows:
row.some_field = "some-value"
ExampleModel.objects.bulk_update(rows, fields=["some_field"])6) Fewer DB queries are not always better
This might seem counterintuitive but having fewer database queries is not always the best solution. We often try to reduce the number of db queries to improve performance but in some scenarios having too large of a query can be slower than multiple lightweight queries. This can be when there are a lot of db joins which can explode the number of records to be fetched from the db.
One example of this happening can be when calculating stats in real time. Having complex aggregates on multiple levels of nesting can sometimes result in an explosion of the number of rows. Instead splitting the queries into smaller lightweight queries can be faster.
7) Preserve computed values than calculating in real time
Simple computations that are needed again and again can be wasteful to compute in real time if they can be computed once and stored. An example can be say for a model of a Class representing a classroom and a Student. A class can have many students, so when we display a list of classes, computing the number of students for each of the class at runtime is wasteful. Especially if once any student row is created or deleted, we count the number of students and store that value in the table as a field against each class. Then at fetch time that value can be displayed as is instead of being recomputed again and again.
This can be achieved using django signals. Django offers multiple signals, two of which can be used for our example, the post_save and the post_delete. These are fired once the model is saved, where through an argument it is available if the model was created for the first time and also when a model is deleted.
from django.db import models
from django.dispatch import receiver
from django.db.models.signals import post_save, post_delete, pre_save
class Class(models.Model):
...
student_count = models.IntegerField(...)
...
class Student(models.Model):
school_class = models.ForeignKey(Class, ...)
...
@receiver(post_save, sender=Student)
def student_post_save(sender, instance, created, ...):
# Recount the number of students if this is a new row
if created:
count = ... # Count the number of rows
instance.school_class.student_count = count
instance.school_class.save()
@receiver(post_delete, sender=Student)
def student_post_delete(sender, instance, ...):
# Recount the number of students
count = ... # Count the number of rows
instance.school_class.student_count = count
instance.school_class.save()Conclusion
Database query optimisation is very complex and many of the optimisation techniques can range from beginner to advanced. A good developer should always learn and educate themselves on this topic as this is an essential aspect of making a good quality and a sophisticated web server. These are some of the optimisations that a beginner developer can learn and implement.