asked
query performance tuning
Hi all, I have a query I wrote for an SSRS report. When I run the query in SSMS its slow like it takes 7 minutes to run but eventually gives me results. When I try to run the report, it does not ever finish loading. In fact it crashes my visual studio. Can anyone give me some insight on how to fix this? Thanks, With cte As (Select STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM, STUDENT_ACAD_PROGRAMS_VIEW.STP_DEPT, STUDENT_ACAD_PROGRAMS_VIEW.STP_DEGREE, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE, STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE, Row_Number() Over (Partition By STUDENT_ACAD_PROGRAMS_VIEW.STUDENT_ID Order By STUDENT_ACAD_PROGRAMS_VIEW.STP_ACAD_LEVEL, STUDENT_ACAD_PROGRAMS_VIEW.STP_DEGREE, STUDENT_ACAD_PROGRAMS_VIEW.STP_DEPT, STUDENT_ACAD_PROGRAMS_VIEW.STP_ACADEMIC_PROGRAM) rn From TERMS Right Join STUDENT_ACAD_PROGRAMS_VIEW On STUDENT_ACAD_PROGRAMS_VIEW.STP_START_DATE <= TERMS.TERM_END_DATE And (STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE Is Null Or STUDENT_ACAD_PROGRAMS_VIEW.STP_END_DATE >= TERMS.TERM_END_DATE) Where TERMS.TERMS_ID = Upper(@Term) And STUDENT_ACAD_PROGRAMS_VIEW.STP_CURRENT_STATUS <> 'Potential' And STUDENT_ACAD_PROGRAMS_VIEW.STP_CCDS1 Is Null) Select COURSE_SECTIONS1.SEC_SUBJECT As [Primary Subject], COURSE_SECTIONS.SEC_SHORT_TITLE, COURSE_SECTIONS.SEC_SUBJECT, Case When SubQuery.STUDENT_CURRENT_TYPE Is Null Then 'Unknown' When (Query1.STP_DEPT_1 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_2 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_3 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_4 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_5 = COURSE_SECTIONS.SEC_SUBJECT) And SubQuery.STUDENT_CURRENT_TYPE = 'LLM' Then 'Other LLM' When (Query1.STP_DEPT_1 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_2 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_3 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_4 = COURSE_SECTIONS.SEC_SUBJECT Or Query1.STP_DEPT_5 = COURSE_SECTIONS.SEC_SUBJECT) And SubQuery.STUDENT_CURRENT_TYPE = 'MS' Then 'Other MS' When SubQuery.STUDENT_CURRENT_TYPE = 'JD' Or SubQuery.STUDENT_CURRENT_TYPE = 'JDLLM' Then SubQuery.STUDENT_CURRENT_TYPE Else 'Other' End As [Student Category], STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID, COURSE_SECTIONS.SEC_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_LAST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_FIRST_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_MIDDLE_NAME, STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CREDITS, Query1.ACAD_PROGRAM_1, Query1.ACAD_PROGRAM_2, Query1.ACAD_PROGRAM_3, COURSE_SECTIONS1.SEC_NAME As [Primary Name] From COURSE_SECTIONS Inner Join COURSE_SEC_XLISTS On COURSE_SECTIONS.SEC_XLIST = COURSE_SEC_XLISTS.COURSE_SEC_XLISTS_ID Left Join COURSE_SECTIONS COURSE_SECTIONS1 On COURSE_SEC_XLISTS.CSXL_PRIMARY_SECTION = COURSE_SECTIONS1.COURSE_SECTIONS_ID Left Join STUDENT_ENROLLMENT_DETAIL_VIEW On STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_COURSE_SECTION_ID = COURSE_SECTIONS.COURSE_SECTIONS_ID Left Join (Select R1.STUDENTS_ID, R1.STU_TYPES As STUDENT_CURRENT_TYPE From STU_TYPE_INFO As R1 With(NoLock) Where R1.POS = (Select Min(R2.POS) As MINPOS From STU_TYPE_INFO As R2 With(NoLock) Left Join TERMS On R2.STU_TYPE_DATES < TERMS.TERM_START_DATE Where R1.STUDENTS_ID = R2.STUDENTS_ID And TERMS.TERMS_ID = Upper(@Term) Group By R2.STUDENTS_ID, TERMS.TERMS_ID)) As SubQuery On STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID = SubQuery.STUDENTS_ID Left Join (Select t1.STUDENT_ID, t1.STP_ACAD_LEVEL ACAD_LEVEL_1, t1.STP_DEGREE STP_DEGREE_1, t1.STP_DEPT STP_DEPT_1, t1.STP_ACADEMIC_PROGRAM ACAD_PROGRAM_1, t1.STP_START_DATE STP_START_DATE_1, t1.STP_END_DATE STP_END_DATE_1, t2.STP_ACAD_LEVEL ACAD_LEVEL_2, t2.STP_DEGREE STP_DEGREE_2, t2.STP_DEPT STP_DEPT_2, t2.STP_ACADEMIC_PROGRAM ACAD_PROGRAM_2, t2.STP_START_DATE STP_START_DATE_2, t2.STP_END_DATE STP_END_DATE_2, t3.STP_ACAD_LEVEL ACAD_LEVEL_3, t3.STP_DEGREE STP_DEGREE_3, t3.STP_DEPT STP_DEPT_3, t3.STP_ACADEMIC_PROGRAM ACAD_PROGRAM_3, t3.STP_START_DATE STP_START_DATE_3, t3.STP_END_DATE STP_END_DATE_3, t4.STP_ACAD_LEVEL ACAD_LEVEL_4, t4.STP_DEGREE STP_DEGREE_4, t4.STP_DEPT STP_DEPT_4, t4.STP_ACADEMIC_PROGRAM ACAD_PROGRAM_4, t4.STP_START_DATE STP_START_DATE_4, t4.STP_END_DATE STP_END_DATE_4, t5.STP_ACAD_LEVEL ACAD_LEVEL_5, t5.STP_DEGREE STP_DEGREE_5, t5.STP_DEPT STP_DEPT_5, t5.STP_ACADEMIC_PROGRAM ACAD_PROGRAM_5, t5.STP_START_DATE STP_START_DATE_5, t5.STP_END_DATE STP_END_DATE_5 From cte t1 Left Join cte t2 On t1.STUDENT_ID = t2.STUDENT_ID And t2.rn = 2 Left Join cte t3 On t1.STUDENT_ID = t3.STUDENT_ID And t3.rn = 3 Left Join cte t4 On t1.STUDENT_ID = t4.STUDENT_ID And t4.rn = 4 Left Join cte t5 On t1.STUDENT_ID = t5.STUDENT_ID And t5.rn = 5 Where t1.rn = 1) Query1 On STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID = Query1.STUDENT_ID Where COURSE_SECTIONS1.SEC_SUBJECT <> COURSE_SECTIONS.SEC_SUBJECT And (COURSE_SECTIONS.SEC_TERM = Upper(@Term) And COURSE_SECTIONS.SEC_XLIST Is Not Null And STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM = Upper(@Term) And STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_COURSE_SECTION_ID Is Not Null And (STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'Add' Or STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'New'))
Can you post the query plan, and the schema (particularly `VIEW` and `INDEX` definitions)?
I see a couple of possibilities here. I will try to explain, but still include a complete solution on the bottom of this answer, hopefully, it will help. First, there are more columns included in the CTE than actually needed unless the statement in the original question does not include everything. Assuming that this is not the case, the CTE has to part with some of the columns which are not needed. Also, because the data in the CTE, which looks like it can possibly have up to 5 rows per student, is subjected to join with itself 5 times, you can try a different technique instead, namely restate the CTE such that it already returns the academic program and department related columns 5 times per row at once. Because this is not possible to do with the more elegant PIVOT (which is limited to only pivot one column, but you need at least 2) the plain old case statement to manually pivot your columns will do the trick. The complete script is below. If you want to see how it works, please execute the contents of the CTE itself to see how it pivots the data to prepare it for the future joins, thus relieving you from the necessity to join the CTE with itself 5 times. I hope that I was able to preserve the original intention of the query. Please let me know if this is not so:
;with cte as
(
select
STUDENT_ID,
max(case when rn = 1 then STP_ACADEMIC_PROGRAM else null end) ACAD_PROGRAM_1,
max(case when rn = 2 then STP_ACADEMIC_PROGRAM else null end) ACAD_PROGRAM_2,
max(case when rn = 3 then STP_ACADEMIC_PROGRAM else null end) ACAD_PROGRAM_3,
max(case when rn = 1 then STP_DEPT else null end) STP_DEPT_1,
max(case when rn = 2 then STP_DEPT else null end) STP_DEPT_2,
max(case when rn = 3 then STP_DEPT else null end) STP_DEPT_3,
max(case when rn = 4 then STP_DEPT else null end) STP_DEPT_4,
max(case when rn = 5 then STP_DEPT else null end) STP_DEPT_5
from (
Select
pv.STUDENT_ID, pv.STP_ACADEMIC_PROGRAM, pv.STP_DEPT,
Row_Number() Over (Partition By pv.STUDENT_ID
Order By pv.STP_ACAD_LEVEL, pv.STP_DEGREE,
pv.STP_DEPT, pv.STP_ACADEMIC_PROGRAM) rn
From TERMS t Right Join STUDENT_ACAD_PROGRAMS_VIEW
On pv.STP_START_DATE = t.TERM_END_DATE)
Where t.TERMS_ID = Upper(@Term) And pv.STP_CURRENT_STATUS 'Potential'
And pv.STP_CCDS1 Is Null
) t
group by STUDENT_ID
)
Select
COURSE_SECTIONS1.SEC_SUBJECT As [Primary Subject],
COURSE_SECTIONS.SEC_SHORT_TITLE,
COURSE_SECTIONS.SEC_SUBJECT,
Case
When SubQuery.STUDENT_CURRENT_TYPE Is Null Then 'Unknown'
When COURSE_SECTIONS.SEC_SUBJECT in(cte.STP_DEPT_1, cte.STP_DEPT_2, cte.STP_DEPT_3,
cte.STP_DEPT_4, cte.STP_DEPT_5) and SubQuery.STUDENT_CURRENT_TYPE = 'LLM' Then 'Other LLM'
When COURSE_SECTIONS.SEC_SUBJECT in(cte.STP_DEPT_1, cte.STP_DEPT_2, cte.STP_DEPT_3,
cte.STP_DEPT_4, cte.STP_DEPT_5) and SubQuery.STUDENT_CURRENT_TYPE = 'MS' Then 'Other MS'
When SubQuery.STUDENT_CURRENT_TYPE in ('JD', 'JDLLM') Then SubQuery.STUDENT_CURRENT_TYPE
Else 'Other'
End As [Student Category],
STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID,
COURSE_SECTIONS.SEC_NAME,
STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_LAST_NAME,
STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_FIRST_NAME,
STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_MIDDLE_NAME,
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CREDITS,
cte.ACAD_PROGRAM_1,
cte.ACAD_PROGRAM_2,
cte.ACAD_PROGRAM_3,
COURSE_SECTIONS1.SEC_NAME As [Primary Name]
From
COURSE_SECTIONS Inner Join COURSE_SEC_XLISTS
On COURSE_SECTIONS.SEC_XLIST = COURSE_SEC_XLISTS.COURSE_SEC_XLISTS_ID
Left Join COURSE_SECTIONS COURSE_SECTIONS1
On COURSE_SEC_XLISTS.CSXL_PRIMARY_SECTION = COURSE_SECTIONS1.COURSE_SECTIONS_ID
Left Join STUDENT_ENROLLMENT_DETAIL_VIEW
On STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_COURSE_SECTION_ID = COURSE_SECTIONS.COURSE_SECTIONS_ID
Left Join
(
Select
R1.STUDENTS_ID,
R1.STU_TYPES As STUDENT_CURRENT_TYPE
From STU_TYPE_INFO As R1 With(NoLock)
Where
R1.POS = (
Select
Min(R2.POS) As MINPOS
From STU_TYPE_INFO As R2 With(NoLock) Left Join TERMS
On R2.STU_TYPE_DATES < TERMS.TERM_START_DATE
Where
R1.STUDENTS_ID = R2.STUDENTS_ID And TERMS.TERMS_ID = Upper(@Term)
Group By R2.STUDENTS_ID, TERMS.TERMS_ID)
) As SubQuery
On STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID = SubQuery.STUDENTS_ID
Left Join cte
On STUDENT_ENROLLMENT_DETAIL_VIEW.STUDENT_ID = cte.STUDENT_ID
Where
COURSE_SECTIONS1.SEC_SUBJECT COURSE_SECTIONS.SEC_SUBJECT And
(COURSE_SECTIONS.SEC_TERM = Upper(@Term) And
COURSE_SECTIONS.SEC_XLIST Is Not Null And
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_TERM = Upper(@Term) And
STUDENT_ENROLLMENT_DETAIL_VIEW.SECTION_COURSE_SECTION_ID Is Not Null And
(STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'Add' Or
STUDENT_ENROLLMENT_DETAIL_VIEW.ENROLL_CURRENT_STATUS = 'New'))
16 People are following this question.
