Optimize Django queries within 10 mins.

Introduction:

Hi readers, I hope you guys are doing good and safe, Today i came up with new topic of Django framework. As i wrote many i article on Django framework , well if you are new you can see my previous post about Django framework. This framework written in python language and its very secure even some famous websites such as Instagram, Reddit and even Nasa’s website itself developed in this framework because it comes with a lot of per-build features such as Admin pannel, ORM queryset and generic methods which save lot of time and make web development process extremely fast. But when we develop a web app which have large database and thousands of daily visitors . So in that cases database queries optimization is like a oxygen, if don’t your web server can’t handle this much load and eventually down. For tackling these kind of problems optimization act as a lifesaver.

Requirements:

• Python 3
• Django 2.2
• Django rest framework
• Django debug toolbar

what you will learn from this tutorial:

• rest API development with Django rest frame work
• optimizing database queries using select_related and prefetch_related
• play with Django Queryset
• Using Django generic methods

Database structure

For this implementation i used SQLite relational database, Which have three tables Publisher, Book, Store each of them is related using Foreign key and many to many relation.

As you can see in the bellow image Store table is connected with book table using many to many relationship and Book table further connected with Publisher using foreignkey (one to many relation ship).

I pre populated database for this query optimization testing, I user faker module to insert fake data. you guys can view this database’s data by its admin panel or you guys can view this db.slite3 file using sql browser.

admin panel link => 127.0.0.1:8000/admin/

username = admin

password = admin

how to run this code:

• clone or download it from my github repo
• install requirements.txt file using this cmd (pip3 install requirements.txt)
• run this project using this cmd (python3 manage.py runserver )

With and without optimization:

As you guys can see I have created two classes here one is normal StoreList which render data without and optimization and another class named as OptimizedStoreList where I used prefetch_related and select_related methods for obtaining data without any database query.

For viewing number of queries i used Django debug toolbar , which is Django profiler, it depicts us various details such as number of database queries, computation time, cache and signal requests for specific page. Sounds interesting ? you guys can explore it from official docs.

select_related and prefetch_related queryset’s methods which allow us to take relational db data in a same query.
— for ForeignKey relationship we use select_related method
— for ManyToManyField relationship we use prefetch_related method

For StoreList with this default query set it shows 185 databases queries which take 55.45ms time in my local system.

For OptimizedStoreList, here I used select related and prefetch related and the number of database queries reduced to 5 from 185.

Conclusion:

If you guys developing big application and big database spend your time on query optimization it is totally worth it and it will improve your app speed and reduce computation cost.