Posted by rcamarda on 12/01/06 17:37

I have created a table that contains buckets to hold activitives of
enrollment for each of our admissions officer for each day of an
enrollment session. I have an UPDATE that builds rolling totals and
updates said table. I count unique students from a table that contains
registration information.
Everything works, however it runs pretty slowly and I am wondering if
there is a better approach to writing the SQL. SQL's Engine Tuning
advisor has 0% improvment.
I'd appreciate any suggestions as to better ways to accomplish my goal.
Or, compliments on my genius if there isnt a better way! *grin*

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

with 50K rows, this takes about 5 minutes. However I have almost
9,000,000 rows of history. (15 years of 4 quarter sessions). If this
build is linear, it would take 1.25 hours.

My relationships and dependencies are housed in my ETL tool.
// Connection: Target

CREATE TABLE
"dbo"."F_BI_Admissions_Officer_Summary_By_Session_RT_Current"
(
"AO_SUMMARY_KEY" VARCHAR(43) NULL,
"ADMREP_ID" VARCHAR(15) NULL,
"ADMREP_SKEY" BIGINT NULL,
"ADMREP_CAPTION" VARCHAR(20) NULL,
"ADMREP_CAMPUS_ID" VARCHAR(10) NULL,
"ADMREP_CAMPUS_CAPTION" VARCHAR(30) NULL,
"ADMREP_REGION_CAPTION" VARCHAR(20) NULL,
"ADMREP_TEAM_CAPTION" VARCHAR(57) NULL,
"ADMREP_EXPIRED_FLAG" VARCHAR(5) NULL,
"SESSION_ID" CHAR(6) NULL,
"SESSION_DAY" INTEGER NULL,
"SESSION_DT" DATETIME NULL,
"LEAD_COUNT" INTEGER NULL,
"INTERVIEW_COUNT" INTEGER NULL,
"APPLICATION_COUNT" INTEGER NULL,
"LY_SESSION_ID" VARCHAR(10) NULL,
"LY_LEAD_COUNT" SMALLINT NULL,
"LY_INTERVIEW_COUNT" SMALLINT NULL,
"LY_APPLICATION_COUNT" SMALLINT NULL,
"LY_ALL_REGISTRATION_COUNT" SMALLINT NULL,
"NEW_READMIT_REGISTRATION_COUNT" SMALLINT NULL,
"ALL_REGISTRATION_COUNT" INTEGER NOT NULL,
"NEW_REGISTRATION_COUNT" INTEGER NOT NULL,
"READMIT_REGISTRATION_COUNT" INTEGER NOT NULL,
"CONTINUING_REGISTRATION_COUNT" INTEGER NOT NULL,
"CANCELLED_REGISTRATION_COUNT" INTEGER NOT NULL,
"LY_NEW_REGISTRATION_COUNT" SMALLINT NULL,
"LY_READMIT_REGISTRATION_COUNT" SMALLINT NULL,
"LY_CONTINUING_REGISTRATION_COUNT" SMALLINT NULL,
"LY_CANCELLED_REGISTRATION_COUNT" SMALLINT NULL,
"TALISMA_CALLS_IN" SMALLINT NULL,
"TALISMA_CALLS_OUT" SMALLINT NULL,
"AVAYA_CALLS_IN" SMALLINT NULL,
"AVAYA_CALLS_OUT" SMALLINT NULL,
"LEAD_FORECAST" SMALLINT NULL,
"INTERVIEW_FORECAST" SMALLINT NULL,
"APPLICATION_FORECAST" SMALLINT NULL,
"NEW_REGISTRATION_FORECAST" SMALLINT NULL,
"READMIT_REGISTRATION_FORECAST" SMALLINT NULL,
"CONTINUING_REGISTRATION_FORECAST" SMALLINT NULL
)
;

// Connection: Target

CREATE TABLE "dbo"."F_BI_Registration_Tracking"
(
"UNIQUE_KEY" VARCHAR(87) NULL,
"REGISTRATION_KEY" VARCHAR(40) NULL,
"REGTRACK_ID" VARCHAR(47) NULL,
"CLASSES_OFFERED_ID" VARCHAR(23) NULL,
"STUDENT_ID" CHAR(20) NULL,
"SESSION_ID" CHAR(6) NULL,
"FULL_CLASS_ID" VARCHAR(15) NULL,
"CAMPUS_ID" VARCHAR(10) NULL,
"ACTIVITY_DT" DATETIME NULL,
"ACTIVITY_CODE" VARCHAR(1) NULL,
"LOAD_DT" DATETIME NULL,
"SOURCE" VARCHAR(4) NULL,
"ACTIVITY_COUNT" SMALLINT NULL,
"CLASS_DROP_DT" DATETIME NULL,
"CLASS_DROP_COUNT" SMALLINT NULL,
"CLASS_ADD_DT" DATETIME NULL,
"CLASS_ADD_COUNT" SMALLINT NULL,
"BEFORE_D0_CLASS_COUNT" SMALLINT NULL,
"DAY0_CLASS_COUNT" SMALLINT NULL,
"AFTER_D0_CLASS_COUNT" SMALLINT NULL,
"ALL_CLASS_COUNT" SMALLINT NULL,
"BEFORE_D0_ONLINE_CLASS_COUNT" SMALLINT NULL,
"DAY0_ONLINE_CLASS_COUNT" SMALLINT NULL,
"AFTER_D0_ONLINE_CLASS_COUNT" SMALLINT NULL,
"ALL_ONLINE_CLASS_COUNT" SMALLINT NULL,
"VM_POS" BIGINT NULL,
"SOURCE_FILE" SMALLINT NULL,
"BANDED_ID" BIGINT NULL,
"CLASSES_OFFERED_SKEY" BIGINT NULL,
"STUDENT_SKEY" BIGINT NULL,
"SESSION_SKEY" BIGINT NULL,
"CLASS_CAMPUS_SKEY" BIGINT NULL,
"COMPUTED_DT" DATETIME NULL,
"SESSION_DAY" BIGINT NULL,
"Count_Down" BIGINT NULL,
"AFTER_DAY0_FIRST_REG_FLAG" SMALLINT NULL
)
;

[Back to original message]