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.

Senko Rašić

October 01, 2014
Tweet

More Decks by Senko Rašić

Other Decks in Programming

Transcript

  1. 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 %}
  2. 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 %}
  3. 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 %}
  4. [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
  5. 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
  6. 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