In 250-500 words evaluate the impact of the steps you took, what their potential benefits and setbacks may be, and what you would advise as the next steps to improve the performance of this query.
- Based on the output from the explain command, determine the steps you would take (building indexes on certain columns, modifying the query, both, etc.) to get the same output, but improve overall performance.
- Execute three steps that you think would make for improvement in the performance of this query. Report their run time in the table below.
In 250-500 words evaluate the impact of the steps you took, what their potential benefits and setbacks may be, and what you would advise as the next steps to improve the performance of this query.
Select *
From Student join enrollment on student.ID = enrollment.Student_ID
join section on section.ID = enrollment.section_ID
join department on major = department.name
join faculty on faculty.id = section.faculty_ID
join address on address.id = student.address_ID
join Course on section.course_Number = course.num and section.dept_id = course.dept_ID
Where
--we want to make sure we have name information for students if we want to reach out to them
Student.Name_Last Not Like ('')
-- the theater department has asked to be out of this study
and Student.Major <> 'Theater'
--no students who have failed as we're looking for passing grades
and Grade > '1.33'
--we want to make sure we only have instructors, and the theater department is not part of this study
and Faculty.job in
(Select job
From Faculty
Where Job not in ('Administrative','General Services','Human Resources')
and Dept <> 'THT')
and Section_ID >=1
--summer courses don't always reflect accurately given their tight schedule and rapid fire delivery of materials
and Semester <> 'Summer'
--we don't want bias of an adivosr giving better grades
and Student.Advisor_ID <> Section.Faculty_ID
-- we don't want bias if a student is possibly a faculty members child
and Student.Address_ID <> Faculty.Address_ID
Order by Student.Name_Last, Grade desc, Faculty.Name_Last, Major
![▷ Run Cancel Disconnect Change Database: AShahbain
5
join faculty on faculty.id = section. faculty_ID
6
join address on address.id = student.address_ID
7
join Course on section.course_Number = course. num and section.dept_id = course.dept_ID
8
Where
9 --we want to make sure we have name information for students if we want to reach out to them
Student.Name_Last Not Like (¹¹)
10
11
the theater department has asked to be out of this study
12
and Student.Major <> 'Theater'
13
--no students who have failed as we're looking for passing grades
14
and Grade > '1.33'
15
16
17
(Select job
18
From Faculty
Results Messages Query Plan Plan Tree Top Operations
--we want to make sure we only have instructors, and the theater department is not part of this study
and Faculty.job in
Query 1: Query cost (relative to the script): 100.00%
Select * From Student join enrollment on student.ID = enrollment. Student ID join section on section.ID = enrollment.section_ID join department on...
Missing Index (Impact 30.4485): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [dbo].[enrollment] ([grade], [section_id]) INCLUDE
([student_id])
0.95%
Missing Index (Impact 63.5026): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >]ON [dbo].[enrollment] ([section_id], [grade]) INCLUDE
([student_id])
Missing Index (Impact 14.3468): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo]. [address] ([id]) INCLUDE ([street], [city],
[country], [postal_code])
ced Loops
Semi Join)
1
1.71%
C
Nested Loops
(Inner Join)
22062
3.04%
Table Scan
[faculty]
1
3.93% E
Nested Loops
(Inner Join)
40000
3.04%
Table Scan
[faculty]
1
Estimated PlanEnable Actual Plan ParseEnable SQLCMD
100000
0.1% B
A↓
Sort
10.45%
Table Scan
[address]
1
1.21% E
Merge Join
(Inner Join)
0.03% 8
-@
Compute Scalar
26732
17552
10.07%
Sort
17552
To Notebook
33.49%
唱
1.72%
26732
Clustered Index Scan
[student]. [PK_student]
Hash Match
(Inner Join)
179
0.04%
Table Scan
[section]
30.05%
618880 ↓
Table Scan
[enrollment]
A
<<I>
+
Kod](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F89ff1732-f96f-4eea-9abf-a0b34d26033a%2F26da851c-6a28-470e-9dc1-083e0012c9f1%2Fcot0axp_processed.jpeg&w=3840&q=75)
![▷ Run
5
6
7
8
9
10
11
12
13
14
15
16
17
(Select job
18
From Faculty
Results Messages Query Plan Plan Tree Top Operations
Cancel Disconnect Change Database: AShahbain
join faculty on faculty.id = section. faculty_ID
join address on address.id = student.address_ID
join Course on section.course_Number = course.num and section.dept_id = course.dept_ID
0%
SELECT
Where
--we want to make sure we have name information for students if we want to reach out to them
Student.Name_Last Not Like (¹¹)
the theater department has asked to be out of this study
and Student.Major <> 'Theater'
--no students who have failed as we're looking for passing grades
and Grade > '1.33'
Query 1: Query cost (relative to the script): 100.00%
Select * From Student join enrollment on student.ID = enrollment. Student ID join section on section.ID = enrollment.section_ID join department on...
Missing Index (Impact 30.4485): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [dbo].[enrollment] ([grade], [section_id]) INCLUDE
([student_id])
B
--we want to make sure we only have instructors, and the theater department is not part of this study
and Faculty.job in
Missing Index (Impact 63.5026): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [dbo].[enrollment] ([section_id], [grade]) INCLUDE
([student_id])
Missing Index (Impact 14.3468): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >]ON [dbo]. [address] ([id]) INCLUDE ([street], [city],
[country], [postal_code])
1
0% B
Nested Loops
(Inner Join)
1
0.01%
Nested Loops
(Inner Join)
16
0.03%
Table Scan
[department]
Estimated PlanEnable Actual Plan ParseEnable SQLCMD
1
196
0.1%
2↓
Sort
0.03%
Table Scan
[course]
1
0.95%
Nested Loops
(Left Semi Join)
1
1.71% E
Nested Loops
(Inner Join)
22062
3.04%
Table Scan
[faculty]
1
3.93%
Nested Loops
(Inner Join)
40000
To Notebook
3.04%
Table Scan
[faculty]
1
100000
0.1%
A↓
Sort
10.45%
Table Scan
[address]
IL
le
85
#](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F89ff1732-f96f-4eea-9abf-a0b34d26033a%2F26da851c-6a28-470e-9dc1-083e0012c9f1%2Fte5wxgh_processed.jpeg&w=3840&q=75)
Step by step
Solved in 4 steps
