In this article, you'll learn about N 1 queries, how to detect them with AppSignal, and how to fix them to speed up your Django apps significantly.
We'll start with the theoretical aspects and then move on to practical examples. The practical examples will mirror scenarios you might encounter in a production environment.
Let's get started!
What Are N 1 Queries?
The N 1 query problem is a prevalent performance issue in web applications that interact with a database. These queries can cause significant bottlenecks, which intensify as your database grows.
The problem occurs when you retrieve a collection of objects and then access the related objects for each item in the collection. For instance, fetching a list of books requires a single query (1 query), but accessing the author for each book triggers an additional query for every item (N queries).
N 1 problems can also occur when creating or updating data in a database. For example, iterating through a loop to create or update objects individually, rather than using methods like bulk_create() or bulk_update(), can result in excessive queries.
N 1 queries are highly inefficient because executing numerous small queries is significantly slower and more resource-intensive than consolidating operations into fewer, larger queries.
Django's default QuerySet behavior can inadvertently lead to N 1 issues, especially if you're unaware of how QuerySets work. Querysets in Django are lazy, meaning no database queries are executed until the QuerySet is evaluated.
Prerequisites
Ensure you have:
- Python 3.9 and Git installed on your local machine
- An AppSignal-supported operating system
- An AppSignal account
Note: The source code for this project can be found in the appsignal-django-n-plus-one GitHub repository.
Project Setup
We'll work with a book management web app. The web app is built to demonstrate the N 1 query problem and how to resolve it.
Start by cloning the base branch of the GitHub repo:
$ git clone git@github.com:duplxey/appsignal-django-n-plus-one.git \ --single-branch --branch base && cd appsignal-django-n-plus-one
Next, create and activate a virtual environment:
$ python3 -m venv venv && source venv/bin/activate
Install the requirements:
(venv)$ pip install -r requirements.txt
Migrate and populate the database:
(venv)$ python manage.py migrate (venv)$ python manage.py populate_db
Lastly, start the development server:
(venv)$ python manage.py runserver
Open your favorite web browser and navigate to http://localhost:8000/books. The web app should return a JSON list of 500 books from the database.
The Django admin site is accessible at http://localhost:8000/admin. The admin credentials are:
user: username pass: password
Install AppSignal for Django
To install AppSignal on your Django project, follow the official docs:
- AppSignal Python installation
- AppSignal Django instrumentation
- AppSignal SQLite instrumentation
Ensure everything works by restarting the development server:
$ git clone git@github.com:duplxey/appsignal-django-n-plus-one.git \ --single-branch --branch base && cd appsignal-django-n-plus-one
Your app should automatically send a demo error to AppSignal. From this point forward, all your errors will be sent to AppSignal. Additionally, AppSignal will monitor your app's performance and detect any issues.
Web App Logic
The prerequisite to fixing N 1 queries is understanding your app's database schema. Pay close attention to your models' relationships: they can help you pinpoint potential N 1 problems.
Models
The web app has two models — Author and Book — which share a one-to-many (1:M) relationship. This means each book is associated with a single author, while an author can be linked to multiple books.
Both models have a to_dict() method for serializing model instances to JSON. On top of that, the Book model uses deep serialization (serializing the book as well as the book's author).
The models are defined in books/models.py:
$ python3 -m venv venv && source venv/bin/activate
They are then registered for the Django admin site in books/admin.py, like so:
(venv)$ pip install -r requirements.txt
Notice that AuthorAdmin uses BookInline to display the author's books within the author's admin page.
Views
The web app provides the following endpoints:
- /books/ returns the list of books
-
/books/
/ returns a specific book - /books/by-authors/ returns a list of books grouped by authors
- /books/authors/ returns the list of authors
-
/books/authors/
/ returns a specific author
The links above are clickable if you have the development web server running.
And they're defined in books/views.py like so:
(venv)$ python manage.py migrate (venv)$ python manage.py populate_db
Great, you now know how the web app works!
In the next section, we'll benchmark our app to detect N 1 queries with AppSignal and then modify the code to eliminate them.
Detect N 1 Queries in Your Django App with AppSignal
Detecting performance issues with AppSignal is easy. All you have to do is use/test the app as you normally would (for example, perform end-user testing by visiting all the endpoints and validating the responses).
When an endpoint is hit, AppSignal will create a performance report for it and group all related visits together. Each visit will be recorded as a sample in the endpoint's report.
Detect N 1 Queries in Views
Firstly, visit all your app's endpoints to generate the performance reports:
- /books/
- /books/
/ - /books/by-authors/
- /books/authors/
- /books/authors/
/
Next, let's use the AppSignal dashboard to analyze slow endpoints.
Example 1: One-To-One Relationship (select_related())
Navigate to your AppSignal app and select Performance > Issue list on the sidebar. Then click Mean to sort the issues by descending mean response time.
Click on the slowest endpoint (books/) to view its details.
Looking at the latest sample, we can see that this endpoint returns a response in 1090 milliseconds. The group breakdown shows that SQLite takes 651 milliseconds while Django takes 439.
This indicates a problem because an endpoint as simple as this shouldn't take as long.
To get more details on what happened, select Samples in the sidebar and then the latest sample.
Scroll down to the Event Timeline to see what SQL queries got executed.
Hovering over the query.sql text displays the actual SQL query.
More than 1000 queries were executed:
$ git clone git@github.com:duplxey/appsignal-django-n-plus-one.git \ --single-branch --branch base && cd appsignal-django-n-plus-one
These are a clear sign of N 1 queries. The first query fetched a book (1), and each subsequent query fetched the book's author's details (N).
To fix it, navigate to books/views.py and modify book_list_view() like so:
$ python3 -m venv venv && source venv/bin/activate
By utilizing Django's select_related() method, we select the additional related object data (i.e., author) in the initial query. The ORM will now leverage a SQL join, and the final query will look something like this:
(venv)$ pip install -r requirements.txt
Wait for the development server to restart and retest the affected endpoint.
After benchmarking again, the response time goes from 1090 to 45, and the number of queries lowers from 1024 to 2. This is a 24x and 512x improvement, respectively.
Example 2: Many-To-One Relationship (prefetch_related())
Next, let's look at the second slowest endpoint (books/by-authors/).
Use the dashboard as we did in the previous step to inspect the endpoint's SQL queries. You'll notice a similar but less severe N 1 pattern with this endpoint.
This endpoint's performance is less severe because Django is smart enough to cache the frequently executed SQL queries, i.e., repeatedly fetching the author of a book. Check out the official docs to learn more about Django caching.
Let's utilize prefetch_related() in books/views.py to speed up the endpoint:
$ git clone git@github.com:duplxey/appsignal-django-n-plus-one.git \ --single-branch --branch base && cd appsignal-django-n-plus-one
In the previous section, we used the select_related() method to handle a one-to-one relationship (each book has a single author). However, in this case, we're handling a one-to-many relationship (an author can have multiple books), so we must use prefetch_related().
The difference between these two methods is that select_related() works on the SQL level, while prefetch_related() optimizes on the Python level. The latter method can also be used for many-to-many relationships.
For more information, check out Django's official docs on prefetch_related().
After benchmarking, the response time goes from 90 to 44 milliseconds, and the number of queries lowers from 32 to 4.
Detect N 1 Queries in Django Admin
Discovering N 1 queries in the Django admin site works similarly.
First, log in to your admin site and generate performance reports (for example, create a few authors or books, update, and delete them).
Next, navigate to your AppSignal app dashboard, this time filtering the issues by admin:
In my case, the two slowest endpoints are:
- /admin/login
- /admin/books/author/
We can't do much about /admin/login, since it's entirely handled by Django, so let's focus on the second slowest endpoint. Inspecting it will reveal an N 1 query problem. The author is fetched separately for each book.
To fix this, override get_queryset() in BookInline to fetch author details in the initial query:
$ python3 -m venv venv && source venv/bin/activate
Benchmark once again and verify that the number of queries has decreased.
Wrapping Up
In this post, we've discussed detecting and fixing N 1 queries in Django using AppSignal.
Leveraging what you've learned here can help you significantly speed up your Django web apps.
The two most essential methods to keep in mind are select_related() and prefetch_related(). The first is used for one-to-one relationships, and the second is for one-to-many and many-to-many relationships.
Happy coding!
P.S. If you'd like to read Python posts as soon as they get off the press, subscribe to our Python Wizardry newsletter and never miss a single post!
The above is the detailed content of Find and Fix N ueries in Django Using AppSignal. For more information, please follow other related articles on the PHP Chinese website!

This tutorial demonstrates how to use Python to process the statistical concept of Zipf's law and demonstrates the efficiency of Python's reading and sorting large text files when processing the law. You may be wondering what the term Zipf distribution means. To understand this term, we first need to define Zipf's law. Don't worry, I'll try to simplify the instructions. Zipf's Law Zipf's law simply means: in a large natural language corpus, the most frequently occurring words appear about twice as frequently as the second frequent words, three times as the third frequent words, four times as the fourth frequent words, and so on. Let's look at an example. If you look at the Brown corpus in American English, you will notice that the most frequent word is "th

This article explains how to use Beautiful Soup, a Python library, to parse HTML. It details common methods like find(), find_all(), select(), and get_text() for data extraction, handling of diverse HTML structures and errors, and alternatives (Sel

This article compares TensorFlow and PyTorch for deep learning. It details the steps involved: data preparation, model building, training, evaluation, and deployment. Key differences between the frameworks, particularly regarding computational grap

Python's statistics module provides powerful data statistical analysis capabilities to help us quickly understand the overall characteristics of data, such as biostatistics and business analysis. Instead of looking at data points one by one, just look at statistics such as mean or variance to discover trends and features in the original data that may be ignored, and compare large datasets more easily and effectively. This tutorial will explain how to calculate the mean and measure the degree of dispersion of the dataset. Unless otherwise stated, all functions in this module support the calculation of the mean() function instead of simply summing the average. Floating point numbers can also be used. import random import statistics from fracti

Serialization and deserialization of Python objects are key aspects of any non-trivial program. If you save something to a Python file, you do object serialization and deserialization if you read the configuration file, or if you respond to an HTTP request. In a sense, serialization and deserialization are the most boring things in the world. Who cares about all these formats and protocols? You want to persist or stream some Python objects and retrieve them in full at a later time. This is a great way to see the world on a conceptual level. However, on a practical level, the serialization scheme, format or protocol you choose may determine the speed, security, freedom of maintenance status, and other aspects of the program

The article discusses popular Python libraries like NumPy, Pandas, Matplotlib, Scikit-learn, TensorFlow, Django, Flask, and Requests, detailing their uses in scientific computing, data analysis, visualization, machine learning, web development, and H

This tutorial builds upon the previous introduction to Beautiful Soup, focusing on DOM manipulation beyond simple tree navigation. We'll explore efficient search methods and techniques for modifying HTML structure. One common DOM search method is ex

This article guides Python developers on building command-line interfaces (CLIs). It details using libraries like typer, click, and argparse, emphasizing input/output handling, and promoting user-friendly design patterns for improved CLI usability.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Dreamweaver Mac version
Visual web development tools

Notepad++7.3.1
Easy-to-use and free code editor

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

SublimeText3 Mac version
God-level code editing software (SublimeText3)
