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
2. We’re hiring!
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