$30 off During Our Annual Pro Sale. View Details »

Detecting duplicate SQL queries in Django

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.

Senko Rašić

October 01, 2014
Tweet

More Decks by Senko Rašić

Other Decks in Programming

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