The explain command ---------------------------- Select * From Student join enro

Question

The explain command

----------------------------

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.course_number 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

------------------------------------------------------------------------------------------------

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.

PgAdmin3 File Edit Query Favourites Macros View Window Help ? 4) 28% G) Sat 2:18 PM a?E Query- assign1 on postgres@localhost:5432 ??????? 1??1 p, 1??/ut3 u l e e i ? | : Dassign l on postgres@local host:54 3 2 SQL Editor Graphical Query Builder rv Object browser Scratch pad ? Server Groups Previous queries DeleteDelete All Servers (1) INF407 imosc Query 1' ® ? Explain analyze Select Databases ( ? Li assign! ? From Student join enrollment on student.ID enrollment.Student ID Catalo ?Extensi Foreig join section on section.ID enrollment.section-1D join department on major- department.nane join faculty on faculty.id section.faculty_ID join address on address.id - student.address TD join Course on section.course Number - course.course-number and section,dept.id - course. deptID SchemWhere publ | --we want to make sure we have name information for students if we want to reach out to them Student.Name Last Not Like C' the theater deoartment has asked to be out of this studv Sel Output pane [Query 1] Data Outpu Messages History QUERY PLAN Slony O postgres %, Tablespace ?Group Roles A. Login Roles! (cost-28469.02. .28471.65 rows=1852 width-381) (actual time-3429.388.. 3429.312 rows-120 Loops-1) > 2 Sort Key: student.name last, enrollment.grade DESC, faculty.name last, student.major Sort Method: quicksort Memory: 61kB 3 4 Nested Loop Hash Join (cost-11549.26..28078.87 rows-1052 width-234) (actual time-3286.629. .3428.127 roNs-120 loops-1) ? (cost-11549.56 ..28416.22 rows-1052 width-301) (actual time-3286.650. .3429.037 rows-12a loops-1) Hash Cond: (Cstudent.major)::text- (department.name)::text) -> Hash Join (cost-11547.90, .28862. 81 rows-1117 width-140) (actual time-3286.586. .3427.999 rows-138 loops-11 Hash Cond: (student. id-enrollment, student-id) Join Filter: (Cstudent.advisor-id section.faculty id) AND (student.addressid faculty.addressid)) -Sea Scan on student Ccost-0.03..14687.80 rows-584090 width-53) Cactual time-0.064..105.75 10 5 rows-584685 loops-1 Filter: (((name-last) ;;text !- Rows Removed by Filter: 33835 ..;; text) AND CCmajor);:text 'Theater, ;;text)) 12 13 -Hash (cost-11532.98..11532.98 rows-1194 width-87) (actual tie-3283.437. .3283.437 rows-172 loops-1) OK. Unix Ln 1, Col 17, Ch 17 51 rows 3.4 secs Retrieving details on database assign1... Done assignl on postgres@local host:5432 anL 25 msec

Explanation / Answer

Q1) Execute three steps that you think would make for improvement in the performance of this query.

1.While creating index, it should be taken into consideration which all columns will be used to make SQL queries and create one or more indexes on those columns. Practically, indexes are also a type of tables, which keep primary key or index field and a pointer to each record into the actual table.

eg:- CREATE UNIQUE INDEX STUDENT_INDEX ON STUDENT(ID, Last_Name, Major, Grade);

Similarly we need to create indexes for other Tables and while creating indexes mention the column names that are being used in the SQL Query.

2. We can remove unwanted Relation/Tables from the JOIN Query eg:- Address, if we arent displaying any columns related to ADDRESS Table, we can remove so that cost of JOIN Query execution will be reduced and the query will execute in relative less time.

3. We can have certain constraints in the CREATE TABLE itself so that we dont put such filters in SQL Select Query:- LAST_NAME VARCHAR2(100) NOT NULL, if we have this Constraint in CREATE TABLE STUDENT, we can avoid this WHERE clause Student.Name_Last Not Like (''). Thus we can remove unwanted filters from the Select Query so as to enable less filtering of results obtained. This can speedup Query execution.

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.