sql - 需要查询优化
问题描述
需要优化我的sql查询来提升性能。下面是查询
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');
当我尝试使用 With 子句进行重复查询时,性能会提高而不是降低。提前感谢您的帮助。因此,对于这些列出的列,您可以看到存在多个 case 语句,并且也重复了少量代码块。有什么方法可以用 except 子句进行优化。
解决方案
简化查询,性能可能会自动提高。
例如,POTENTIAL_PERIODS_PRESENT
和POTENTIAL_MINUTES_PRESENT
中的子查询几乎相同。它们从相同的表中读取,并使用相同的连接,它们只是使用不同的聚合函数。同时执行所有聚合函数。
兑换:
SELECT COUNT(PER.PERIOD_NUMBER)
...
SELECT SUM(BSI.MINUTES_ATTENDED)
...
类似于:
JOIN
(
SELECT STUDENTID, COUNT(PER.PERIOD_NUMBER), SELECT SUM(BSI.MINUTES_ATTENDED)
...
GROUP BY STUDENTID
) STUDENT_DATA
ON EV.STUDENID = STUDENT_DATA.STUDENTID
也许可以转换其他子查询,但它们并不像前两个一样简单。您的查询非常庞大,确切的细节是您必须痛苦地解决的问题。
推荐阅读
- java - 在没有测试方法覆盖的情况下为子类运行 JUnit 测试
- java - .jar 可执行文件,保持“小窗口”
- c# - 如何使用 NAudio 将声音文件加载到内存中并稍后使用?
- node.js - Google Node.js SDK unicode 上的操作
- jenkins - 将 Jenkins 内部版本号传递给 Protractor for SauceLabs
- python - 在 virtualenv 中运行 jupyter notebook(有权限问题)
- button - Vue使用按钮更改输入字段中的文本
- sql-server - SQL Server Management Studio 无法识别 .bak 文件
- r - 使用R中的stringi提取字符串中某些字符之后的多个子字符串
- objective-c - 在测试 iOS Swift 时访问只读属性