Skip to main content

Django select_related and prefetch_related

 


Difference between select_related and prefetch_related



Reducing SQL queries is one of the first steps when optimizing a Django project. There are two powerful methods included in the Django ORM to help us that can boost performance by creating a single more complex QuerySet rather than multiple, smaller queries.

In this project we will understand about  select_related and prefetch_related. 

Django use these two orm method to reduce sql queries in database based on different scenario. 


select_related

Lets assume  this two model we have. 

class Author(models.Model):
name = models.CharField(max_length=200)
def __str__(self):
return self.name


class Courses(models.Model):
name = models.CharField(max_length=200)
author = models.ForeignKey(Author,on_delete=models.CASCADE,related_name='courses')

def __str__(self):
return self.name


Here we have two mode. One is Author and another is Courses model. and we have added Author as foreign key in Courses mode. 

now suppose we want to get all the courses then we have to run this query

course = Courses.objects.all()

 Database query will be like this 

SELECT id, name,author_id FROM blog_courses

and now if we want to get the author it'll run a query again.example:

we have now Courses model objects in course variable. we want 0 index values author details.

courses[0].author

now it'll execute another query to get the author details.
Database query will be like this:

SELECT id, name, author_id FROM blog_courses

SELECT id, name FROM blog_author WHERE id = 1

This 2 query will execute if we want to get the author details from Courses mode.


But if we use select_related then we can reduce this expensive query execution in database

let's see what happen we use 

course = Courses.objects.select_related()

This query will also return all the objects from Courses model in course variable. In database it'll execute this sql query

SELECT id,name, author_id, FROM blog_courses INNER JOIN blog_author ON (author_id = id)


When we will use select_related() this will automatically INNER JOIN with Author model and get the all values from Author model. 

courses[0].author

Now if we can easily get the author value without executing another Query.


prefetch_related

Now let's deep drive into prefetch_related. In this scenario we want to get the Courses model objects from Author model. 
we'll query in Author model but we want data from Courses model. Author model has no connection with Courses model.

author = models.ForeignKey(Author,on_delete=models.CASCADE,related_name='courses')

Hope you can remember this field. we have used Author model as Foreign key in Courses model and we have used related_name

Using related_name and prefetch_related we can get Courses model data from Author model.

If we django query like this

author = Author.objects.prefetch_related()

Database Query:

SELECT id,name FROM blog_author ;


This will as same as Author.objects.all() query. 

But if we use

author = Author.objects.prefetch_related("courses")

In database it'll execute this sql query

SELECT id, name, author_id FROM blog_courses WHERE author_id IN (1, 2, 3, 4); 


See here it's executing a query in Courses model with where condition of Author id. Now we can get Courses Model object easily  in a single query. 



Labels:

Comments

Post a Comment

Popular posts from this blog

Implementing Advance Query Optimization in Django ORM

 Django's ORM makes database interactions seamless, allowing developers to write queries in Python without raw SQL. However, as applications scale, inefficient queries can slow down performance, leading to high latency and database load.  This guide explores advanced query optimization techniques in Django ORM to go beyond basic CRUD (Create, Read, Update, Delete) operations and improve efficiency.  1. Use QuerySet Caching to Avoid Repeated Queries Using cache reduces redundant queries for frequently accessed data. Caching helps reduce repeated database hits. 2. Avoid .count() on Large Datasets Using .count() on large tables can be expensive Inefficient way: Optimized way ( .exists() is Faster) 3. Use Indexes for Faster Lookups Indexes speed up queries on frequently filtered fields. Add db_index=True for frequently queried fields: 4. Optimize Bulk Inserts and Updated Performing operations on multiple records one by one is inefficient. Use bulk_create() for mass insert...
Post a Comment
Read more

Django pk vs id

 Django pk VS id If you don’t specify primary_key=True for any fields in your model, Django will automatically add an IntegerField to hold the primary key, so you don’t need to set primary_key=True on any of your fields unless you want to override the default primary-key behavior. The primary key field is read-only. If you change the value of the primary key on an existing object and then save it, a new object will be created alongside the old one Example: class UserProfile ( models . Model ): name = models . CharField ( max_length = 500 ) email = models . EmailField ( primary_key = True ) def __str__ ( self ): return self . name suppose we have this model. In this model we have make email field as primary key. now django default primary key id field will be gone. It'll remove from database. we can not query as   UserProfile.objects.get(id=1) after make email as primary key this query will throw an error.  Now we have to use pk  Us...
Post a Comment
Read more

Django Optimization Processes for Write Operation for Postgresql

When optimizing a Django project for large write operations, especially when dealing with PostgreSQL, there are several strategies you can employ to reduce the time it takes to perform these operations: 1. Bulk Inserts In django, we create objects using create()  . Asynchronous version is acreate() .It's a  convenience method for creating an object and saving it all in one step.  and  These are same and equivalent. The create() method is used to create and save a single object in the database. Example: Instead of inserting one row at a time, consider using Django's bulk_create() method to insert multiple rows in a single query. This reduces the overhead of multiple database round trips. Example:  The bulk_create() method is used to create and save multiple objects in the database in a single query. It accepts a list of model instances and inserts them into the database in a single batch operation, which significantly reduces the overhead compared to individ...
Post a Comment
Read more