Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I welcome back to modulate This is less than 8.6 challenge one. Now we've already gone over the top bullet points, but the bottom to identify what where we are going to do in this challenge, we're going to use explain to identify problems in the provided query,
00:17
and it's also gonna be our goal to remove all full table scans
00:21
Now. With that, let's go ahead and head over to the requirements document and look at the details of what we need to dio. All right, here we are at the requirements document. This is intermediate sequel Challenge one. Optimize quarry, no full table scans, and this is challenged one for modulate.
00:39
So the sequel program requirements are as follows, utilizing the axiomatic a database and the provided query. Remove any and all full table scans. Senior programmer instructions
00:50
Examine the providing query.
00:52
We want to ensure that code we push out is at very least optimized to a reasonable standard.
00:58
Remove any and all full table skins. You may add indexes if necessary. You can also rearrange the query and two equivalent expressions.
01:07
Patterns observed in the data can be assumed toe always be true. Now if you are in a production area, you, of course, will want to confirm that a pattern is always true. But here we could just assume that we if we observe a pattern, it's always true.
01:23
So the things that we will need to solve this problem include the explain command,
01:30
new indexes and the sub string function.
01:33
So go ahead and Paul's the video and see if you can solve it on your own before reviewing the answer.
01:41
Okay, let's head over and see how we solve this problem.
01:46
All right, we have the query in front of us. Let's see if we can work through this and find the full table scans and remove them to get started with. I'm just gonna run the query with the explain at the top.
01:57
I can see that I have to levels and they are both of type. All which is not good. It means doing a full table scan on both levels.
02:05
So
02:07
let's jump in the middle here on this derive table and put the explain command in there and just run this portion.
02:15
We can see that we still have a type of all.
02:17
Let's see if we can resolve that somewhere. Somehow. That's coming from the PM Tran table, and it's using the company I D and batch number. So let's go look at that table
02:29
and see if we haven't any indexes that have those two columns
02:37
and I seek plenty of company I ds. But I don't see any batch numbers. So let's go ahead and add that to create a new index company I D
02:46
and batch number. I can find it.
02:52
We put in alphabetical order. That should make it easier.
02:54
There it is
02:57
and hit. OK,
02:59
I didn't put on unique. I'm pretty sure it's not a unique configuration. So I'm going to save that.
03:07
It looks like I have an index with a knit with the same name already.
03:10
So I'm just gonna go ahead and type in an underscore to here,
03:15
and I'm just going to save that.
03:19
Okay,
03:20
now I'm gonna run explain on this query again.
03:23
Now I have a reference
03:27
that's not as good as it could be.
03:30
I know this wild card is dangerous on both ends because it causes the sequel engine to have to do a lot of checks. So let's get rid of that on the left and see if that improves things even more.
03:44
So now we have a range, which is better than a breath,
03:47
but I think we can even make it even even better.
03:52
Let's see if we can do this. Let's use the sub string command
03:57
and I'm gonna copy this field out,
04:02
started position one and go to out,
04:04
and that is required to equal
04:09
AP because that's obviously what we're looking for.
04:12
And I've made that determination
04:15
because if I look at the pattern of the table
04:17
on the batch number, I can see that AP starts at Position one and goes to Ford,
04:24
and we're allowed to assume that these patterns are always true. So
04:29
I'm gonna use the substrate command.
04:31
So let's see, that improves things again.
04:35
So we're back to reference.
04:40
Okay, I double checked and references actually better than range, so that's a better condition to be in.
04:47
Now. Let's get rid of this, explain command here and rerun the whole query
04:54
and see if we have any improvements
04:56
and we do. We can see that one of the layers is still referenced.
05:00
However, it appears that the outer layer batch is not doing a full table scan. Now we're using the columns company I D and posted.
05:09
Let's go look at the batch table and see if we can improve that by targeting an index or creating an index.
05:17
So we need company I D and posted.
05:25
Okay, again, we have plenty of company I d. But no posted. Let's go ahead and create a new index.
05:31
So a company I d
05:36
and posted,
05:39
I'm confident they aren't unique.
05:42
I'm gonna hit okay
05:44
and save it.
05:45
Persist,
05:46
let's go back.
05:48
Run the whole query again.
05:54
And they are both of type reference.
05:56
So we have successfully removed all the table scans and that completes the requirement document.
06:01
So the way we solve this is that we implemented some new indexes and we made it a little bit better by using the sub string function to check the value of a field as opposed to using a wild card, which would take just a tad bit more computation to ah, do all those checks. So I hope you're able to get that working, and I hope to see in the next lesson.
06:21
Thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor