Optimize Your Django queries with select related, prefetch related and Django Debug Toolbar 👨🏻‍💻

hi guys in this video I'm going to show you how to use select related and prefetch related to optimize the speed of your queries so you won't have a messy situation like over here without further Ado let's get started guys before we get started let's take a look at what have I done so far so I started a brand new Django project and within this project I created a very simple application called posts inside of this directory in the models py file I placed two classes the first one is author and it consists of only one field called name and then we have the second one called post here we have two Fields the title and the author the author currently is a foreign key relationship to the author class above but later we will change it to a many-to-many relationship so we will pretend that a post can have many authors and then if we take a look at the viewspy file of this application we will see a prepared view for some optimization using select related and pre-fetch related so over here we are going to optimize the queries then in the settings py file I just added posts to the installed apps list and I told Django where to look for additional templates I haven't installed Django debug toolbar just yet we will do it in a second then we have the main HTML file where we are checking if the data exists and if the data does exist we are looping through the data and displaying them as a list item in other case we are just displaying that there is no data this main HTML file is placed in the templates directory of the source directory next to the base HTML file from widget inherits the base HTML file is just a simple bootstrap starter template with some adjustments okay so then the final step is to take a look at the Run changes script and here we are automating making migrations migrating and running the server all right so as the next step let's install Django debug toolbar the link to this documentation is placed in the description below so let's grab pip install Django debug toolbar let's quit the development server from running let's install Django debug toolbar then let's go to the next step and we need to add Django debug Toolbar to the installed apps list so let's go to core settings installed apps list and let's place it over here okay let's go to the next step we need to include Django debug toolbar in the URL pattern so let's do this as well so let's go to URLs and let's place it down below over here we need to import include so from Django URLs let's also import include like this let's save it and then we also need to add middleware so let's set let's copy this and let's go to the settings py file once again and let's place the debug toolbar middleware down below okay let's save this and I think this is the final step the configuration of the internal IPS let's copy it paste it down below all right let's save this and I think for our simple use case this should work so let's hit refresh of course we need to run the server python manage py run server okay let's hit refresh and there it is okay so this is what we are going to do currently if we go to the SQL section we have two queries and this is related to authentication So currently I'm logged into Django Administration if I logout we have zero queries okay and then let me log in one more time and we have two queries again all right so keep in mind that we will be working with the Django Administration that's why we will have two additional queries all right so as the next step let's create some objects in order to create some objects from the Django Administration we actually need to register them so I forgot about the step from dot models we want to import post and we also want to import author let's register these models very quickly so first of all let's place in the author and then let's do the same for the Post let's save this and let's hit refresh all right so now let's Place five outdoors and six posts so I'm going to place an author One save and add an add another one out for two out for three author for author five and let's do the same for posts so we will have post one let's assign it to author One post two let's also assign it to Alpha One post three let's assign it to l33 post for let's assign it to author for and post five let's also assign it to Outer four and uh I I mentioned that there should be six posts okay so post six and let's assign it to author five all right so as the next step let's go to the Views py file as you can see I already done the Imports so let's create a first query set called post and this is going to be post objects all so if we are fetching all the posts and I'm going to save this and then I'm going to head over to our main page hit refresh and then as you can see in the SQL we only have two queries and this is because query query sets in Django are lazy so the objects in the query sets are lazy and this basically means that they don't actually retrieve the data until we use them so currently we are not using it in any way so let's change this let's go back and let's retrieve the titles first okay so I'm going to get the title not the authors but the title and I'm going to set to to reassign the data so here we are going to use the list comprehension and I'm going to write post title for Post in posts okay let's see if this changes anything so I'm going to save this go back hit refresh and then go to SQL and as you can see we have three queries and one is related to The Post table but this isn't an issue over here so we had two queries right now we have three so if we are doing it like this where we are referring to a title which is just an ordinary Char field we don't have any issues the problem begins when we are dealing with a relationship to a different table so let's take a look at um the authors right now let's grab the author's name let's see what will happen so instead of post title I'm going to write post author name for post in posts let's save this let's hit refresh and right now as you can see we have nine queries so basically it's hitting the database with each single object as you can see we have six posts so every time with every object it's hitting the database one time all right so this can be a very big problem if you have huge amounts of data and in order to fix it we can use select related so let's do this right now so over here I'm going to rewrite the posts and this is going to be post objects objects and then select related and I want to grab all the authors at once so I don't want to grab them or every single object in the database I just want to grab them all in ones all in one query and this is exactly what I'm doing over here okay so instead of hitting the database with every single time with every single object we are grabbing them at once and then I'm just going to cut this piece of code put it below we can comment this out we don't have to but we can do it and now I'm going to save this hit refresh and now we have three queries all right so we have improving our uh queries by a lot okay so instead of nine we have three okay and imagine if we would have much much more objects okay so this could mean very big optimization depending on how much data we have all right um one thing that I want to show you also is that we are not losing any data so I'm just going to print posts values let's save this um I'm going to refresh over here we we have additional query right now but as you can see we have all the data necessary so we have the title and we have the author ID and we have the ID of our posts so we aren't losing any data right now okay so this is Select related and we can use it with a foreign key or a one-to-one relationship but if we would like to work on a many-to-many relationship or a reverse foreign key relationship in this case we would have to use refetch related so I'm going to show you uh this also on an example but we need to go to models py file first let's comment out this um this line and let's bring in the authors which now is a many-to-many relationship to the author all right so I'm going to save this and then I'm going to go to the terminal and then I'm going to run changes sh all right and if we go to the admin to the posts right now we can assign many authors to a single post so I'm going to do some changes very quickly okay all right so now let's go back to the viewspy file um let's comment this out and let's work on this prefetch related example I'm actually going to bring this post object oh I'm going to cut it and I'm going to paste it down below okay we have the posts but we also need the authors so I'm going to use a for Loop this time I could do a nest that list comprehension but it won't be a readable that much so this time I'm going to do a simple for Loop or post in posts then I'm going to grab the authors and this is going to be Outpost authors all okay and then I'm going to Loop through the outdoors so for author in our first uh first I'm going to Simply append the author name to the data so data append Alpha name all right so let's save this and then let's take a look what do we have over here all right so we have many authors and then if we go to the SQL we again um have many queries so we have six similar queries meaning that we are referring to six posts and how we could how we can optimize this well as mentioned before we can use this time prefetch related so let's go back and over here I'm just going to copy this comment this out and then paste it below and here after objects we are going to use prefetch related and we are going to use it on the authors this time so we are grabbing all the authors at once instead of and grabbing them with each single post object so let's save this right now let's go to our main page let's hit refresh and now we have four queries or queries instead of nine so the first query fetches the main objects when which in our case are the post and the second one fetches the related objects which are the authors all right so the next step is to use the prefetch class together with prefetch related to get posts of all the authors and here we will be using again the foreign key relationships so I'm going to bring this back in and comment this out I'm going to save this I'm going to quit the development server from running and then I'm going to run changes again I'm going to select the option as one and now we can go back to and the administration and let's do some changes we said the author as one so everywhere we have now the author the first author as the author of all the the posts so let's do some adjustments I'm going to set this one to author 2. I'm going to save this I'm going to select this one as outer four outer five and let's do author two doesn't really matter okay so right now we can go back to the Views py file and let's comment this out and let's write a query set for the authors so it's going to be let me just do it like this authors is equal to and then author objects objects oh okay but as mentioned before this time we want to use this query set in order to retrieve all the titles post titles of the author so um what we want to do is to place those post titles in the data so we need to look through the outers or author in authors uh of course authors there it is we want to grab the posts so author author post set oh okay so here we are grabbing all the posts of the particular out Outer and then we can Loop through those posts and add them to the data list so let's do this or Post in posts let's append the post Idol all right let's save this and let's see how this will work so here we have the posts and we have eight queries okay so this time we have five similar queries and this means it hits the database with every single author what we can do right now is to optimize this query set with the prefetch class which can be imported from Django DB models so let's bring it in from Django DB models import prefetch okay and here let's rewrite the outers so authors is equal to author objects and then all prefetch related and here we are going to use the prefetch class first of all we need to specify the relationships to prefetch so here in our case we are going to write post set okay and as the next step we need to write the query set to use for each relationship so let's write query set and in our case it's going to be post objects oh okay so now I'm going to comment this out and save the viewspy file hit refresh and we have four queries okay so we have optimized our uh queries that also for getting the posts of all the authors and I want to finish off over here this was just a very simple introduction to select related and prefetch related I hope you guys enjoyed the tutorial and if you did please consider subscribing to the channel it was very difficult to record this one because I'm currently in Alicante and I'm doing an Airbnb I have a wonderful view on the Alicante City on the sea but I have a renovation on the fifth floor so I'm living currently on the eighth floor the renovation is on the fifth floor and it's just a disaster so if you don't mind liking the video and leaving a comment I would be really really grateful because this was probably one of the most difficult old videos to to record thank you so much and hope to see you in the next one take care and bye bye

Channel: Pyplane

Views: 5,747

Keywords: django, django orm, select_related django, prefetch_related django, django performance, django programming, django tutorial 2023, web development, django tips and tricks, django debug toolbar, django coding, django database, django querysets optimization, django performance optimization, django debug toolbar profiling, python django tutorial, python django

Id: 3NDGnj19GiA

Length: 21min 19sec (1279 seconds)

Published: Fri Feb 03 2023