$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

  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