Joshua Hassan

Posted on Feb 22, 2023

Write Faster Django Queries

#python #django #database #performance

Hi readers, I was recently working on a team project when I encountered inefficient and redundant code. To address this issue, I researched and experimented with various techniques for optimizing Django queries, which led to the creation of this article. Regardless of your experience level with Django, this article aims to equip you with the necessary tools to enhance the performance of your applications.

TABLE OF CONTENTS

Introduction
Prerequisites
What is Django ORM
How to view database performance
The N+1 problem
Ways to write faster and more efficient queries
- Use select_related() and prefetch_related():
- Use filter() instead of get()
- Use only() or defer()
- Use subqueries
- Use Q objects for complex queries
- Use annotations
- Use aggregation
- Use caching
- Honorable mentions
Conclusion
References

Introduction

Developers prioritize building web applications that are efficient and scalable, and optimizing database queries is crucial to achieving these goals. Django's built-in Object-Relational Mapping (ORM) allows developers to interact with the database without writing complex SQL queries. However, poorly optimized queries can result in slow performance and increased server load. In this article, we'll discuss techniques for writing faster Django queries and improving your application's overall performance.

At the end of the article, readers will have gained a better understanding of how to write optimized queries, and will be able to implement these techniques in their own applications. Additionally, readers will have gained a deeper understanding of how the Django ORM works, and will be able to use this knowledge to make informed decisions when working with databases in their projects.

Prerequisites

Knowledge of Python
Basic Knowledge of Django
Basic Knowledge of Django-Orm

What is Django ORM

The Django web framework provides a built-in tool called Object-Relational Mapping (ORM), which enables developers to interact with relational databases using high-level Python objects instead of writing complex SQL queries. The ORM maps database tables to Python classes, and database rows to instances of those classes, providing a simple and intuitive way to perform CRUD (create, read, update, and delete) operations on the database. This abstraction layer simplifies working with databases, removes the need to deal with low-level details, and reduces the risk of SQL injection attacks. Moreover, the Django ORM is compatible with a variety of databases, such as PostgreSQL, MySQL, SQLite, and Oracle.

How to view database performance

You can use the Django Debug Toolbar to view DB performance. The below link is for Django Debug Toolbar documentation:

https://django-debug-toolbar.readthedocs.io/en/latest/installation.html
Use QuerySet.explain() to understand how specific QuerySets are executed by your database.
Use django silk to view DB performance. https://github.com/jazzband/django-silk

But in this guide, we will be using the first option

The N+1 problem

The N+1 problem is a common issue that can occur in ORM frameworks, including Django. It arises when you fetch a collection of objects and then iterate over them to access a related object. This can lead to N+1 database queries being executed, where N is the number of objects you're iterating over.

For example, suppose you have a Book model with an author ForeignKey field, and you want to display a list of books with their author names. If you retrieve a queryset of all books and iterate over them to access the author names, Django will execute a separate database query for each book to retrieve its associated author. This can lead to a significant number of database queries being executed, which can slow down your application.

Ways to write faster and more efficient queries

1. Use select_related() and prefetch_related():

These methods are used to optimize database queries by reducing the number of database queries. select_related() is used to retrieve related objects in one database query, while prefetch_related() is used to retrieve multiple sets of related objects in one database query.

Suppose you have two models, Author and Book, where each book has an author

from  django.db  import  models

class  Author(models.Model):
    name  =  models.CharField(max_length=100)

    def  __str__(self):
        return  self.name

    class  Meta:
        verbose_name  =  'author'
        verbose_name_plural  =  'authors'


class  Book(models.Model):
    title  =  models.CharField(max_length=100)
    author  =  models.ForeignKey(Author, on_delete=models.CASCADE)

    def  __str__(self):
        return  self.title

    class  Meta:
        verbose_name  =  'book'
        verbose_name_plural  =  'books'

Here are three versions of a view that retrieves a list of books and their authors using the Django ORM. The first version is a simple implementation that retrieves the data using two separate queries, the second version optimizes the queries using select_related() and the third version optimizes the queries using prefetch_related():

# Version 1 - Simple implementation
def book_list(request):
    books = Book.objects.all()
    context = {'books': books}
    return render(request, 'book_list.html', context)

# Version 2 - Optimized implementation using select_related()
def book_list(request):
    books = Book.objects.select_related('author').all()
    context = {'books': books}
    return render(request, 'book_list.html', context)

# Version 3 - Optimized implementation using prefetch_related()
def book_list(request):
    books = Book.objects.prefetch_related('author').all()
    return render(request, 'book_list.html', {'books': books})

booklist.html

{% for  book  in  books %}
<div  class="w-1/2 px-4 mb-8">
    <div  class="bg-white shadow-lg rounded-lg overflow-hidden">
        <div  class="p-4">
            <h2  class="text-lg font-bold">{{ book.title }}</h2>
            <p  class="text-gray-700">{{ book.author }}</p>
            <p  class="text-gray-700 mt-2">{{ book.description }}</p>
        </div>
    </div>
</div>

{% endfor %}

In the first version, the books are retrieved using a single query (Book.objects.all()), but a separate query is executed for each author (book.author in the for loop). This can lead to the N+1 query problem if there are many books and authors in the database, as it will result in a large number of queries being executed.

we are having 6 queries

The second version of the view is optimized because it reduces the number of queries that need to be executed. The select_related() method is used to retrieve the related author objects in the same query as the books, using a join operation. This means that only one query is executed, regardless of the number of books and authors in the database. By reducing the number of queries, the second version of the view is more optimized than the first version.

reduced to 1 query

The third version of the view is also optimized, but it uses a different technique called prefetch_related(). This method is useful when you want to retrieve related objects that are not directly related to the main object being queried (in this case, Book). In the Book and Author model, there is a foreign key relationship between the two models, and select_related() works perfectly to optimize the query. However, if the Author model had a foreign key relationship with another model, say Publisher, and you wanted to retrieve all the books written by authors and the publisher information for each book, select_related() would not work in this case because it only works with directly related models.

In such cases, you can use prefetch_related() to retrieve the related objects in a separate query. In the third version of the view, prefetch_related('author') is used to retrieve all the authors for the books in a single query. This technique is particularly useful when you have many related objects and need to optimize the queries while minimizing the number of database hits.

By using prefetch_related() in the third version of the view, Django retrieves all the related author objects in a single query and caches them for use in the template. This results in faster query execution times and reduces the number of database hits, especially when dealing with large datasets.

optimized to 2 queries

2. Use filter() instead of get():

When retrieving a single object from the database using the Django ORM, you have two options: get() or filter(). Both methods are used to retrieve an object based on a set of conditions, but they behave differently.

get() retrieves a single object that matches the specified conditions, or raises an exception if no object is found or more than one object is found. For example, to retrieve a user with a specific email address, you can use the following code:

from django.contrib.auth.models import User

user = User.objects.get(email='user@example.com')

On the other hand, filter() retrieves a QuerySet of objects that match the specified conditions. For example, to retrieve all users with a specific first name, you can use the following code:

users = User.objects.filter(first_name='John')

When you use get() to retrieve a single object, Django makes a single database query to retrieve the object. However, if the specified conditions match more than one object, get() raises a MultipleObjectsReturned exception. If no object is found, get() raises a DoesNotExist exception.

When you use filter() to retrieve a single object, Django still makes two database queries: one to retrieve the matching objects, and another to retrieve the first object from the matching objects. However, you can avoid making the second query by using the first() method to retrieve the first object from the QuerySet. For example:

user = User.objects.filter(email='user@example.com').first()

This code retrieves the first user that matches the email address, or returns None if no matching user is found.

In summary, using filter() instead of get() when retrieving a single object can reduce the number of database queries by avoiding exceptions and allowing you to retrieve the first object from a QuerySet instead of making a separate query.

3. Use only() or defer():

When querying a model, Django retrieves all fields of that model by default. However, in many cases, you may not need all fields for a particular query. This can lead to unnecessary database queries, which can impact performance.

Using the only() method, you can specify the fields you need for a particular query, which will limit the amount of data retrieved from the database. This can help reduce the number of queries and minimize the amount of data transferred over the network, which can improve the overall performance of your application.

Similarly, using the defer() method, you can exclude fields that are not needed for a particular query. This can be especially useful for fields that contain large amounts of data, such as binary data or large text fields. Excluding these fields from the query can help reduce the amount of data transferred over the network and improve the performance of your application.

Suppose you have a model User with many fields, but you only need to retrieve the username and email fields. You can use the only() method to limit the fields returned by the query:

users = User.objects.only('username', 'email').all()

This will retrieve all users with only their username and email fields in a single query, instead of retrieving all fields for each user.

Alternatively, suppose you have a model User with many fields, but you don't need to retrieve the password field. You can use the defer() method to exclude the password field from the query:

users = User.objects.defer('password').all()

This will retrieve all users with all fields except the password field in a single query.

Overall, using only() and defer() can be a powerful technique for optimizing your Django application's performance by reducing the amount of data retrieved from the database and minimizing the number of queries executed.

4. Use subqueries

Subqueries can be used to retrieve related data in a more efficient way. For example, if you need to retrieve the count of related objects for each object in a queryset, you can use a subquery instead of using a loop.

Suppose you have two models, Author and Book, where each book has an author. If you want to retrieve a list of authors with the count of their books, you could use a loop to iterate through each author and count their books. However, this approach would result in multiple database queries and slow performance for large datasets. Instead, you can use a subquery to retrieve the count of books for each author in a single query.

Here's an example of the inefficient solution using a loop:

def  number_of_author_books(request):
    authors  =  Author.objects.all()
    for  author  in  authors:
        count  =  Book.objects.filter(author=author).count()
        author.count =  count

    return  render(request, "author_books.html", {'authors': authors})

In this example, the loop iterates through each author and retrieves the count of books using a separate database query for each author. This can result in slow performance, especially for large datasets.

We have 3 queries

Now, here's an example of the optimized solution using a subquery:

from django.db.models import OuterRef, Subquery, Count

def  number_of_author_books_optimized(request):
    authors  =  Author.objects.annotate(
    book_count=Subquery(
        Book.objects.filter(author=OuterRef('pk')).values('author').annotate(
            count=Count('id')
            ).values('count')[:1]
        )
    )

    return  render(request, "author_books.html", {'authors': authors})

In this example, the annotate() method is used to add a book_count field to each author. The resulting authors queryset is passed to the author_books.html template, which displays the author's name and the number of books they've written in a table.
The Subquery function is used to retrieve the count of books for each author in a subquery, which is more efficient than using a loop. The OuterRef function is used to reference the pk of the outer query (i.e., the Author queryset), and the [:1] slice is used to limit the subquery to return only the first value.

We now optimized to have 1 query

here is the html

{% extends 'base.html' %}

{% block content %}
<div class="max-w-7xl mx-auto py-6 sm:px-6 lg:px-8">
    <div class="bg-white overflow-hidden shadow-xl sm:rounded-lg">
        <table class="min-w-full divide-y divide-gray-200">
            <thead class="bg-gray-50">
                <tr>
                    <th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                        Author
                    </th>
                    <th scope="col" class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                        Number of Books
                    </th>
                </tr>
            </thead>
            <tbody class="bg-white divide-y divide-gray-200">
                {% for author in authors %}
                <tr>
                    <td class="px-6 py-4 whitespace-nowrap">
                        <div class="text-sm font-medium text-gray-900">{{ author.name }}</div>
                    </td>
                    <td class="px-6 py-4 whitespace-nowrap">
                        <div class="text-sm font-medium text-gray-900">{{ author.count }}</div>
                        <div class="text-sm font-medium text-gray-900">{{ author.book_count  }}</div>
                    </td>
                </tr>
                {% endfor %}
            </tbody>
        </table>
    </div>
</div>
{% endblock %}

By using a subquery, we can retrieve the count of books for each author in a single database query, resulting in much faster performance compared to using a loop.

Subqueries can be a highly effective method for fetching related data in a more efficient manner, particularly for complex or large datasets. Nevertheless, it is crucial to exercise caution while using subqueries and to evaluate their performance for your particular use case.

5. Use Q objects for complex queries:

Q objects allow you to create complex queries with multiple conditions. They are especially useful when combining multiple conditions with OR or NOT operators.

Here is an example of an inefficient query that uses multiple filter conditions:

books = Book.objects.filter(
    author__name='John',
    publisher__name='Penguin',
).exclude(
    genre='Romance'
)

This query retrieves all books written by an author named John, published by Penguin, and excludes any books with the genre 'Romance'. However, this can get messy and hard to read when dealing with many conditions.

Using Q objects, we can simplify this query by combining multiple conditions using OR or NOT operators:

from django.db.models import Q

books = Book.objects.filter(
    Q(author__name='John') |
    Q(publisher__name='Penguin')
).exclude(
    Q(genre='Romance') & Q(publisher__name='Penguin')
)

This query retrieves all books written by an author named John or published by Penguin, and excludes any books with the genre 'Romance' and published by Penguin.

By using Q objects, we can write more concise and readable queries, while also improving query performance by reducing the number of database hits.

It's important to note that using too many Q objects or nesting them too deeply can also have a negative impact on performance. It's important to strike a balance between readability and efficiency when using Q objects.

Here is an example of a nested Q object that could potentially be inefficient:

books = Book.objects.filter(
    Q(author__name='John') |
    Q(
        Q(publisher__name='Penguin') &
        Q(genre='Mystery')
    )
)

In this query, we are retrieving all books written by an author named John or published by Penguin with the genre 'Mystery'. However, this query could potentially be slow if the database has to perform many nested queries to retrieve the results. In cases like this, it may be better to break the query into multiple simpler queries instead.

6. Use annotations:

In Django, annotations offer a way to supplement the queryset with additional information that isn't stored in the database. Annotations come in handy when you need to compute aggregate values or perform computations on associated objects. Employing annotations enables you to sidestep executing multiple queries, thus enhancing your application's efficiency.

To illustrate, consider a scenario where you have a Product model and your aim is to fetch the total count of reviews for each product. One inefficient approach would be to employ a loop to iterate through each product and retrieve the review count for each one.

products = Product.objects.all()
for product in products:
    reviews = product.reviews.all()
    product.num_reviews = len(reviews)
return render(request, "product_list.html", {"products": products})

This approach will result in an N+1 query problem, where N is the number of products in the queryset. A more efficient way to do this is to use the annotate() method to add an extra field to the queryset:

from django.db.models import Count

products = Product.objects.annotate(num_reviews=Count('reviews'))
return render(request, "product_list.html", {"products": products})

Here, we are using the Count() aggregation function to count the number of related reviews for each product. The num_reviews field is added to the queryset as an annotation, and we can access it in the template.

Using annotations can greatly improve the performance of your queries by reducing the number of database queries required. However, you should be careful when using them with large datasets, as they can increase the memory usage of your application. It's always a good practice to test the performance of your queries with realistic data and adjust them as necessary.

7. Use aggregation:

Aggregation is a powerful feature in Django that allows you to perform complex calculations on a set of values in the database. By using aggregation, you can minimize the number of queries needed to retrieve and process data, which can significantly improve performance.

For example, let's say you have a model Order with a field price, and you want to calculate the total revenue for all orders. You can use the aggregate() method to perform this calculation in a single query:

from django.db.models import Sum

total_revenue = Order.objects.aggregate(total=Sum('price'))['total']

In this example, the aggregate() method calculates the sum of the price field for all Order objects, and returns the result as a dictionary with the key 'total'. This code is much more efficient than manually iterating over all Order objects and summing their prices, especially if there are a large number of orders in the database.

Another example is calculating the average rating for a set of Product objects. Here is the inefficient version that requires multiple queries:

products = Product.objects.all()
ratings_sum = 0
count = 0
for product in products:
    ratings = Rating.objects.filter(product=product)
    ratings_sum += sum(rating.value for rating in ratings)
    count += ratings.count()
average_rating = ratings_sum / count if count > 0 else 0

This code iterates over all Product objects, and for each product, it queries the related Rating objects and calculates the sum and count of their values. Finally, it calculates the average rating by dividing the sum by the count. This approach requires multiple queries and can be very slow if there are a large number of products and ratings.

Here is the optimized version using aggregation:

from django.db.models import Avg

average_rating = Product.objects.annotate(avg_rating=Avg('ratings__value')).aggregate(avg=Avg('avg_rating'))['avg']

In this code, we use the annotate() method to add an avg_rating field to each Product object, which is the average of the value field of all related Rating objects. Then, we use the aggregate() method to calculate the average of the avg_rating field for all Product objects. This code only requires a single query and is much faster than the previous version.

8. Use caching:

Caching allows you to store the results of a query in memory or on disk, so that subsequent requests can be served from the cache instead of making a database query. This can significantly improve the performance of your application.

It is a powerful tool for query optimization as it allows you to avoid executing the same query repeatedly by storing the results in memory or on disk. By caching the results of a query, subsequent requests can be served faster without hitting the database, which can significantly improve the performance of your application.

Here's an example of how caching can be used to improve query performance. Suppose you have a view that displays a list of products with their corresponding categories:

from django.shortcuts import render
from .models import Product

def product_list(request):
    products = Product.objects.all()
    categories = set(p.category for p in products)
    context = {
        'products': products,
        'categories': categories,
    }
    return render(request, 'product_list.html', context)

This view retrieves all products from the database and then loops through them to extract the set of categories. This can be inefficient if you have a large number of products.

To optimize this query using caching, you can use Django's caching framework to store the set of categories in cache. Here's the updated view:

from django.shortcuts import render
from django.core.cache import cache
from .models import Product

def product_list(request):
    products = Product.objects.all()
    categories = cache.get('categories')
    if categories is None:
        categories = set(p.category for p in products)
        cache.set('categories', categories)
    context = {
        'products': products,
        'categories': categories,
    }
    return render(request, 'product_list.html', context)

In this updated view, the categories are first retrieved from the cache using the cache.get() function. If they are not found in the cache, they are computed and stored in cache using the cache.set() function. Subsequent requests for the same view will retrieve the categories from cache instead of recomputing them.

Note that caching can also have drawbacks, such as increased memory usage and the need to keep the cache in sync with the database. It's important to use caching judiciously and only when it provides a measurable improvement in performance.

Some pros of using caching include

Faster response times: Caching can reduce the response times of web pages by storing frequently accessed data in memory or on disk, reducing the need to generate data from scratch each time a request is made.
Scalability: Caching can help improve the scalability of web applications by reducing the load on the database and web server.
Reduced network traffic: By caching frequently accessed data, you can reduce the amount of data that needs to be transmitted over the network, resulting in faster load times.
Customizable caching policies: Django cache allows you to configure different caching policies for different types of data, giving you fine-grained control over how and when data is cached.

Cons of using caching include

Increased complexity: Adding caching to an application adds complexity, which can make it harder to maintain and debug.
Increased memory usage: Depending on the caching policy used, caching can result in increased memory usage, which can impact the performance of other applications running on the same server.
Cache invalidation: Caching introduces the problem of cache invalidation, which is the process of keeping the cache up-to-date with changes in the underlying data. In some cases, it may be difficult to ensure that the cache is always up-to-date, which can lead to inconsistent data being displayed to users.
Security risks: Caching sensitive data can introduce security risks if the cache is not properly secured.

Overall, caching can be an effective way to improve the performance of web applications, but it should be used judiciously and with care. It's important to weigh the potential benefits against the costs and risks before deciding to use caching in a production environment.

For more information on how to set this up, there is a link to the official django docs in the reference section

9. Use indexing:

Indexing can improve the performance of your database queries by allowing the database to retrieve data more quickly. You can add indexes to your database tables using Django's migration framework.

10. Use database-level query optimization techniques:

Depending on the database you are using, there may be specific query optimization techniques you can use to improve performance, such as optimizing the database schema, tuning database parameters, or using a different database engine.

11. Use third-party libraries:

There are many third-party libraries available for Django that can help you optimize your database queries. For example, the django-debug-toolbar can be used to analyze the performance of your queries and identify areas for improvement.

Conclusion

In conclusion, writing faster queries is an essential part of optimizing a Django application's performance. This article highlights several best practices for writing efficient queries, such as using the select_related() and prefetch_related() methods, limiting the number of fields returned, and avoiding unnecessary database hits. The use of third-party packages like Django Debug Toolbar and Django Silk can also provide insight into query performance and help identify potential areas for optimization. By following these guidelines and regularly monitoring query performance, developers can ensure that their Django applications are performing as efficiently as possible.

Resources

Top comments (1)

KarovInal • May 3

@joshthecodingaddict Thanks for sharing!
I have a question: Did you optimize deep nested relations that are using GenericRelation, and how did you optimize those queries? I have a lot of subqueries during the serialization process (I'm using DRF).

DEV Community