I am trying to run the following query using SSIS Visual Studio, but it seems to be using all of the virtual memory and taking over an hour to run. Can someone please check to see if there is an issue with the query?
SELECT DISTINCT STUDENTS.DCID, S_SC_STU_X.ADVISOR AS ADVISOR,
S_SC_STU_X.STUDENT_BIRTHCNTRY AS BIRTHCNTRY,
S_SC_STU_X.STUDENT_BIRTHPLACE AS BPLACE,
S_SC_STU_X.DROPOUT_DATE AS DROPDATE,
S_SC_STU_X.DROPOUT_REASON AS DROPREASON,
S_SC_STU_X.ENGL_PROF AS ENGPROF,
S_SC_STU_X.FIRST_LANG_SPOKEN AS FIRSTLNG,
S_SC_STU_X.HOMELANG AS HOMELNG,
S_SC_STU_X.INSTRSETTING AS INSTRSET,
S_SC_STU_X.MEDICAIDID AS MEDICAIDID,
S_SC_STU_X.MIGRANT AS MIGRANT,
S_SC_STU_X.NOSHOW_REASON AS NSREASON,
S_SC_STU_X.TRUEGRADE AS TRUEGRADE,
S_SC_STU_X.USSCHENTRYDATE AS USSCHENTDT,
S_SC_STU_X.MCKINNEY_VENTO_SERV AS HOMELESS_MCKINNEY_VENTO_SERVE,
S_SC_STU_X.NIGHT_RESIDENCE AS HOMELESS_NIGHT_RES,
S_SC_STU_X.UNACCOMP_YOUTH AS HOMELESS_UNACCOMP_YOUTH,
S_SC_STU_X.RETAINREASONCODE AS RETAIN,
S_SC_STU_X.SELFCONTAINED AS SELFDATE,
PS_customfields.getcf('students',students.id,'SC_AltSchProgInd') AS AltSchPrgm,
PS_customfields.getcf('students',students.id,'CCSD_street_apt') as APT,
STUDENTS.DOB AS BIRTHDATE,
STUDENTS.MAILING_CITY AS CITY,
to_char(storedgrades.CREDATMPT) as CREDATMPT,
to_char(storedgrades.CREDEARNED) as CREDEARNED,
STUDENTS.DISTRICTOFRESIDENCE AS DISTRICTRES,
S_SC_STU_EC_X.EC_HeadStartInd AS ECHEADSTART,
psm_email.email AS EMAIL,
SUBSTR(to_char(PS.STUDENTS.ENROLLMENT_SCHOOLID, '000'),2,3) as ENROLLMENT_SCHOOLID,
STUDENTS.ENTRYCODE AS ENTERCODE,
STUDENTS.ENTRYDATE AS ENTERDATE,
STUDENTS.ETHNICITY AS ETHNICCODE,
STUDENTS.FEDETHNICITY AS FEDETHNICITY,
STUDENTS.FIRST_NAME AS FIRSTNAME,
STUDENTS.LUNCHSTATUS AS FREEMEALS,
STUDENTS.GENDER AS GENDER,
STUDENTS.GRADE_LEVEL AS GRADE,
STUDENTS.HOME_ROOM AS HOMEROOM,
STUDENTS.LAST_NAME AS LASTNAME,
STUDENTS.EXITCODE AS LEAVECODE,
STUDENTS.EXITDATE AS LEAVEDATE,
STUDENTS.MAILING_STREET AS MAILADDR,
STUDENTS.MIDDLE_NAME AS MIDDLENAME,
NICKNAME,
OTHERNAME,
STUDENTS.SCHED_NEXTYEARGRADE AS NEXTGRADE,
STUDENTS.NEXT_SCHOOL AS NEXTSCHOOL,
STUDENTCOREFIELDS.PREVSTUDENTID AS PREVIOUSID,
STUDENTCOREFIELDS.PRIMARYLANGUAGE AS PRIMARYLNG,
SUBSTR(to_char(STUDENTRACE.race),1,40) AS RACE,
STUDENTS.STREET AS RESADDR,
STUDENTS.CITY AS RESCITY,
STUDENTS.STATE AS RESSTATE,
STUDENTS.ZIP AS RESZIPCODE,
STUDENTS.SCHED_SCHEDULED AS SCHED_SCHEDULED,
case when students.schoolid ='999999' then '999' else SUBSTR(to_char(PS.STUDENTS.SCHOOLID, '000'),2,3) end AS SCHOOLID,
S_SC_STU_X.SCHOOLRES AS SCHOOLRES,
case when IncludeInReporting is null then 'YES'
when IncludeInReporting in ('1') then 'NO'
else IncludeInReporting end AS SCINCLUDE,
STUDENTS.MAILING_STATE AS STATE,
STUDENTS.STATE_STUDENTNUMBER AS STATEID,
STUDENTS.ENROLL_STATUS AS STATUS,
PS_customfields.getcf('students',students.id,'CCSD_Street') as street,
PS_customfields.getcf('students',students.id,'CCSD_Street_num') as street_num,
SUBSTR(to_char(STUDENTS.STUDENT_NUMBER),1,25) AS STUDENT_NUMBER,
STUDENTS.ID AS STUDENTID,
STUDENTS.HOME_PHONE AS TELEPHONE,
STUDENTS.MAILING_ZIP AS ZIPCODE,
PS_customfields.getcf('students',students.id,'CCSD_media_rel_denied') as CCSD_media_rel_denied
,Oral_Commun_Lang
,Written_Commun_Lang
,ParentsMilitaryStatus
,CompHealthEdCode
,PECompCode
,Foster_Home
,Title1AsstTypeCode
,Title1ReadInd
,Title1MathInd
,County
,GridCode
,GEOCODE
,MAILING_GEOCODE
,CCSD_TUITION_REQUIRED
,PS_customfields.getcf('students',students.id,'CCSD_Stu_Owns_CC_Prop') as CCSD_Stu_Owns_CC_Prop
,PS_customfields.getcf('students',students.id,'CCSD_Out_of_County_Stu') as CCSD_Out_of_County_Stu,
schoolentrydate,
schoolentrygradelevel,
districtentrydate,
enrollment_transfer_date_pend,
PS_customfields.getcf('students',students.id,'CCSD_restrict_info_from_mil_recs') as restrict_info,
CCSD_RESTRICT_INFO_FROM_COL_RE, CCSD_STU_CELL_PHONE,CCSD_Proof_of_Res_Due_Date,
studentcorefields.singleparenthshldflag
FROM PS.STUDENTS LEFT OUTER JOIN
S_SC_STU_X ON S_SC_STU_X.STUDENTSDCID=STUDENTS.DCID LEFT OUTER JOIN
S_SC_STU_EC_X ON S_SC_STU_EC_X.STUDENTSDCID=STUDENTS.DCID LEFT OUTER JOIN
S_SC_STU_CONTACTS_X ON S_SC_STU_CONTACTS_X.STUDENTSDCID=STUDENTS.DCID LEFT OUTER JOIN
U_DEF_EXT_STUDENTS ON U_DEF_EXT_STUDENTS.STUDENTSDCID=STUDENTS.DCID LEFT OUTER JOIN
STUDENTCOREFIELDS ON STUDENTCOREFIELDS.STUDENTSDCID=STUDENTS.DCID
LEFT OUTER JOIN
PS.SCHOOLS ON PS.SCHOOLS.SCHOOL_NUMBER = PS.STUDENTS.SCHOOLID LEFT OUTER JOIN
(SELECT STUDENTID, SUM(POTENTIALCRHRS) AS CREDATMPT, SUM(EARNEDCRHRS) AS CREDEARNED
FROM PS.STOREDGRADES
GROUP BY STUDENTID) storedgrades ON storedgrades.STUDENTID = PS.STUDENTS.ID LEFT OUTER JOIN
(SELECT MAX(LUNCH_ID) AS LUNCH_ID, LASTFIRST
FROM PS.TEACHERS
GROUP BY LASTFIRST) teachers ON PS.STUDENTS.HOME_ROOM = teachers.LASTFIRST LEFT OUTER JOIN
(SELECT STUDENTID, LISTAGG(RACECD, ',') WITHIN GROUP (ORDER BY RACECD) AS race
FROM PS.STUDENTRACE STUDENTRACE_1
GROUP BY STUDENTID) STUDENTRACE ON STUDENTRACE.STUDENTID = PS.STUDENTS.ID
left outer join
(SELECT
DISTINCT
STUDENTS.ID,
PSM_STUDENTCONTACT.EMAIL STUDENT_EMAIL
FROM
PSM_STUDENTCONTACT
INNER JOIN PSM_STUDENT ON PSM_STUDENTCONTACT.STUDENTID = PSM_STUDENT.ID
INNER JOIN STUDENTS ON PSM_STUDENT.STUDENTIDENTIFIER = STUDENTS.STUDENT_NUMBER
INNER JOIN PSM_STUDENTCONTACTTYPE ON PSM_STUDENTCONTACT.STUDENTCONTACTTYPEID = PSM_STUDENTCONTACTTYPE.ID
WHERE
PSM_STUDENTCONTACT.EMAIL IS NOT NULL
AND PSM_STUDENTCONTACTTYPE.NAME = 'Self'
) email on email.id=ps.students.id
left outer join
(
select
psm_student.studentidentifier studentidentifier,
psm_studentcontact.email email
from
psm_student
join psm_studentcontact on psm_studentcontact.studentid = psm_student.id
join psm_studentcontacttype on psm_studentcontacttype.id = psm_studentcontact.studentcontacttypeid
where
psm_studentcontacttype.name = 'Self'
) psm_email on students.student_number = psm_email.studentidentifier
where cast(entrydate as date)>'02-JUN-18'
