The Django ORM API is incredibly flexible and abstract. However, if you do not fully understand how it operates, if you have not already, you will probably wind up with slow and heavy views. Therefore, this article offers workable answers to the N+1 and long loading time problems. For the sake of clarity, I’ll make a straightforward view that illustrates typical ORM query issues and common best practices. A python development company can support you in getting the Django query optimized.

In this blog, we will understand how to optimize ORM queries. All the steps will be discussed in depth. Stay tuned.

What is Django ORM? 

To Proceed further, first, let’s discuss the basics of Django ORM. What it is and what role it plays. Django supports object-relational mapping and it is one of its most important features.

Django’s ORM is basically a Python-based technique to build SQL to query and change your database and obtain results.

Django’s ORM, or Object Relational Mapping Layer, makes it easier to communicate with multiple database systems like MySQL, PostgreSQL, SQLite, etc. with application data. The Django Framework includes the ORM, which is an instance of the ORM idea.

Django’s Object-Relational Mapper lets you communicate with your database as you would with SQL, which is one of its most potent features. 

We have a lot of options to optimize the Queryset thanks to Django ORM. Always keep in mind what you need and don’t require. It is simple to optimize our Queryset if we are aware of our requirements.

Ways to see Database Performance? 

The maintenance of any application must include database monitoring. Early detection of database problems can help the program stay functional and accessible. Database outages may not be detected until it’s too late and the company is losing money and clients if there isn’t effective monitoring in place. So it is necessary to have a constant look at the database performance. Let’s discuss how you can do the same.

1. Used the Django Debug Toolbar to view DB performance. The below link is for Django Debug Toolbar documentation:

           https://django-debug-toolbar.readthedocs.io/en/latest/installation.html

      2. Use QuerySet.explain() to understand how specific QuerySets are executed by your database. 

      3. Use silk library to view DB performance.

https://silk.readthedocs.io/en/latest/

4. Write the below code in Django settings.py file 

• Now create two models Author and Book: 

•  Run query in python shell to see the dB performance:
•  To run the python shell write the below command
• python manage.py shell
• Write below queries to see the result 

•    One thing we must constantly keep in mind is that Queryset is lazy 
•    Nothing happens in the database until Queryset is evaluated).

For example:

• Because we haven’t called those books yet, no database action has been taken before this line. 

Query: 

• There have only been two SQL queries executed so far.
• After we call it, it acts on the database. 

Query: 

•    Now one query performs. 

We use the Below 4 Models to Explain Optimization Methods:

Author model:

Book model:

Country model: 

Author Country model:

Now start with optimization methods:

select_related():

• Use select_related() when you need foreign key-related objects.

Normal Query : 

Query: 

Optimize Query: 

Query: 

prefetch_related() : 

• Use prefetch_related() when you need ManyToMany Field-related objects. 

Normal Query :  

 Query: 

• Perform 8 queries to get all books. 

Optimize Query: 

Query: 

• Perform 4 queries to get all books.

Try to avoid database queries inside a loop:

Query inside a loop: 

Query: 

• Perform 7 queries to delete books that are written by bhargav.

Optimized way:

Query:

• Perform 5 queries to delete books that are written by bhargav.

If you want specific values then use values() and values_list():

values():

It is used as an iterable and returns dictionaries instead of model instances.

Example: 

Output: 

values_list():

• Returns a QuerySet that returns a list of tuples, rather than model instances, when used as an iterable.

Example:

Query: 

Output: 

• If you pass a single field then you write flat=True in value_list() and this method returns results that are single values rather than one-tuples. 

Query: 

Output: 

• If you set named=True then you get namedtuple. 

Query: 

Output: 

Use of  defer() and only()

defer():

• You can instruct Django not to obtain some fields with deferring if your Django model contains some fields that have a lot of data but you don’t require those columns for a specific query ().
• Delay the attribute-level work.
• Some Model fields, such as the loading of main key fields, cannot be delayed.
• Multiple fields can be passed to defer ().

Example:

Query: 

• In the above image, we see that the query gets an id and name field.

only():

• only() is Opposite of defer() . 
• If you have fewer fields that you want to retrieve than those you don’t, you can use only() to retrieve only the fields provided as arguments.

Example:

Query: 

• In the above image, we see that the query gets an id and email field.
• We use only() and defer() in the same query.

Use foreign key values directly:

Don’t do:

Example: 

Query: 

• In the image see that if we don’t use a foreign key directly then it takes 5 queries. 

Do: 

Query: 

• In the image see that if we use a foreign key directly then it takes 4 queries 

exists() and count():

exists():

• If you want to check if the queryset is empty or not then use exists().

Don’t do: 

count():

Don’t do:

• It Evaluates the entire query set 

Do: 

• It Executes more efficient SQL to determine the count. 

update():

• If you want to update the same field with the same value in multiple objects then use the update() method.
• update method works with queryset.

Example: If you want to update multiple author countries who belong to India then you don’t use a for loop with a saving method. Use the update() method.

Don’t Do : 

Query: 

• In this way update query performs for every object.

Do: 

Query: 

• In this way in one query update all objects.

bulk_create():

• This method inserts a provided list of objects into the database in an efficient manner.
•  The model’s save method will not be called and the pre_save and post_save signals will not be sent.
• It does not work with child models in a multi-table inheritance scenario. Also doesn’t work with many-to-many relationships. 

Syntax:

bulk_create(objs,batch_size=None,ignore_conflicts=False)

Here,

batch_size=It controls how many objects are created in a single query.

Example: If you want to enter multiple countries in the country table then use bulk_create().

Filter():

• It returns a new QuerySet which contains objects matching the specified lookup parameters.

Example:

If you want to name an author who lives in an Indian country then you use the filter method.

Code for getting Indian authors:

Code for shown Indian authors:

Output:

Query: 

Conclusion 

This is how Django ORM optimization works. Even though optimization is complicated, following a few basic guidelines can help a lot. 

• Make it a routine to isolate code and log the queries it generates.
• Loops shouldn’t be used for queries.
• Recognize the ORM’s data caching methods
• Recognize when Django will ask a question.
• Don’t try to achieve too much at the cost of clarity.