Improve Django SQL Queries Using Debug Toolbar

Django’s database querying is there to make life easier but it’s also easy to forget about the SQL that is still generated under the hood. When using Django’s query objects it’s extremely difficult to know how often the database is being hit and with what SQL queries. We often write queries in our views, check that they return what we want, then forget them. Lots of the time that is okay but often we’ll run into performance problems later down the road due to too many database queries. Even if you only have one query in your view, the template might generate more queries without you knowing about them.

Luckily we have the django debug toolbar to show us how many queries are being sent to our database for a single page request. So I’m going to go through an example of using the debug toolbar to make a performance improvement for a django view. I’m not going into how to install the debug tool bar because it’s all on their download page here.

I created a simple test site to demonstrate how to find and improve database hits. It has one page which just lists a load of comments from the database. The template HTML is as follows.

<!doctype html>
<html>
<head>
	<title>Debug Demo</title>

	<style type="text/css">
	div {
		margin:10px;
		padding:10px;
		border:1px solid #000;
	}
	</style>
</head>
<body>

	<h1>Comments</h1>

	{% for comment in comments %}
	<div>
	<p>Posted by: {{ comment.posted_by.first_name }}</p>
	<p>Date: {{ comment.post_date|date:"D jS M" }}</p>

	{{ comment.text|linebreaks }}
	</div>
	{% endfor %}

</body>
</html>

So as you can see, the template is expecting a list of comments and is just going to create markup to display each of them. The Django model and view are as follows.

from django.db import models
from django.contrib.auth.models import User

class Comment(models.Model):
    post_date = models.DateTimeField(auto_now_add=True)
    posted_by = models.ForeignKey(User)
    text = models.TextField()

from django.shortcuts import render
from DebugDemo.models import Comment

def comments(request):

    return render(request, 'comments.html', {
                     'comments': Comment.objects.all()
                    })

The result is as expected, the page loads and we see all comments in the database. What you may think by looking at our view code is that one database hit is made, just one that gets all comments. In reality we have one hit to get the comments plus one hit per comment that is retrieved. So to put this in Python form we have len(comments)+1 database hits. The problem is that we won’t notice a performance problem for this until we have loads of comments to show and loads of visitors wanting to see them. We can see the SQL that was sent to the database by using the debug toolbar and opening the SQL tab. In my example I had two comments in the database so I had three queries as shown below.

What’s happening here is that the view will run one database query to retrieve all the comment data then our template needs the user’s name to show against each comment, so when it get’s to that it will do another query to retrieve each user for each comment. Understanding why these queries are generated is half the battle but it’s made a lot easier with the debug toolbar as we can see what the SQL is and therefor know what data each is retrieving. When our list of SQL queries has one for comments and loads more for users it’s pretty obvious what’s going on.

Solving this is a matter of re-writing our view query so that it retrieves the data that our template needs all at once. To do that we can use the query objects values method. It’s always about not retrieving the things you don’t need. So our new view looks like this.

from django.shortcuts import render
from DebugDemo.models import Comment

def comments(request):

    comments = Comment.objects.all().values('post_date', 'posted_by__first_name', 'text')

    return render(request, 'comments.html', {
                     'comments': comments
                    })

You can see that we’re using the values method to tell Django to only retrieve the three columns that our template needs. The problem now is that our template needs a bit of an update. It currently calls comment.posted_by.first_name to print the users name but this is no longer correct because our new query returns a dictionary of key value pairs not a list of model objects. So here is the new template code, it’s just using comments.posted_by__first_name instead.

<!doctype html>
<html>
<head>
	<title>Debug Demo</title>

	<style type="text/css">
	div {
		margin:10px;
		padding:10px;
		border:1px solid #000;
	}
	</style>
</head>
<body>

	<h1>Comments</h1>

	{% for comment in comments %}
	<div>
	<p>Posted by: {{ comment.posted_by__first_name }}</p>
	<p>Date: {{ comment.post_date|date:"D jS M" }}</p>

	{{ comment.text|linebreaks }}
	</div>
	{% endfor %}

</body>
</html>

The result is just one query which we can see in the debug toolbar.

I hope I’ve shown how important it is to keep on eye on the queries that are going on because even in this really simple example we can see how performance problems can easily creep in. My advice is to always run through the pages you build with the debug toolbar to have a quick look at the queries to see if there is anything unexpected going on.

This entry was posted on October 9, 2011 at October 9, 2011 and is filed under Database, Django, Python. You can follow any responses to this entry through the RSS 2.0 feed.

Josh
October 9, 2011 at 11:56 pm

The other way to reduce the number of queries without changing the template code at all would be to use select_related.

Comment.objects.all().select_related(depth=1)

Etienne
October 10, 2011 at 1:30 am

Hmm, maybe you didn’t choose your example with enough care, but you didn’t really improved the performance of your view/template. The 3 queries where taking 2 ms and now your only query take also 2 ms. Not a big improvement! It’s not always true that less queries take less time. Sometimes it’s the inverse. We need to always benchmark and Django Debug Toolbar is really helping doing that!

Tom Coote
October 10, 2011 at 5:34 am
Hi Etienne, it is just an example and one that I know becomes a problem when you have real data like say 50 comments.