Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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.

382b18eebb1386e80ff7e20fd1eb784e?s=128

Senko Rašić

October 01, 2014
Tweet

Transcript

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

  2. We’re hiring!

  3. None
  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 %}
  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 %}
  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 %}
  7. Know thy SQL

  8. django.test.TestCase.assertNumQueries()

  9. Image credit: Stack Exchange

  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
  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
  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
  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> 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
  14. Copy production DB to staging server

  15. None