Tools for optimizing django ORM queries

One of Django's most powerful features is the ORM (Object Relational Mapping). It allows us to use Python code instead of SQL to interface with the database.

It offers a number of advantages:
--------------------------------
1. Because the database engine is isolated from us, we can easily move to another database system.
2. It has migration support: we can easily alter our tables by updating our models, and Django will build the migration scripts required to update the database tables.
3. It supports transactions, which means you may make numerous database updates in a single transaction and roll it back if something goes wrong.

It does, however, have several drawbacks:
-----------------------------------------
1. It's obfuscated because it's an abstraction on top of SQL, and we have no idea which SQL queries will be generated from our Python code.
2. Django has no means of knowing when we'll need to access a related table, therefore it won't perform JOINS for us.
3. The ORM offers us the false impression that what we're doing isn't costly. We have no means of knowing whether accessing an attribute in an object will cause a database query that could have been avoided with a JOIN.

To overcome the drawbacks, we must become more familiar with it and comprehend what is going on underneath the hood.
First, figure out what's going on under the hood.

First, we need to figure out what's going on in our system, which SQL queries are being used, and how much it's costing us.

Here are a few different ways to inspect SQL queries while they're being executed:

1. connection.queries
--------------------------
When debug=True, the queries that have been executed by the printing "connection.queries" can be accessed:

"connection.queries" stores a list of SQL queries in the form of dictionaries that contain the SQL code as well as the time it took to execute them.

The query list could easily become muddled. Django provides a technique to clean them up to remedy this:

from django.db import reset_queries
reset_queries()

2.shell_plus –print-sql
---------------------------
The django-extensions project is fantastic, because it includes a lot of helpful functionality.

One of them is shell plus. It's a Django shell with some added features. It will print the SQL queries as they are executed when you run your code if you call it with the —print-sql flag.

I'll use shell plus throughout this post so you can view the SQL queries that are running as the code is executed.

Here's a sample of what the output might look like:

manage.py shell_plus --print-sql
books = Books.objects.get(id=1)
SELECT "app_book"."id",
"app_book"."title",
"app_book"."pages",
"app_book"."published",
"app_book"."edition"
FROM "app_book"
ORDER BY "app_book"."id" ASC
LIMIT 1

3. third party package
--------------------------
1. "django-silk"

"django-silk" is a tool for profiling. It intercepts requests, records the SQL queries executed, and allows you to visualise the results.
You'll be able to scroll through the requests, view a list of SQL queries that were run, and examine the details of a specific query, such as which line of code caused it to run.

2. " django-debug-toolbar"

While browsing your Django project, django-debug-toolbar adds a toolbar to your browser that displays a lot of debugging information. It allows you to observe how many SQL queries were run on a given request. It's also possible to dig deeper into these searches, examine the SQL code, and discover what sequence they were run in and how long each one took.