How to Use Select Related and Prefetch Related in Django

hey everyone in today's video i want to explain what the select related and prefetch related lookups do in django so these become really important as the data in your database grows you'll find that you'll need them eventually to avoid performance issues so before i get into how they work exactly i just want to tell you that i have a course available called understanding django and if you want to learn more things from me about working with the database in django or pretty much any of the standard features of django you can check out this course so i'll leave a link in the description below so you can take a look at it now for the example i'm going to be using django debug toolbar just to illustrate what's going on i'm not going to show you how this is set up but i'll link to another video that shows you how the debug toolbar works it's not really important how it's set up but what's important is what i can get from it and i'll show you that in a moment also i have a template that's just blank and that's for the django debug toolbar purposes then i have a model that represents an e-commerce store so i have a customer model product model order model and a line item model and then i have the script to just add some random data into my database so i'll be using mostly the order model here real really the order model only directly and then using that order model i'll get the customer and the product as well so the first thing i want to do is i want to show you what happens when i do a regular query without adding anything extra so what i'm going to do is i'm going to query my order model so orders equals order.objects.all and since query sets in django are lazy you have to actually use it for something the result before it will execute the query against the database so what i'll do is i'll just loop over the orders so for order and orders i'll loop over it and i'll just assign it to a variable let's say temp equals order and now if i go to my page and run it i can see the query that gets executed over here by clicking sql and it tells me it's calling select star from example order so if i just expand it it's just giving me all of the actual columns which is just star if you know sql so i'm just selecting all the data from my order table and it tells me the time 0.54 milliseconds so it's pretty quick it's actually pretty slow but it's the debug environment that i'm working on that is slow not so much the overall query so now if i go back to my code and instead of temp equals order i say order dot customer right so this customer is from a relationship here so this foreign key relationship back to the customer so if i wanted to get something like the customer's name that is on a particular order i would just call order dot customer and then i can do something like dot name after customer if i wanted their name so now let me go back to my app and refresh it and you can see it's already taking longer so i'll just sit here and wait until it's done okay and it just finished for me but i edited out the weight but if i go over here to sql again and i wait for it to load i can already see it's taken 422 milliseconds so it's a lot slower than the original and we see here instead of one query i have a bunch of queries so as you can imagine this is pretty inefficient and my order table has 5 000 orders in it so what's happening is for each order it's executing another query to get the customer information for that order and this is where select related helps you so select related what it does is it's a way to tell django what you want to be included in your query so typically it only includes the data from the model that you're querying so the table associated with that model so typically it only includes data from the order table and then when you want to get data from a table that is related to the order table through a foreign key relationship it will perform another query for each order that you have and the more orders that you have in the database the longer this will take and this kind of goes against the idea of relational databases in the first place like the whole point is you can write queries that will give you all the data that you want at once so to handle this what you can do is use select related so before the dot all all i have to do is call select underscore related and then inside of here the string inside of select related i just tell django the field that i'm looking to have returned along with the order data so in this case it's going to be customer and just know when you're using select related you have to be using a foreign key relationship so here customer here is a foreign key relationship and now when i do this let's see if there is any change so i'll go back here refresh and it already loaded if i click here we see i have one query again it was only 0.39 milliseconds if i open this up we see the query here is more complicated but it's just one so it's basically combining the order table and the customer table to give me all of the data at the same time this means i'll be able to get the customer name for example on an order without having to write another query or execute another query against the database to get that information so that's where select related comes into play and a lot of people run into an issue where they don't really understand why their queries are getting slow it's simply because more and more data is being added in the database so when you first start building your app you're probably only using test data and then you launch the app there's really no data but as time goes on more and more data gets added and then your queries get slower and slower and part of the reason is because you don't have something like select related or as we'll see in a moment prefetch related in cases where it would be more efficient so now let me show you prefetch related and it's related to select related so prefetch related is kind of the equivalent of select related in a sense but it works with many to many fields instead of just foreign keys so the reason why you need prefetch related instead of just select related for both is because the mini to mini relationship occurs over multiple tables it's not just two tables that are concerned it's really three so the two main tables in the association table so it's not as easy to return the data in the same way as the select related given the constraints of the django query system orm so you kind of expect your data to be in a certain format when you use the query set here so they came up with prefetch related to give you an alternative to using this kind of idea with many-to-many relationships instead of just one submini or meaning to one however they call it in django if you were actually writing queries directly then there wouldn't be that big of a difference between what you do for the select related and the prefetch related but for django purposes there's a difference so what i'll do is i'll comment this one out and i'll just do the same thing so first i'll do the one without using it so order.objects.all and what i want to do is instead of just having the customer here just one thing i want to loop over all the products in an order so i can do for product in order.products.all and then i'll do the same thing where i'm just basically assigning to a variable this doesn't actually do anything so product and now i'll go to my app and refresh okay and the query just finished for me and i'll edit out the weight again so here once again it performs 5001 queries pretty much one query for every order that i have in the database and we see it till considerably longer and i see i have all these queries here so select from example product inner join example line item and it just gives me the data for each one of these so it takes a long time and prefetch related will dramatically reduce that time so pre fetch related and then just like the select related i specify the field so products and prefetch related works on mini to mini i'll go back over here refresh it's a little bit slower than the original example and like i said the slowness mostly has to do with my computer not so much the query but i go over here and we see i have two queries so the second query is pretty big but this is just the way that django handles these prefetch related queries so basically what it does is it figures out every id that is returned from the first query and then it takes those ids and it gets everything in the second query and then it uses python to basically join the data together so you can use it in a way that it seems like it's all from one query when it's really two queries combined so i hope that was able to help you if you've ever had database troubles in django like you had slow queries maybe this was the issue and i'm sure this will help if in the future you build a django app and you want to make sure that you don't have any performance issues going forward so that's it for this video if you have any questions feel free to leave a comment down below if you like this video please give me a thumbs up and if you haven't subscribed to my channel already please subscribe so thank you for watching and i will talk to you next time

Channel: Pretty Printed

Views: 4,236

Keywords: select_related, prefetch_related, select related, prefetch related, django, queryset, django queryset, queryset performance

Id: TzgZBg7oXNA

Length: 9min 34sec (574 seconds)

Published: Wed Jul 28 2021