SQL 튜닝에 대해서 도움을 받고자 올립니다.

데이터가 백만건이라서 조회하는데 7분정도 걸립니다. 좀 더 줄이고싶은데

어디부분을 수정하고 어떻게 개선해야할까요?

난이도 : 상

SELECT A.STUD_NO
				 , A.DEPART_CODE			 
				 , A.MAJOR_CODE
				 , A.GRADE			 
				 , B.GPA_AVG_PREEDIT
				 , B.GPA_TOT_PREEDIT
				 , B.SCORE_AVG
				 , A.BIRTH
				 , COUNT(*) OVER (PARTITION BY A.YEAR,A.TERM,A.GRADE,A.DEPART_CODE,A.MAJOR_CODE) AS EMP_COUNT
			  FROM (
			  
					SELECT 
						 AA.STUD_NO
						,AA.YEAR
						,AA.TERM
						,AA.GRADE
						,CASE
							WHEN BB.DEPART_CODE IS NULL THEN (SELECT DEPART_CODE FROM T_SU_LECT_REQUEST WHERE YEAR=AA.YEAR AND TERM=AA.TERM AND STUD_NO=AA.STUD_NO GROUP BY DEPART_CODE)
							ELSE BB.DEPART_CODE
						END AS DEPART_CODE
						,CASE
							WHEN BB.MAJOR_CODE IS NULL THEN (SELECT MAJOR_CODE FROM T_SU_LECT_REQUEST WHERE YEAR=AA.YEAR AND TERM=AA.TERM AND STUD_NO=AA.STUD_NO GROUP BY MAJOR_CODE)
							ELSE BB.MAJOR_CODE
						END AS MAJOR_CODE
						,AA.BIRTH
						,AA.REQ_CREDIT_TOT
						,(SELECT IIF(COUNT(*) > 0,0,1) FROM T_SJ_SCORE_TERM WHERE YEAR=AA.YEAR AND TERM=AA.TERM AND STUD_NO=AA.STUD_NO) AS TERM_CNT 
					FROM (
						SELECT
							 AAA.STUD_NO
							,AAA.YEAR
							,AAA.TERM
							,AAA.GRADE
							,BBB.BIRTH
							,SUM(AAA.CREDIT) AS REQ_CREDIT_TOT
						FROM T_SU_LECT_REQUEST AAA
						INNER JOIN (
							SELECT STUD_NO, DBO.SF_GET_SERIAL_NUMBER_TO_BIRTH(SERIAL_NUMBER,'') AS BIRTH FROM T_HJ_STUD_MASTER
						) BBB
						ON AAA.STUD_NO = BBB.STUD_NO

						WHERE AAA.YEAR = 2016
						AND AAA.TERM = 2
						AND ISNULL(AAA.CREDIT_PAY_YN,'N') != 'Y'
						GROUP BY  AAA.STUD_NO,AAA.YEAR,AAA.TERM,AAA.GRADE,BBB.BIRTH 
					) AA
					LEFT OUTER JOIN (
						SELECT 
							 AAA.STUD_NO
						    ,AAA.NEXT_DEPART_CODE AS DEPART_CODE
							,AAA.NEXT_MAJOR_CODE AS MAJOR_CODE
						FROM T_HJ_CHANGE AAA
						LEFT OUTER JOIN (
							SELECT STUD_NO, MAX(OLD_DATA_SEQ) AS MAX_SEQ FROM T_HJ_CHANGE WHERE YEAR = 2016 AND TERM = 2 GROUP BY STUD_NO
						) BBB
						ON AAA.STUD_NO = BBB.STUD_NO
						WHERE AAA.OLD_DATA_SEQ = MAX_SEQ
					) BB
					ON AA.STUD_NO = BB.STUD_NO

			    ) A

				LEFT OUTER JOIN(
					SELECT
						YEAR,
						TERM, 
						STUD_NO,
						RECEIVE_CODE,
						CONVERT(DECIMAL(5,2),ROUND(SUM(GET_SCORE * CREDIT) / SUM(CREDIT),2)) AS SCORE_AVG,
						SUM(GPA) AS GPA_TOT_PREEDIT,
						CONVERT(DECIMAL(3,2),ROUND(SUM(GPA * CREDIT) / SUM(CREDIT),2)) AS GPA_AVG_PREEDIT
					FROM (
						SELECT
							YEAR,
							TERM,
							STUD_NO,
							CREDIT,
							COMP_DIV,
							EVALU_METHOD,
							RECEIVE_CODE,
							CASE
								WHEN NULLIF(REPLACE_LECT_CODE,'') IS NULL THEN GET_SCORE
								ELSE RE_GET_SCORE
							END AS GET_SCORE,
							CASE
								WHEN NULLIF(REPLACE_LECT_CODE,'') IS NULL THEN GPA
								ELSE RE_GPA
							END AS GPA
						FROM T_SJ_SCORE_CNTI
					) A
					WHERE COMP_DIV NOT IN ('8','10')
					AND ISNULL(EVALU_METHOD,'') != '03'
					AND RECEIVE_CODE != '02'
					GROUP BY YEAR, TERM, STUD_NO, RECEIVE_CODE
				) B 
				ON A.STUD_NO = B.STUD_NO
				AND A.YEAR = B.YEAR
				AND A.TERM = B.TERM

				LEFT OUTER JOIN (
					SELECT 
						A.YEAR,
						A.TERM, 
						A.STUD_NO,
						SUM(CREDIT) AS GET_CREDIT_TOT
					FROM T_SJ_SCORE_CNTI A
					WHERE A.CHG_SCORE_GRADEMARK_PREEDIT NOT IN ('F','NP')
					GROUP BY A.YEAR, A.TERM, A.STUD_NO
				) C
				ON A.STUD_NO = C.STUD_NO
				AND A.YEAR = C.YEAR
				AND A.TERM = C.TERM

			 WHERE A.DEPART_CODE	= (CASE WHEN ISNULL('','') = '' THEN A.DEPART_CODE ELSE '' END)
			   AND A.MAJOR_CODE	= (CASE WHEN ISNULL('','') = '' THEN A.MAJOR_CODE ELSE '' END)
			 ORDER BY A.MAJOR_CODE, A.GRADE DESC , A.TERM_CNT, B.GPA_AVG_PREEDIT DESC, B.SCORE_AVG DESC, C.GET_CREDIT_TOT DESC, A.REQ_CREDIT_TOT DESC, A.BIRTH DESC