Posted by Ed Murphy on 12/02/06 05:22

rcamarda wrote:

> UPDATE F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_RT_CURRENT -- (RT =
> rollingtotal)
> SET NEW_REGISTRATION_COUNT =
> (SELECT COUNT(DISTINCT STUDENT_ID)
> FROM
> (SELECT aa.session_id, aa.student_id, student_status
> FROM F_BI_REGISTRATION_TRACKING AA
> LEFT OUTER JOIN F_BI_STUDENT_SUMMARY_SESSION BB ON
> AA.STUDENT_ID=BB.STUDENT_ID AND AA.SESSION_ID=BB.SESSION_ID
> LEFT OUTER JOIN D_BI_STUDENT CC
> ON AA.STUDENT_ID = CC.STUDENT_ID AND CC.CURR_IND
> = 'Y'
> WHERE AA.SESSION_ID = OA.SESSION_ID
> AND ACTIVITY_DT <= OA.SESSION_DT
> AND CC.TALISMA_AO_ID = OA.ADMREP_ID
> AND STUDENT_STATUS = 'NEW'
> GROUP BY AA.SESSION_ID, aa.STUDENT_ID, STUDENT_STATUS
> HAVING SUM(ACTIVITY_COUNT) > 0
> ) as ppp
> )
> FROM F_BI_ADMISSIONS_OFFICER_SUMMARY_BY_SESSION_rt_current OA

Do you have indexes on all appropriate columns?

Which table contains STUDENT_STATUS?

Does the join to D_BI_STUDENT need to be left outer? What about the
join to F_BI_STUDENT_SUMMARY_SESSION? If so, can you eliminate the
need by adding dummy rows (with some sort of this-is-a-dummy-row
indicator) to those tables?

Can you replace this form (boiled down from the original):

SELECT COUNT(DISTINCT STUDENT_ID) FROM (
SELECT SESSION_ID, STUDENT_ID, STUDENT_STATUS
FROM ...
GROUP BY SESSION_ID, STUDENT_ID, STUDENT_STATUS
HAVING SUM(ACTIVITY_COUNT) > 0
)

with this?

SELECT COUNT(*) FROM (
SELECT STUDENT_ID
FROM ...
GROUP BY STUDENT_ID
HAVING SUM(ACTIVITY_COUNT) > 0
)

[Back to original message]