Need to optimize my sql query to enhance the performance. below is the query
SELECT
(
CASE
WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
FROM PREFS
WHERE PREFS.SCHOOLID = EV.SCHOOLID
AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
AND PREFS.YEARID = EV.YEARID
AND PREFS.DCID =
(SELECT MIN(DCID)
FROM PREFS PDUPS
WHERE PDUPS.SCHOOLID = EV.SCHOOLID
AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
AND PDUPS.YEARID = EV.YEARID
) ) = '1'
THEN
(SELECT COUNT(PER.PERIOD_NUMBER)
FROM PERIOD PER,
BELL_SCHEDULE_ITEMS BSI,
CYCLE_DAY CY,
CALENDAR_DAY CDSUB,
CC CC,
SECTION_MEETING SM,
SECTIONS SEC
WHERE CC.DATEENROLLED <= CD.DATE_VALUE
AND CD.DATE_VALUE < CC.DATELEFT
AND ABS( CC.SECTIONID ) = SM.SECTIONID
AND SM.SECTIONID = SEC.ID
AND SEC.EXCLUDE_ADA = 0
AND SM.PERIOD_NUMBER = PER.PERIOD_NUMBER
AND SM.SCHOOLID = PER.SCHOOLID
AND SM.YEAR_ID = PER.YEAR_ID
AND BSI.PERIOD_ID = PER.ID
AND BSI.BELL_SCHEDULE_ID = CDSUB.BELL_SCHEDULE_ID
AND ( (CDSUB.SCHOOLID = EV.SCHOOLID
AND BSI.ADA_CODE = 1)
OR (CDSUB.SCHOOLID <> EV.SCHOOLID) )
AND SM.CYCLE_DAY_LETTER = CY.LETTER
AND SM.SCHOOLID = CY.SCHOOLID
AND SM.YEAR_ID = CY.YEAR_ID
AND SM.YEAR_ID = EV.YEARID
AND CY.ID = CDSUB.CYCLE_DAY_ID
AND CDSUB.DATE_VALUE = CD.DATE_VALUE
AND CDSUB.INSESSION = 1
AND CC.STUDENTID = EV.STUDENTID
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
)
ELSE
(SELECT COUNT(P.PERIOD_NUMBER)
FROM CC CC,
SECTION_MEETING SM,
CYCLE_DAY CY,
PERIOD P,
BELL_SCHEDULE_ITEMS BSI,
SECTIONS SEC
WHERE CC.STUDENTID = EV.STUDENTID
AND CC.DATEENROLLED <= CD.DATE_VALUE
AND CC.DATELEFT > CD.DATE_VALUE
AND CY.ID = CD.CYCLE_DAY_ID
AND SM.SECTIONID = ABS(CC.SECTIONID)
AND SM.CYCLE_DAY_LETTER = CY.LETTER
AND SM.PERIOD_NUMBER = P.PERIOD_NUMBER
AND SM.SCHOOLID = P.SCHOOLID
AND SM.YEAR_ID = P.YEAR_ID
AND SM.YEAR_ID = EV.YEARID
AND SM.SECTIONID = SEC.ID
AND SEC.EXCLUDE_ADA = 0
AND BSI.BELL_SCHEDULE_ID = CD.BELL_SCHEDULE_ID
AND BSI.ADA_CODE = 1
AND BSI.PERIOD_ID = P.ID
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
)
END) AS POTENTIAL_PERIODS_PRESENT,
(
CASE
WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
FROM PREFS
WHERE PREFS.SCHOOLID = EV.SCHOOLID
AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
AND PREFS.YEARID = EV.YEARID
AND PREFS.DCID =
(SELECT MIN(DCID)
FROM PREFS PDUPS
WHERE PDUPS.SCHOOLID = EV.SCHOOLID
AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
AND PDUPS.YEARID = EV.YEARID
) ) = '1'
THEN NVL(
(SELECT SUM(BSI.MINUTES_ATTENDED)
FROM PERIOD PER,
BELL_SCHEDULE_ITEMS BSI,
CYCLE_DAY CY,
CALENDAR_DAY CDSUB,
CC CC,
SECTION_MEETING SM,
SECTIONS SEC
WHERE CC.DATEENROLLED <= CD.DATE_VALUE
AND CD.DATE_VALUE < CC.DATELEFT
AND ABS( CC.SECTIONID ) = SM.SECTIONID
AND SM.SECTIONID = SEC.ID
AND SEC.EXCLUDE_ADA = 0
AND SM.PERIOD_NUMBER = PER.PERIOD_NUMBER
AND SM.SCHOOLID = PER.SCHOOLID
AND SM.YEAR_ID = PER.YEAR_ID
AND BSI.PERIOD_ID = PER.ID
AND BSI.BELL_SCHEDULE_ID = CDSUB.BELL_SCHEDULE_ID
AND ( (CDSUB.SCHOOLID = EV.SCHOOLID
AND BSI.ADA_CODE = 1)
OR (CDSUB.SCHOOLID <> EV.SCHOOLID) )
AND SM.CYCLE_DAY_LETTER = CY.LETTER
AND SM.SCHOOLID = CY.SCHOOLID
AND SM.YEAR_ID = CY.YEAR_ID
AND SM.YEAR_ID = EV.YEARID
AND CY.ID = CDSUB.CYCLE_DAY_ID
AND CDSUB.DATE_VALUE = CD.DATE_VALUE
AND CDSUB.INSESSION = 1
AND CC.STUDENTID = EV.STUDENTID
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
) ,0)
ELSE NVL(
(SELECT SUM(BSI.MINUTES_ATTENDED)
FROM CC CC,
SECTION_MEETING SM,
CYCLE_DAY CY,
PERIOD P,
BELL_SCHEDULE_ITEMS BSI,
SECTIONS SEC
WHERE CC.STUDENTID = EV.STUDENTID
AND CC.DATEENROLLED <= CD.DATE_VALUE
AND CC.DATELEFT > CD.DATE_VALUE
AND CY.ID = CD.CYCLE_DAY_ID
AND SM.SECTIONID = ABS(CC.SECTIONID)
AND SM.CYCLE_DAY_LETTER = CY.LETTER
AND SM.PERIOD_NUMBER = P.PERIOD_NUMBER
AND SM.SCHOOLID = P.SCHOOLID
AND SM.YEAR_ID = P.YEAR_ID
AND SM.YEAR_ID = EV.YEARID
AND SM.SECTIONID = SEC.ID
AND SEC.EXCLUDE_ADA = 0
AND BSI.BELL_SCHEDULE_ID = CD.BELL_SCHEDULE_ID
AND BSI.ADA_CODE = 1
AND BSI.PERIOD_ID = P.ID
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
) ,0)
END) AS POTENTIAL_MINUTES_PRESENT,
(
CASE
WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
FROM PREFS
WHERE PREFS.SCHOOLID = EV.SCHOOLID
AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
AND PREFS.YEARID = EV.YEARID
AND PREFS.DCID =
(SELECT MIN(DCID)
FROM PREFS PDUPS
WHERE PDUPS.SCHOOLID = EV.SCHOOLID
AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
AND PDUPS.YEARID = EV.YEARID
) ) = '1'
THEN
(SELECT COUNT(*)
FROM ATTENDANCE ATT,
ATTENDANCE_CODE AC,
CC CC,
SECTIONS SEC,
BELL_SCHEDULE_ITEMS BSI,
CALENDAR_DAY CDSUB
WHERE ATT.STUDENTID = CC.STUDENTID
AND CDSUB.DATE_VALUE = CD.DATE_VALUE
AND CDSUB.BELL_SCHEDULE_ID = BSI.BELL_SCHEDULE_ID
AND BSI.PERIOD_ID = ATT.PERIODID
AND ( (CDSUB.SCHOOLID = EV.SCHOOLID
AND BSI.ADA_CODE = 1)
OR (CDSUB.SCHOOLID <> EV.SCHOOLID) )
AND ATT.CCID = CC.ID
AND ATT.ATTENDANCE_CODEID = AC.ID
AND CC.DATEENROLLED <= ATT.ATT_DATE
AND ATT.ATT_DATE < CC.DATELEFT
AND ATT.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND AC.PRESENCE_STATUS_CD = 'Absent'
AND AC.CALCULATE_ADA_YN = 1
AND ABS(CC.SECTIONID) = SEC.ID
AND SEC.EXCLUDE_ADA = 0
AND ATT.STUDENTID = EV.STUDENTID
AND ATT.ATT_DATE = CD.DATE_VALUE
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
)
ELSE
(SELECT COUNT(*)
FROM ATTENDANCE ATT,
ATTENDANCE_CODE AC,
CC CC,
SECTIONS SEC,
BELL_SCHEDULE_ITEMS BSI
WHERE ATT.STUDENTID = CC.STUDENTID
AND CD.BELL_SCHEDULE_ID = BSI.BELL_SCHEDULE_ID
AND BSI.PERIOD_ID = ATT.PERIODID
AND BSI.ADA_CODE = 1
AND ATT.CCID = CC.ID
AND ATT.ATTENDANCE_CODEID = AC.ID
AND ATT.ATT_DATE >= CC.DATEENROLLED
AND ATT.ATT_DATE < CC.DATELEFT
AND ATT.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND AC.PRESENCE_STATUS_CD = 'Absent'
AND AC.CALCULATE_ADA_YN = 1
AND ABS(CC.SECTIONID) = SEC.ID
AND SEC.EXCLUDE_ADA = 0
AND ATT.STUDENTID = EV.STUDENTID
AND ATT.ATT_DATE = CD.DATE_VALUE
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
)
END) AS PERIODS_ABSENT,
(
CASE
WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
FROM PREFS
WHERE PREFS.SCHOOLID = EV.SCHOOLID
AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
AND PREFS.YEARID = EV.YEARID
AND PREFS.DCID =
(SELECT MIN(DCID)
FROM PREFS PDUPS
WHERE PDUPS.SCHOOLID = EV.SCHOOLID
AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
AND PDUPS.YEARID = EV.YEARID
) ) = '1'
THEN NVL(
(SELECT SUM(
CASE
WHEN NVL(ATTT.ENTRYCOUNT, 0) = 0
THEN NVL( (
CASE
WHEN AC.PRESENCE_STATUS_CD = 'Absent'
AND AC.CALCULATE_ADA_YN = 1
THEN BSI.MINUTES_ATTENDED
WHEN AC.PRESENCE_STATUS_CD = 'Absent'
AND AC.CALCULATE_ADA_YN = 0
THEN 0
ELSE BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES
END), 0)
ELSE NVL(BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES, 0)
END)
FROM ATTENDANCE ATT
JOIN ATTENDANCE_CODE AC
ON ATT.ATTENDANCE_CODEID = AC.ID
JOIN CC CC
ON ATT.CCID = CC.ID
JOIN SECTIONS SEC
ON ABS(CC.SECTIONID) = SEC.ID
JOIN BELL_SCHEDULE_ITEMS BSI
ON ATT.PERIODID = BSI.PERIOD_ID
JOIN CALENDAR_DAY CDSUB
ON BSI.BELL_SCHEDULE_ID = CDSUB.BELL_SCHEDULE_ID
LEFT JOIN
(SELECT ATTT2.ATTENDANCEID,
COUNT(ATTT2.ATTENDANCEID) ENTRYCOUNT
FROM ATTENDANCE_TIME ATTT2
GROUP BY ATTT2.ATTENDANCEID
) ATTT
ON ATT.ID = ATTT.ATTENDANCEID
WHERE ATT.STUDENTID = CC.STUDENTID
AND CDSUB.DATE_VALUE = CD.DATE_VALUE
AND ( (CDSUB.SCHOOLID = EV.SCHOOLID
AND BSI.ADA_CODE = 1)
OR (CDSUB.SCHOOLID <> EV.SCHOOLID) )
AND CC.DATEENROLLED <= ATT.ATT_DATE
AND ATT.ATT_DATE < CC.DATELEFT
AND ATT.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND SEC.EXCLUDE_ADA = 0
AND ATT.STUDENTID = EV.STUDENTID
AND ATT.ATT_DATE = CD.DATE_VALUE
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
) ,0)
ELSE NVL(
(SELECT SUM(
CASE
WHEN NVL(ATTT.ENTRYCOUNT, 0) = 0
THEN NVL( (
CASE
WHEN AC.PRESENCE_STATUS_CD = 'Absent'
AND AC.CALCULATE_ADA_YN = 1
THEN BSI.MINUTES_ATTENDED
WHEN AC.PRESENCE_STATUS_CD = 'Absent'
AND AC.CALCULATE_ADA_YN = 0
THEN 0
ELSE BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES
END), 0)
ELSE NVL(BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES, 0)
END)
FROM ATTENDANCE ATT
JOIN ATTENDANCE_CODE AC
ON ATT.ATTENDANCE_CODEID = AC.ID
JOIN CC CC
ON ATT.CCID = CC.ID
JOIN BELL_SCHEDULE_ITEMS BSI
ON BSI.PERIOD_ID = ATT.PERIODID
JOIN SECTIONS SEC
ON ABS(CC.SECTIONID) = SEC.ID
LEFT JOIN
(SELECT ATTT2.ATTENDANCEID,
COUNT(ATTT2.ATTENDANCEID) ENTRYCOUNT
FROM ATTENDANCE_TIME ATTT2
GROUP BY ATTT2.ATTENDANCEID
) ATTT
ON ATT.ID = ATTT.ATTENDANCEID
WHERE ATT.STUDENTID = CC.STUDENTID
AND CD.BELL_SCHEDULE_ID = BSI.BELL_SCHEDULE_ID
AND ATT.ATT_DATE >= CC.DATEENROLLED
AND ATT.ATT_DATE < CC.DATELEFT
AND ATT.ATT_MODE_CODE = 'ATT_ModeMeeting'
AND SEC.EXCLUDE_ADA = 0
AND BSI.ADA_CODE = 1
AND ATT.STUDENTID = EV.STUDENTID
AND ATT.ATT_DATE = CD.DATE_VALUE
AND CC.DCID NOT IN
(SELECT CCE.CCDCID
FROM CC_EXCLUSIONS CCE
WHERE CD.DATE_VALUE>=CCE.STARTDATE
AND CD.DATE_VALUE <=CCE.ENDDATE
)
) ,0)
END) AS MINUTES_ABSENT
FROM PT_ENROLLMENT_ALL EV,
CALENDAR_DAY CD,
BELL_SCHEDULE BS
WHERE EV.SCHOOLID = CD.SCHOOLID
AND EV.SCHOOLID = 2053
AND CD.BELL_SCHEDULE_ID = BS.ID
AND CD.INSESSION = 1
AND CD.DATE_VALUE >= EV.ENTRYDATE
AND CD.DATE_VALUE < EV.EXITDATE
AND CD.DATE_VALUE >= to_date('07/28/2018', 'mm/dd/yyyy')
AND CD.DATE_VALUE <= to_date('06/30/2019', 'mm/dd/yyyy');
When I tried to use With clause(CTE Common Table Expression) for duplicate queries then the performance goes increased bit. But I believe there is still a scope of performance improvement. So for these listed columns you can see there are multiple case statements are there and few chunks of codes are repeated as well. Is there any way that can be optimized with except clause. Thanks for your help in advance.
This SQL Script is from Oracle.
Simplify the query and the performance might automatically improve.
For example, the subqueries in POTENTIAL_PERIODS_PRESENT and POTENTIAL_MINUTES_PRESENT are almost identical. They read from the same tables, and use the same joins, they just use a different aggregate function. Do all the aggregate functions at the same time.
Convert:
SELECT COUNT(PER.PERIOD_NUMBER)
...
SELECT SUM(BSI.MINUTES_ATTENDED)
...
To something like:
JOIN
(
SELECT STUDENTID, COUNT(PER.PERIOD_NUMBER), SELECT SUM(BSI.MINUTES_ATTENDED)
...
GROUP BY STUDENTID
) STUDENT_DATA
ON EV.STUDENID = STUDENT_DATA.STUDENTID
Perhaps the other subqueries can be converted, but they're not trivially identical like the first two. Your queries are pretty huge, and the exact details are something you'll have to painfully work through.