Query optimization needed

Question

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 for duplicate queries then the performance goes increased stead of reducing. Thanks for your help in advance. 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.

First of all, it's highly unlikely anybody will choose to spend their free time on trying to understand this vast screed of poorly formatted code (we are all volunteers here). Secondly, tuning queries is a matter of many different factors, which requires all sorts of details missing from your question. Please read [this excellent post](https://stackoverflow.com/a/34975420/146325) which explains how to provide the information we need, and may even help you learn how to optimize the query yourself. — APC, Sep 12 '19 at 22:21
How about posting an execution plan? You will need to resolve full table scans on large tables and other expensive operations. Reviewing execution plan is much easier for us than evaluation this lengthy query. — alexherm, Sep 12 '19 at 23:16
On quick review, you have alot of subselects selecting 1 field based on a date range. You can load these values into variables before running the select - this will likely make the query much faster, but there may be more work to do beyond just that. — alexherm, Sep 12 '19 at 23:19

score 0 · Accepted Answer · answered Sep 13 '19 at 01:15

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.

score -1 · Answer 2 · answered Sep 16 '19 at 12:02

-1

Use the following:

select * into #mytemp
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');

select ...[all select cases of original query]...
from #mytemp
drop table #mytemp;

i believe this will resolve performance.

answered Sep 16 '19 at 12:02

Stefanos Zilellis

571
3
8

This answer looks like SQL Server syntax, but the question is about Oracle. – Jon Heller Sep 17 '19 at 21:05
It is SQL Server syntax but i am sure there would be equivalent Oracle syntax. Still it will solve performance problem. – Stefanos Zilellis Sep 18 '19 at 06:34

Query optimization needed

2 Answers2