Quite often our queries span relationships,

we go from books to their authors, or authors to their books.

These jumps across relationships require Django to do extra queries and

most of the time these extra queries happen due to something you're probably

not considering as a source of query generation your templates.

Thankfully we can do something about that if we're a little smarter about how

we provide course that's to our templates.

Let's go take a look at it.

So let's take a look at our course detail view.

Let's go here and look at of course.

Yeah okay, it's got some stuff on it.

Let's look at the SQL, 61 milliseconds.

Not the fastest SQL, not super slow.

It's a decent number of queries.

Six queries for a single page.

So let's go see what we can do.

I think we can do some stuff here.

So let's see.

We're getting course and we're getting steps.

All right, so I think we can improve some of this let's do let's do a try.

And let's say that course is equal to models.course.objects,

and they're going to bring in prefetch related.

And I thought about a bit about what that does in just a minute.

So let's do quizzes and text set and then we're going to close that and

then we're going to do a get pk = pk publish = true.

And except model's dot course does not exist,

and we're going to raise HTTP 404 which do we have that imported, we don't.

Let's go ahead and import that all right.

And then, otherwise, you can delete that.

And we can indent that.

And we can make this look nicer.

Sorry I just get really particular about how my code is laid out.

Okay, so we used this new thing, we used prefetch related.

What is prefetch related do?

Prefetch related goes off and fetches.

Everything in the quiz is set and everything in the text is set.

Then pulls them back and

assigns them to our query set to the items in our query set.

So they're already available for us.

Now this is gonna generate one SQL query,

two SQL queries, three SQL queries and that's cool.

That's fine.

We were already doing one, two, three right with our sorted.

So we're not really changing our order but we're getting them right away.

We know we need them.

What is going to them all at once so pulls them back into Python and

then Python attaches them to our objects.

So we leave this we have six queries blah it's decent honest of

let's refresh this, and see how our queries changed.

We're still doing six queries.

It's slightly faster than it was before but that's okay

it's not going to do an amazing amount of stuff for us and that's fine.

We just know that it has some more stuff.

It's slightly improved we're thinking ahead.

So that's cool.

Let's see if there's a way that we can actually reduce the number of query

I've got these quizzes here.

I want to show how many questions a quiz has.

So let's go add that.

So we would go to Course Detail.

I don't need Course List open anymore, I don't need Models open anymore.

And so we have this, we have this first step in thing, right?

So I want to add, let's see, what does that look like?

I have that, okay.

So let's add a new thing on here.

Let's say if step.question_set.all.

So that means we know that it's a question or a quiz.

So then we're gonna do dt, and let's say Total Questions.

And then let's do a dd and here we want to do step.question_set.count, all right?

And indent both of those.

And then here we're going to do our endif.

All right, so now let's go and refresh this page.

And we've got one here that actually has questions and

it says total questions is 1.

And my goodness look at this nine where he's getting to duplicate

we have 116 milliseconds that's a long time 117 almost.

And if you look at this look how many times we went to courses question 1, 2,

3 we went to courses question 3 times.

That's a whole lot of times to go to courses question.

So, how do we get rid of that?

We can do that with things we've already learned.

Let's come back over here to our views and

we're prefetching quiz set and text but let's add one more here.

And let's do quiz_set_question_set.

And I didn't spell question correctly.

And so what this does is it goes okay you fetch all the questions or

all the quizzes that's cool go fetch all the questions to.

All right so now 116 milliseconds nine queries let's refresh this.

And we are now down to seven query in 67.5 milliseconds way way better.

And we only went to courses question 1 time.

So this is great this is exactly what we wanna do this kind of

major improvement where we can really really just fix it all.

So it's a lot better.

So great, now prefetch related is awesome but

there's a flip side to it, an opposite method, which is called Select related.

Select related lets us get foreign key related records.

Now usually, this means it's just getting a single extra record of a time.

We can use this on our step detail view.

So we have these two, text detail and quiz detail, right?

So, for example, let's do this on quizzes.

Let's go look at a quiz.

Here's our quiz.

All right and we've got seven queries almost over 80 seconds,

80 milliseconds rather and look

at this we're gonna course course a couple times I think we can make this better.

Okay, so let's see about addressing those here in our quiz detail.

All right, so we're gonna do a try again and

we wanna step to be equal to models dot quiz dot objects select related.

And we want to get the course, all right.

And then we do a .get course_id=course_pk rpk=step_pk and

the course_published=True.

Yeah, that works, right?

Right except if the quiz does not exist.

Then we want to raise an Http404 and

otherwise we want to do our return.

So let's take all of that out.

And let's indent that.

All right, cool.

So if we remember we were at seven queries and 80 milliseconds.

Let's refresh.

We're down to six queries and 55 milliseconds.

So that's pretty awesome.

We don't need to go and get the course on its own anymore.

We do here right.

But this is our thing up on the top.

So we can just ignore that one.

So we got a quiz one time that's going to go get our course we get question and

we get answer.

So that's cool.

We can fix this that we can get even better.

This is the kind of tuning that is so much fun to do.

So we're doing a select related course I'm going to drop this to a new line.

And you know I'm going to put each of these on their own line to.

All right?

You can probably guess what we need to add in here.

We need to add.

You go back that way pre-fetch related, and what do we need to prefetch?

Well, we need to prefetch the question set.

And if we look back over here at our SQL queries,

we're going and getting answers too.

And it's kind of weird that we're getting answers.

I mean, it's not weird,

because we literally show the answers but we have to go and fetch them.

So let's not do that.

So we get the question set, let's do question set dunder answer set.

And then let's go refresh.

So six queries, 55.5 milliseconds.

And that I save everything?

All right, six.

So, same difference but we moved the query thing up.

So, we moved these queries to the top.

So yeah, we improved it.

We made it a little bit nicer.

We could optimize a lot of this a lot further.

These two methods are so amazingly handy, but you might find that you just throw

them Aquarius just to see if they help without knowing how best to apply them.

I know I was like that for a long time.

Finally though I got them cemented in my head with two general guidelines

prefetch related is for getting lots of other items.

This is the method you want if you're following a reverse relationship

like quizzed question.

Select related as you can probably guess is for

getting smaller amounts of items, usually just one.

Usually this will relate to a foreign key field in the model you're originally

selecting, like going from question to quiz practice with these two and

you'll see your average grade times drop quite a bit.