Related Records

Quite often our queries span relationships, 0:00 we go from books to their authors, or authors to their books. 0:03 These jumps across relationships require Django to do extra queries and 0:06 most of the time these extra queries happen due to something you're probably 0:10 not considering as a source of query generation your templates. 0:13 Thankfully we can do something about that if we're a little smarter about how 0:17 we provide course that's to our templates. 0:20 Let's go take a look at it. 0:22 So let's take a look at our course detail view. 0:24 Let's go here and look at of course. 0:27 Yeah okay, it's got some stuff on it. 0:30 Let's look at the SQL, 61 milliseconds. 0:31 Not the fastest SQL, not super slow. 0:34 It's a decent number of queries. 0:37 Six queries for a single page. 0:39 So let's go see what we can do. 0:42 I think we can do some stuff here. 0:43 So let's see. 0:46 We're getting course and we're getting steps. 0:47 All right, so I think we can improve some of this let's do let's do a try. 0:51 And let's say that course is equal to models.course.objects, 0:56 and they're going to bring in prefetch related. 1:01 And I thought about a bit about what that does in just a minute. 1:05 So let's do quizzes and text set and then we're going to close that and 1:08 then we're going to do a get pk = pk publish = true. 1:14 And except model's dot course does not exist, 1:21 and we're going to raise HTTP 404 which do we have that imported, we don't. 1:28 Let's go ahead and import that all right. 1:35 And then, otherwise, you can delete that. 1:40 And we can indent that. 1:47 And we can make this look nicer. 1:50 Sorry I just get really particular about how my code is laid out. 1:59 Okay, so we used this new thing, we used prefetch related. 2:02 What is prefetch related do? 2:06 Prefetch related goes off and fetches. 2:07 Everything in the quiz is set and everything in the text is set. 2:11 Then pulls them back and 2:13 assigns them to our query set to the items in our query set. 2:15 So they're already available for us. 2:19 Now this is gonna generate one SQL query, 2:21 two SQL queries, three SQL queries and that's cool. 2:26 That's fine. 2:29 We were already doing one, two, three right with our sorted. 2:30 So we're not really changing our order but we're getting them right away. 2:35 We know we need them. 2:40 What is going to them all at once so pulls them back into Python and 2:40 then Python attaches them to our objects. 2:43 So we leave this we have six queries blah it's decent honest of 2:46 let's refresh this, and see how our queries changed. 2:51 We're still doing six queries. 2:55 It's slightly faster than it was before but that's okay 2:57 it's not going to do an amazing amount of stuff for us and that's fine. 3:00 We just know that it has some more stuff. 3:04 It's slightly improved we're thinking ahead. 3:06 So that's cool. 3:10 Let's see if there's a way that we can actually reduce the number of query 3:11 I've got these quizzes here. 3:14 I want to show how many questions a quiz has. 3:15 So let's go add that. 3:19 So we would go to Course Detail. 3:21 I don't need Course List open anymore, I don't need Models open anymore. 3:23 And so we have this, we have this first step in thing, right? 3:27 So I want to add, let's see, what does that look like? 3:32 I have that, okay. 3:38 So let's add a new thing on here. 3:39 Let's say if step.question_set.all. 3:42 So that means we know that it's a question or a quiz. 3:49 So then we're gonna do dt, and let's say Total Questions. 3:53 And then let's do a dd and here we want to do step.question_set.count, all right? 3:59 And indent both of those. 4:05 And then here we're going to do our endif. 4:15 All right, so now let's go and refresh this page. 4:19 And we've got one here that actually has questions and 4:23 it says total questions is 1. 4:26 And my goodness look at this nine where he's getting to duplicate 4:28 we have 116 milliseconds that's a long time 117 almost. 4:33 And if you look at this look how many times we went to courses question 1, 2, 4:36 3 we went to courses question 3 times. 4:41 That's a whole lot of times to go to courses question. 4:43 So, how do we get rid of that? 4:46 We can do that with things we've already learned. 4:48 Let's come back over here to our views and 4:50 we're prefetching quiz set and text but let's add one more here. 4:53 And let's do quiz_set_question_set. 4:58 And I didn't spell question correctly. 5:02 And so what this does is it goes okay you fetch all the questions or 5:05 all the quizzes that's cool go fetch all the questions to. 5:08 All right so now 116 milliseconds nine queries let's refresh this. 5:12 And we are now down to seven query in 67.5 milliseconds way way better. 5:18 And we only went to courses question 1 time. 5:26 So this is great this is exactly what we wanna do this kind of 5:29 major improvement where we can really really just fix it all. 5:32 So it's a lot better. 5:37 So great, now prefetch related is awesome but 5:38 there's a flip side to it, an opposite method, which is called Select related. 5:42 Select related lets us get foreign key related records. 5:47 Now usually, this means it's just getting a single extra record of a time. 5:51 We can use this on our step detail view. 5:54 So we have these two, text detail and quiz detail, right? 5:58 So, for example, let's do this on quizzes. 6:01 Let's go look at a quiz. 6:04 Here's our quiz. 6:06 All right and we've got seven queries almost over 80 seconds, 6:07 80 milliseconds rather and look 6:12 at this we're gonna course course a couple times I think we can make this better. 6:16 Okay, so let's see about addressing those here in our quiz detail. 6:20 All right, so we're gonna do a try again and 6:27 we wanna step to be equal to models dot quiz dot objects select related. 6:32 And we want to get the course, all right. 6:37 And then we do a .get course_id=course_pk rpk=step_pk and 6:42 the course_published=True. 6:48 Yeah, that works, right? 6:51 Right except if the quiz does not exist. 6:58 Then we want to raise an Http404 and 7:06 otherwise we want to do our return. 7:11 So let's take all of that out. 7:14 And let's indent that. 7:18 All right, cool. 7:21 So if we remember we were at seven queries and 80 milliseconds. 7:22 Let's refresh. 7:25 We're down to six queries and 55 milliseconds. 7:27 So that's pretty awesome. 7:30 We don't need to go and get the course on its own anymore. 7:31 We do here right. 7:35 But this is our thing up on the top. 7:37 So we can just ignore that one. 7:39 So we got a quiz one time that's going to go get our course we get question and 7:40 we get answer. 7:44 So that's cool. 7:46 We can fix this that we can get even better. 7:47 This is the kind of tuning that is so much fun to do. 7:50 So we're doing a select related course I'm going to drop this to a new line. 7:53 And you know I'm going to put each of these on their own line to. 7:59 All right? 8:06 You can probably guess what we need to add in here. 8:07 We need to add. 8:09 You go back that way pre-fetch related, and what do we need to prefetch? 8:12 Well, we need to prefetch the question set. 8:17 And if we look back over here at our SQL queries, 8:20 we're going and getting answers too. 8:25 And it's kind of weird that we're getting answers. 8:26 I mean, it's not weird, 8:29 because we literally show the answers but we have to go and fetch them. 8:30 So let's not do that. 8:35 So we get the question set, let's do question set dunder answer set. 8:36 And then let's go refresh. 8:42 So six queries, 55.5 milliseconds. 8:44 And that I save everything? 8:50 All right, six. 8:56 So, same difference but we moved the query thing up. 8:57 So, we moved these queries to the top. 9:00 So yeah, we improved it. 9:02 We made it a little bit nicer. 9:05 We could optimize a lot of this a lot further. 9:07 These two methods are so amazingly handy, but you might find that you just throw 9:10 them Aquarius just to see if they help without knowing how best to apply them. 9:15 I know I was like that for a long time. 9:18 Finally though I got them cemented in my head with two general guidelines 9:20 prefetch related is for getting lots of other items. 9:25 This is the method you want if you're following a reverse relationship 9:28 like quizzed question. 9:31 Select related as you can probably guess is for 9:33 getting smaller amounts of items, usually just one. 9:35 Usually this will relate to a foreign key field in the model you're originally 9:38 selecting, like going from question to quiz practice with these two and 9:42 you'll see your average grade times drop quite a bit. 9:46