Detecting duplicate SQL queries in Django

Django ORM is a powerful tool to abstract away dealing with the database, but if used improperly, will happily let you generate thousands of SQL queries where one or two might suffice. The price of power is eternal vigilance - this talk will show you how.

Transcript

1. Duplicate SQL queries
   in Django
   Senko Rašić, Good Code
   

   View Slide

2. We’re hiring!
   

   View Slide

3. View Slide

4. models.py:!
   !
   class Author(Model):
   name = CharField(…)
   !
   class Book(Model):
   author = ForeignKey(Author, …)
   title = CharField(…)
   !
   views.py:!
   !
   books = Book.objects.all()
   !
   template.html:!
   !
   {% for book in books %}
   {{ book.title }}
   {% endfor %}
   

   View Slide

5. models.py:!
   !
   class Author(Model):
   name = CharField(…)
   !
   class Book(Model):
   author = ForeignKey(Author, …)
   title = CharField(…)
   !
   views.py:!
   !
   books = Book.objects.all()
   !
   template.html:!
   !
   {% for book in books %}
   {{ book.title }} by {{ book.author.name }}
   {% endfor %}
   

   View Slide

6. models.py:!
   !
   class Author(Model):
   name = CharField(…)
   !
   class Book(Model):
   author = ForeignKey(Author, …)
   title = CharField(…)
   !
   views.py:!
   !
   books = Book.objects.all().select_related(‘author’)
   !
   template.html:!
   !
   {% for book in books %}
   {{ book.title }} by {{ book.author.name }}
   {% endfor %}
   

   View Slide

7. Know thy SQL
   

   View Slide

8. django.test.TestCase.assertNumQueries()
   

   View Slide

9. Image credit: Stack Exchange
   

   View Slide

10. [SQL] repeated query (1000x): SELECT “author"."id",
    “author”.”name”
    FROM "author" WHERE "author"."id" = ?
    [SQL] 1007 queries (1000 duplicates), 945 ms SQL time,
    24351 ms total request time
    django-queryinspect
    

    View Slide

11. SELECT “author”."id", “author”.”name” FROM "author" WHERE "author"."id" = 1
    SELECT “author”."id", “author”.”name” FROM "author" WHERE "author"."id" = 2
    SELECT “author”."id", “author”.”name” FROM "author" WHERE "author"."id" = 3
    !
    …
    !
    SELECT “author”."id", “author”.”name” FROM "author" WHERE "author"."id" = ?
    django-queryinspect
    

    View Slide

12. X-QueryInspect-Num-SQL-Queries: 1007
    X-QueryInspect-Duplicate-SQL-Queries: 1000
    X-QueryInspect-Total-SQL-Time: 945 ms
    X-QueryInspect-Total-Request-Time: 24351 ms
    django-queryinspect
    

    View Slide

13. File "/vagrant/api/views.py", line 178, in get
    return self.serialize(self.object_qs)
    File "/vagrant/customer/views.py", line 131, in serialize
    return serialize(objs, include=includes)
    File "/vagrant/customer/serializers.py", line 258, in
    serialize_contact
    lambda obj: [r.name for r in obj.roles.all()]),
    File "/vagrant/customer/serializers.py", line 258, in
    lambda obj: [r.name for r in obj.roles.all()]),
    !
    [SQL] repeated query (6x): SELECT "customer_role"."id",
    "customer_role"."contact_id", "customer_role"."name"
    FROM "customer_role" WHERE "customer_role"."contact_id" = ?
    django-queryinspect
    

    View Slide

14. Copy production DB to staging server
    

    View Slide

15. View Slide