Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Once we find dangerous queries, we should figure out how to reduce them, shouldn't we? We can use two really useful ORM functions to control how extra queries are handled.
.select_related() and .prefetch_related().
Remember the direction each of these goes. select_related is used on the model when you have the ForeignKey field. prefetch_related is used on the model that's related to by the ForeignKey field.
prefetch_related won't always reduce the number of queries. It helps to prevent extra queries being run in your templates, though, by fetching and attaching the data before the template is ever rendered.
select_related, when used correctly, can drastically reduce the number of queries you run.
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
You need to sign up for Treehouse in order to download course files.
Sign up