question

muk avatar image
muk 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'))
sql-server-2008t-sqlsql-server-2008-r2query-tuning
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post the query plan, and the schema (particularly `VIEW` and `INDEX` definitions)?
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
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'))
Hope this help. Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg thank you so much! This is much faster!!!!!
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.