Apologies for the long SQL statement. I removed the SELECT and GROUP BY portions and am only showing the FROM section (hopefully that will be enough to go on). When we run the query on only one college 'SC' and Student Population 'T' for example, it takes about two minutes and comes close to the numbers we are expecting. If we comment out Population 'T' in order to include all Population, it goes from 2 mins to 30 mins. Any thoughts about how we might speed up the query and more importantly, why when we include more than one college the results are lower overall. In the example for SC when run COLLEGE='SC' only, the total is 135, when run using COLLEGE in ('LA','SC','BN','NS','ED'), the total for SC shows 75. If you need more of the SQL statement, please let me know. We are doing multiple Left Joins as we need to get different status and dates and be able to show one record if it exists per person out of the multiple status and dates. sxschech.cutoff is a table that contains a "Cutoff Date", the date we want to restrict on.
Code:
from sxschech.cutoff, mst_person p,
mst_Admissions_Application a
LEFT JOIN
(SELECT *
FROM (SELECT decision.*,
ROW_NUMBER () OVER (PARTITION BY person_uid ORDER BY decision_date DESC, decision_number DESC)
dt
FROM mst_admissions_decision decision, sxschech.CUTOFF
WHERE decision_date IS NOT NULL AND decision_date <= cutoff_dt
and academic_period in ('200830','200840')
)
WHERE dt = 1
) ad
ON a.APPLICATION_NUMBER = ad.APPLICATION_NUMBER
AND a.ACADEMIC_PERIOD = ad.ACADEMIC_PERIOD
AND a.Academic_period in ('200830','200840')
AND a.PERSON_UID = ad.PERSON_UID
AND a.Curriculum_Priority = 1
LEFT OUTER JOIN
(select entity_uid, state_province,
row_number() over (partition by entity_uid order by address_type, preferred_address_ind desc, address_seq_no desc) as rnum
from mst_address
where address_type in ('MA', 'PR')
and address_status_ind is null -- active and current
and (address_end_date is null or address_end_date > sysdate) ) d
ON a.person_uid = d.entity_uid
and d.rnum = 1
LEFT OUTER JOIN mst_admissions_attribute aa
ON a.person_uid = aa.person_uid
and a.academic_period = aa.academic_period
and a.application_number = aa.application_number
and aa.admissions_attribute in ('FG', 'NFG', 'DFG')
LEFT OUTER JOIN deposit_jl de -- overroad mtt_ar_deposit, now includes deposits, prepaids and released B.lott 6/26
ON a.person_uid = de.account_uid
and a.academic_period = de.academic_period
and de.detail_code <> 'TDEP'
and de.academic_period > '200800'
LEFT OUTER JOIN academic_study e
ON a.person_uid = e.person_uid
and a.academic_period = e.academic_period
AND a.student_level = e.student_level
LEFT OUTER JOIN mst_admissions_requirement r -- waived deposits
ON a.person_uid = r.person_uid
and a.academic_period = r.academic_period
and a.application_number = r.application_number
and r.requirement = 'TDEP'
and r.academic_period > '200800'
and r.requirement_deadline_date IS NULL
LEFT OUTER JOIN
(SELECT *
FROM (SELECT person_uid,academic_period, application_number, decision_date as acc_date,
ROW_NUMBER () OVER (PARTITION BY person_uid ORDER BY decision_date, decision_number)
dt
FROM mst_admissions_decision, sxschech.CUTOFF
WHERE decision_date IS NOT NULL AND decision_date <= cutoff_dt
and academic_period in ('200830','200840')
and DECISION IN ('AE', 'AC', 'CC', 'CD', 'CP', 'CX', 'DC', 'HN', 'N6', 'N7', 'N8',
'PB', 'PH', 'PT', 'QA', 'TL', 'TO', 'US', 'WA')
)
WHERE dt = 1
) acc
ON a.APPLICATION_NUMBER = acc.APPLICATION_NUMBER
AND a.ACADEMIC_PERIOD = acc.ACADEMIC_PERIOD
AND a.Academic_period in ('200830','200840')
AND a.PERSON_UID = acc.PERSON_UID
AND a.Curriculum_Priority = 1
WHERE p.PERSON_UID = a.PERSON_UID
AND a.application_date <= cutoff_dt
AND a.Academic_period in ('200830','200840')
AND SUBSTR(a.PROGRAM,1,2) <> 'ND'
AND a.PROGRAM IS NOT NULL
AND a.STUDENT_LEVEL = 'UG'
AND a.STUDENT_POPULATION <> 'S'
AND a.STUDENT_POPULATION = 'T'
-- AND college in ('LA', 'SC', 'BN', 'NS', 'ED')
AND a.college = 'SC'