Debugging SQL with the django-debug-toolbar

by Michael Herman on Jul 10, 2013.
_________________

In this tutorial, we'll be going over how to debug SQL queries using the django-debug-toolbar. Let's get started.

Please Note: This is a companion post to Learn Indexing, dammit!.

Introduction

How often have you written queries using the Django ORM, just to forget about them a second later as soon as you get the results you're looking for? Even if you do drop down to writing SQL, it's still easy to forget that down the road certain queries can have a negative performance impact on your project.

Fortunately, the django-debug-toolbar can be used to identify processes that take up the most time when rendering a certain page.

Let's look at an example.

Setup

We need to install Django and create a Django Project and an App, django-debug-toolbar, and setup and populate a MySQL database with data.

  1. Start by cloning the repo from here:

    $ git clone git@github.com:mjhea0/django15-template.git

  2. Navigate into "django15-template" and setup/activate a virtualenv:

    $ virtualenv --no-site-packages env $ source env/bin/activate

  3. Install the requirements:

    $ pip install -r requirements.txt

  4. Setup a new database, if needed:

    $ mysql.server start $ mysql -u root -p Enter password: mysql> CREATE DATABASE ;

  5. Update the database settings in settings.py:

    DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': '',
    'USER': 'root', 'PASSWORD': '', } }

  6. Navigate into the "django15" directory, and run the following commands to create your database tables and set up a superuser:

    $ python manage.py syncdb

  7. Create a new app:

    $ python manage.py startapp myapp

    Then add the app name to your settings.py file under INSTALLED_APPS.

You should now have a basic Django Project and App setup. For further asssitance follow along here.

Sanity Check

  1. Ensure that the development server is working and everything is setup thus far:

    $ python manage.py runserver

  2. You should see the following if everything is setup correctly:

    toolbar

  3. Your project structure should look like this:

    
    ├── django15
       │   ├── init.py
       │   ├── settings.py
       │   ├── urls.py
       │   └── wsgi.py
       ├── manage.py
       └── myapp
           ├── init.py
           ├── models.py
           ├── tests.py
           └── views.py

Models, Views, and URLs:

Add the following code to models.py, views.py, urls.py, and admin.py.

  1. Models:

    from django.db import models import datetime

    class Comments(models.Model): title = models.CharField(maxlength=100) added = models.DateTimeField(autonow_add=True) body = models.TextField()

       def __unicode__(self): 
           return unicode(self.title)
    
  2. Views:

    from django.http import HttpResponse from django.template import RequestContext, loader from .models import Comments

    def comments(request): t = loader.get_template('comments.html') c = RequestContext(request, {'comments': Comments.objects.all()}) return HttpResponse(t.render(c))

  3. URLs:

    from django.conf.urls import patterns, include, url

    from django.contrib import admin admin.autodiscover()

    urlpatterns = patterns('', url(r'^admin/', include(admin.site.urls)), url(r'^comments/', 'myapp.views.comments'), )

  4. Admin:

    from django.contrib import admin from .models import Comments

    class CommentsAdmin(admin.ModelAdmin): class Meta: model = Comments

    admin.site.register(Comments,CommentsAdmin)

Template

  1. Update your template directory in settings.py.

    For example:

    TEMPLATE_DIRS = ('/Users/michaelherman/desktop/django15-template/django15/templates')

  2. Add the following template, comments.html, to the newly created directory:

    Essentially, comments.html iterates through each contact from the Comments class and renders HTML for each.

    Test

    1. Sync your database again:

      $ python manage.py syncdb

    2. Add a new Comment in the Django Admin, fire up your server, then navigate to http://localhost:8000/comments/.

    3. There are the 3 SQL queries that take 2.18 ms to complete (this probably will vary slightly on your end). Take a look at the individual SQL queries to gain a deeper understanding of what's happening beneath the hood.
    4. Now, let's see what happens when we add A LOT more data and create a slightly more complex query.

    Add data and test

    1. Download output.sql from here, then run the following command to add data to MySQL:

      $ mysql -u root < output.sql

      This will take a few minutes to fully execute.

    2. Within the Django admin, check your database to make sure the new data is accessible. In all there should be now 30,000 objects in the myapp_comments table. Again, navigate to http://localhost:8000/comments/. We have 3 queries, but now it takes 1974.63 ms to complete.

    3. Add an ordering clause to the model:

      class Meta: ordering = ['-added']

      This only increases the time to 2073.47. Try ordering by another field or group the results. If you add another table, create a foreign key, and use a JOIN in your query.

    Next, let's see what happens when we add indexes. But first, let's drop the query down to raw SQL.

    • Remove the meta class from models.py:

      class Meta: ordering = ['-added']

    • Update the comments function() in views.py:

      def comments(request): t = loader.gettemplate('comments.html') c = RequestContext(request, {'comments': Comments.objects.raw('SELECT * FROM myappcomments ORDER BY added DESC;')}) return HttpResponse(t.render(c))

      This should speed it up slightly.

    South

    1. Sync the database:

      $ python manage.py syncdb

    2. Create a database migration file with South:

      $ python manage.py schemamigration myapp --initial

    3. Migrate the database changes:

      $ python manage.py migrate myapp

    4. Add an index to the added field:

      added = models.DateTimeField(dbindex=True, autonow_add=True)

      Create a new migration file and then push the migration through.

    Test

    1. Test again. On my end, it's down to 694.48 ms:

      two

    As you are going through your queries, ALWAYS drop down to SQL first, as this is generally faster than using the Django ORM, and pay close attention to any queries that use WHERE, ORDER BY, FILTER BY, and any JOINs.

Learn and Earn!

Sign up for great tutorials, guides, rants, raves and opportunities to earn more money!