N+1 Query Problem

When I first heard about query count test at work, I was so confused about what it was. Later, I learned that it’s about database query, and that database connection and access can be very expensive.

In the Sentry’s blog, I found an example that explains n + 1 query problem in Django and how we can improve it.

N + 1 Query Problem When Fetching Data

The following snippet is an example with n + 1 query problem:

from django.http import HttpResponse

def books(request):
    books = Book.objects.all()[:10]
    book_list = [book.title + " by " + book.author.name for book in books]
    return HttpResponse((", ").join(book_list))

• Each call to book.author.name makes a database query to fetch the book’s author.
• In total, it makes 11 queries (1 to fetch the list of books, 10 to fetch the author of each book)

How can we make this better? I learned from this article that we can use select_related method in Django:

from django.http import HttpResponse

def books(request):
    books = Book.objects.select_related("author").all()[:10]
    book_list = [book.title + " by " book.author.name for book in books]
    return HttpResponse((", ").join(book_list))

• Django will JOIN the tables for us in advance and preload author’s information.
• With the preload of the author data, when we call book.author.name, it does not make additional database query.

N + 1 Query Problem When Modifying Data

The following query may have n + 1 query problem:

for i in range(1, 11):
    Book.objects.create(title: f"Book {i})

Instead, we can use bulk_create() to create objects in a single query:

Book.objects.bulk_create(
    [Book(title: f"Book {i}") for i in range(1, 11)]
)

References

• Sentry: N + 1 Queries