Benefit
Time Differential
Reporting Code Rewrite
Gil
Standen, EHIT Kronos Oracle DBA
December
8, 2004
A poorly performing piece of reporting code was
identified by EHIT team and a preliminary report was prepared on November 24,
2004 recommending revised code. This author has completed the code rewrite
and the new, improved Benefit Time Differential report is presented.
The code that was
identified performs poorly and was previously identified and discussed. It is presented again in Appendix C for
reference.
Revised Code The new code is a stored PL/SQL procedure named TKCSOWNER.BTD which is shown in Appendix A. Three temporary tables are also required for the new reporting system (see Appendix B for the sql code for the temp tables). The report stores output in a temp table called QRS that is owned by TKCSOWNER. The report data is queried from the QRS table. Report Installation The new Benefit Time Differential report is installed as follows:
Step |
Description |
1 |
Run the script btd_temp_tables.sql as TKCSOWNER user.You can ignore“ORA-00942: table or view does not exist” errors if observed.You must get 3 “Table created” messages. |
2 |
Run the script btd_rpt_v2_pl.sql as TKCSOWNWER user.You must get the “Procedure Created.” Message. |
Report Execution
Step |
Description |
1 |
Run the Benefit Time Differential report as TKCSOWNER.“exec BTD” |
2 |
Extract the report data from the QRS table.“select * from QRS;” |
Report Performance Data[1]
Code Description |
Elapsed Runtime |
Original Correlated Subquery Report[2] |
31 hours 14 minutes 52 seconds |
Stored Procedure TKCSOWNER.BTD Report |
0 hours 0 minutes 12 seconds |
Appendix A – TKCSOWNER.BTD PL/SQL REPORT GENERATOR CREATE OR REPLACE PROCEDURE TKCSOWNER.BTD IS CURSOR c3 IS select distinct personnum from temp_table_1; CURSOR c1 IS select distinct t.personnum, t.applydate, t.wfctimeinseconds, round(t.wfctimeinseconds/3600,2) as amount, substr(c.customdata,1,1) as shift, t.paycodename from vp_alltotals t, vp_personcustdata c where c.personnum = t.personnum and c.customdatadefid = 4 and substr(c.customdata,1,1) in ('E','N') and t.applydate >= t.prevpayperiodstart and t.applydate <= t.currpayperiodend and t.paycodename in (select name from SF_QUAL_PAYCODES) and t.wfctimeinseconds <> 0 and substr(t.wfclaborlevelname4,1,2) in (select * from SF_QUAL_JOBS) order by t.personnum,t.applydate;EMP_REC c1%ROWTYPE;EMP_REC3 c3%ROWTYPE;QRS_REC QRS%ROWTYPE;BEGIN delete from QRS; delete from temp_table_1; delete from temp_table_2; commit; Open c1; LOOP FETCH c1 INTO EMP_REC; EXIT WHEN c1%NOTFOUND; insert into temp_table_1 values (emp_rec.personnum, emp_rec.applydate,emp_rec.wfctimeinseconds, emp_rec.amount, emp_rec.shift, emp_rec.paycodename); insert into temp_table_2 values (emp_rec.personnum, emp_rec.applydate, emp_rec.wfctimeinseconds, emp_rec.amount, emp_rec.shift, emp_rec.paycodename); commit; END LOOP; close c1; open c3; LOOP FETCH c3 into EMP_REC3; EXIT WHEN c3%NOTFOUND; FOR QRS_REC IN (select distinct personnum, applydate, amount, shift, (select count(*) from temp_table_2 a, temp_table_1 t where a.personnum = t.personnum and a.applydate = t.applydate and a.wfctimeinseconds = t.wfctimeinseconds and a.paycodename = 'Shift Differential' and a.personnum = emp_rec3.personnum) as NumShiftDiff, (select count(*) from temp_table_2 a, temp_table_1 t where a.personnum = t.personnum and a.applydate = t.applydate and a.wfctimeinseconds = t.wfctimeinseconds and a.paycodename in (select name from sf_qual_paycodes) and a.personnum = emp_rec3.personnum) as NumPaycodes from temp_table_1 where personnum=emp_rec3.personnum) LOOP insert into QRS values (QRS_REC.PERSONNUM, QRS_REC.APPLYDATE, QRS_REC.AMOUNT, QRS_REC.SHIFT,QRS_REC.NUMSHIFTDIFF, QRS_REC.NUMPAYCODES); COMMIT; END LOOP; END LOOP; close c3;END;/Appendix B – SCRIPT TO CREATE THE TEMP TABLES FOR THE BTD REPORT set echo onset feedback on DROP TABLE TKCSOWNER.TEMP_TABLE_1;CREATE TABLE "TKCSOWNER"."TEMP_TABLE_1" ("PERSONNUM" VARCHAR2(15 byte), "APPLYDATE" DATE, "WFCTIMEINSECONDS" NUMBER, "AMOUNT" NUMBER, "SHIFT" VARCHAR2(1 byte), "PAYCODENAME" VARCHAR2(50 byte)) TABLESPACE "TKCS3" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) LOGGING; DROP TABLE TKCSOWNER.TEMP_TABLE_2;CREATE TABLE "TKCSOWNER"."TEMP_TABLE_2" ("PERSONNUM" VARCHAR2(15 byte), "APPLYDATE" DATE, "WFCTIMEINSECONDS" NUMBER, "AMOUNT" NUMBER, "SHIFT" VARCHAR2(1 byte), "PAYCODENAME" VARCHAR2(50 byte)) TABLESPACE "TKCS3" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) LOGGING; DROP TABLE TKCSOWNER.QRS;CREATE TABLE "TKCSOWNER"."QRS" ("PERSONNUM" VARCHAR2(15 byte), "APPLYDATE" DATE, "AMOUNT" NUMBER, "SHIFT" VARCHAR2(1 byte), "NUMSHIFTDIFF" NUMBER, "NUMPAYCODES" NUMBER) TABLESPACE "TKCS3" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) LOGGING;Appendix C – ORIGINAL POORLY PERFORMING CODE
The main problem with this code is the use of
correlated subqueries (two of them, shown in red and blue text) which are run
against the complex views vp_alltotals and vp_personcustdata which leads to many
iterations of full table scans against large joined datasets and poor query
performance.
select distinct t.personnum, t.applydate, round(t.wfctimeinseconds/3600,2) as amount, substr(c.customdata,1,1) as shift, (select count(*) from vp_alltotals where personnum = t.personnum and applydate = t.applydate and wfctimeinseconds = t.wfctimeinseconds and paycodename = 'Shift Differential') as NumShiftDiff, (select count(*) from vp_alltotals where personnum = t.personnum and applydate = t.applydate and wfctimeinseconds = t.wfctimeinseconds and paycodename in (select name from SF_QUAL_PAYCODES)) as NumPaycodesfrom vp_alltotals t,vp_personcustdata c where c.personnum = t.personnum and c.customdatadefid = 4 and substr(c.customdata,1,1) in ('E','N') and t.applydate >= t.prevpayperiodstart and t.applydate <= t.currpayperiodend and t.paycodename in (select name from SF_QUAL_PAYCODES) and t.wfctimeinseconds <> 0 and substr(t.wfclaborlevelname4,1,2) in (select * from SF_QUAL_JOBS) order by t.personnum,t.applydate; Appendix E – VP_ALLTOTALS VIEW This is one of the views used in the base query of the Benefit Time Differential report. It is a vendor-delivered view which is part of the Kronos application. CREATE OR REPLACE VIEW "TKCSOWNER"."VP_ALLTOTALS" ("PERSONFULLNAME", "PERSONNUM", "WFCLABORLEVELNAME1", "WFCLABORLEVELNAME2", "WFCLABORLEVELNAME3", "WFCLABORLEVELNAME4", "WFCLABORLEVELNAME5", "WFCLABORLEVELNAME6", "WFCLABORLEVELNAME7", "PAYCODENAME", "PAYCODETYPE", "ISMONEYAMOUNTSW", "APPLYDATE", "WFCTIMEINSECONDS", "WFCWAGEAMOUNT", "WFCMONEYAMOUNT", "PROJTIMEINSECONDS", "PROJWAGEAMOUNT", "PROJMONEYAMOUNT", "SCHEDTIMEINSECONDS", "SCHEDWAGEAMOUNT", "SCHEDMONEYAMOUNT", "HOMEACCOUNTSW", "NOTPAIDSW", "CURRPAYPERIODSTART", "CURRPAYPERIODEND", "PREVPAYPERIODSTART", "PREVPAYPERIODEND", "NEXTPAYPERIODSTART", "NEXTPAYPERIODEND", "VPALLTOTALSTYPE", "EMPLOYEEID", "PAYCODEID", "CHILDPAYCODEID", "LABORACCTID", "PERSONID", "WFCLABORLEVELDSC1", "WFCLABORLEVELDSC2", "WFCLABORLEVELDSC3", "WFCLABORLEVELDSC4", "WFCLABORLEVELDSC5", "WFCLABORLEVELDSC6", "WFCLABORLEVELDSC7", "PCVISIBLETOUSERSW", "ADDTOTCETOTSW") AS SELECT /*+ORDERED */ VE.FULLNM AS PERSONFULLNAME, VE.PERSONNUM, LA1.LABORLEV1NM AS WFCLABORLEVELNAME1, LA1.LABORLEV2NM AS WFCLABORLEVELNAME2, LA1.LABORLEV3NM AS WFCLABORLEVELNAME3, LA1.LABORLEV4NM AS WFCLABORLEVELNAME4, LA1.LABORLEV5NM AS WFCLABORLEVELNAME5, LA1.LABORLEV6NM AS WFCLABORLEVELNAME6, LA1.LABORLEV7NM AS WFCLABORLEVELNAME7, PC.NAME AS PAYCODENAME , PC.TYPE AS PAYCODETYPE, PC.IS_MONEYCAT AS ISMONEYAMOUNTSW, T.APPLYDTM AS APPLYDATE, T.DURATIONSECSQTY AS WFCTIMEINSECONDS, T.WAGEAMT AS WFCWAGEAMOUNT, T.MONEYAMT AS WFCMONEYAMOUNT, TO_NUMBER (0) AS PROJTIMEINSECONDS, TO_NUMBER (0) AS PROJWAGEAMOUNT, TO_NUMBER (0) AS PROJMONEYAMOUNT, TO_NUMBER (0) AS SCHEDTIMEINSECONDS, TO_NUMBER (0) AS SCHEDWAGEAMOUNT, TO_NUMBER (0) AS SCHEDMONEYAMOUNT, T.HOMEACCOUNTSW, T.NOTPAIDSW, CUR.START_DATE AS CURRPAYPERIODSTART, CUR.END_DATE AS CURRPAYPERIODEND, PREV.START_DATE AS PREVPAYPERIODSTART, PREV.END_DATE AS PREVPAYPERIODEND, NXT.START_DATE AS NEXTPAYPERIODSTART, NXT.END_DATE AS NEXTPAYPERIODEND, 'COMBINED PAYCODE WFC TOTALS' AS VPALLTOTALSTYPE, T.EMPLOYEEID, M.GRANDPAYCODEID AS PAYCODEID, M.PAYCODEID AS CHILDPAYCODEID, T.LABORACCTID, VE.PERSONID, LA1.LABORLEV1DSC AS WFCLABORLEVELDSC1, LA1.LABORLEV2DSC AS WFCLABORLEVELDSC2, LA1.LABORLEV3DSC AS WFCLABORLEVELDSC3, LA1.LABORLEV4DSC AS WFCLABORLEVELDSC4, LA1.LABORLEV5DSC AS WFCLABORLEVELDSC5, LA1.LABORLEV6DSC AS WFCLABORLEVELDSC6, LA1.LABORLEV7DSC AS WFCLABORLEVELDSC7, PC.VISIBLE_TO_USER AS PCVISIBLETOUSERSW, PC.EDIT_AFF_SHF_TOTAL AS ADDTOTCETOTSW FROM WTKEMPLOYEE WE, WFCTOTAL T, PAYCODE1MMFLAT M, PERSON VE, PAYCODE PC, LABORACCT LA1, PAYPERIOD CUR, PAYPERIOD PREV, PAYPERIOD NXT WHERE M.GRANDPAYCODEID = PC.PAYCODEID AND M.PAYCODEID = T.PAYCODEID AND M.EFFECTIVEDTM <= T.APPLYDTM AND M.EXPIRATIONDTM > T.APPLYDTM AND WE.EMPLOYEEID = T.EMPLOYEEID AND VE.PERSONID = WE.PERSONID AND LA1.LABORACCTID = T.LABORACCTID AND CUR.PAYRULEID = WE.PAYRULEID AND CUR.START_DT <= SYSDATE AND CUR.END_DT > SYSDATE AND PREV.PAYRULEID = WE.PAYRULEID AND CUR.START_DT = PREV.END_DT AND NXT.PAYRULEID = WE.PAYRULEID AND CUR.END_DT = NXT.START_DT UNION ALL SELECT /*+ORDERED */ VE.FULLNM AS PERSONFULLNAME, VE.PERSONNUM, LA1.LABORLEV1NM AS WFCLABORLEVELNAME1, LA1.LABORLEV2NM AS WFCLABORLEVELNAME2, LA1.LABORLEV3NM AS WFCLABORLEVELNAME3, LA1.LABORLEV4NM AS WFCLABORLEVELNAME4, LA1.LABORLEV5NM AS WFCLABORLEVELNAME5, LA1.LABORLEV6NM AS WFCLABORLEVELNAME6, LA1.LABORLEV7NM AS WFCLABORLEVELNAME7, PC.NAME AS PAYCODENAME, PC.TYPE AS PAYCODETYPE, PC.IS_MONEYCAT AS ISMONEYAMOUNTSW, T.APPLYDATE AS APPLYDATE, TO_NUMBER (0) AS WFCTIMEINSECONDS, TO_NUMBER (0) AS WFCWAGEAMOUNT, TO_NUMBER (0) AS WFCMONEYAMOUNT, TO_NUMBER (0) AS PROJTIMEINSECONDS, TO_NUMBER (0) AS PROJWAGEAMOUNT, TO_NUMBER (0) AS PROJMONEYAMOUNT, T.AMOUNT AS SCHEDTIMEINSECONDS, T.WAGEAMOUNT AS SCHEDWAGEAMOUNT, T.MONEYAMOUNT AS SCHEDMONEYAMOUNT, T.HOMEACCOUNT_SW AS HOMEACCOUNTSW, TO_NUMBER (NULL) AS NOTPAIDSW, CUR.START_DATE AS CURRPAYPERIODSTART, CUR.END_DATE AS CURRPAYPERIODEND, PREV.START_DATE AS PREVPAYPERIODSTART, PREV.END_DATE AS PREVPAYPERIODEND, NXT.START_DATE AS NEXTPAYPERIODSTART, NXT.END_DATE AS NEXTPAYPERIODEND, 'COMBINED PAYCODE SCHEDULED TOTALS' AS VPALLTOTALSTYPE, T.EMPLOYEEID, M.GRANDPAYCODEID AS PAYCODEID, M.PAYCODEID AS CHILDPAYCODEID, T.LABORACCTID, VE.PERSONID, LA1.LABORLEV1DSC AS WFCLABORLEVELDSC1, LA1.LABORLEV2DSC AS WFCLABORLEVELDSC2, LA1.LABORLEV3DSC AS WFCLABORLEVELDSC3, LA1.LABORLEV4DSC AS WFCLABORLEVELDSC4, LA1.LABORLEV5DSC AS WFCLABORLEVELDSC5, LA1.LABORLEV6DSC AS WFCLABORLEVELDSC6, LA1.LABORLEV7DSC AS WFCLABORLEVELDSC7, PC.VISIBLE_TO_USER AS PCVISIBLETOUSERSW, PC.EDIT_AFF_SHF_TOTAL AS ASSTOTCETOTSW FROM WTKEMPLOYEE WE, SCHEDULEDTOTAL T, PAYCODE1MMFLAT M, PERSON VE, PAYCODE PC, LABORACCT LA1, PAYPERIOD CUR, PAYPERIOD PREV, PAYPERIOD NXT WHERE M.GRANDPAYCODEID = PC.PAYCODEID AND M.PAYCODEID = T.PAYCATID AND M.EFFECTIVEDTM <= T.APPLYDATE AND M.EXPIRATIONDTM > T.APPLYDATE AND WE.EMPLOYEEID = T.EMPLOYEEID AND VE.PERSONID = WE.PERSONID AND LA1.LABORACCTID = T.LABORACCTID AND CUR.PAYRULEID = WE.PAYRULEID AND CUR.START_DT <= SYSDATE AND CUR.END_DT > SYSDATE AND PREV.PAYRULEID = WE.PAYRULEID AND CUR.START_DT = PREV.END_DT AND NXT.PAYRULEID = WE.PAYRULEID AND CUR.END_DT = NXT.START_DT UNION ALL SELECT /*+ORDERED */ VE.FULLNM AS PERSONFULLNAME, VE.PERSONNUM, LA1.LABORLEV1NM AS WFCLABORLEVELNAME1, LA1.LABORLEV2NM AS WFCLABORLEVELNAME2, LA1.LABORLEV3NM AS WFCLABORLEVELNAME3, LA1.LABORLEV4NM AS WFCLABORLEVELNAME4, LA1.LABORLEV5NM AS WFCLABORLEVELNAME5, LA1.LABORLEV6NM AS WFCLABORLEVELNAME6, LA1.LABORLEV7NM AS WFCLABORLEVELNAME7, PC.NAME AS PAYCODENAME, PC.TYPE AS PAYCODETYPE, PC.IS_MONEYCAT AS ISMONEYAMOUNTSW, T.APPLYDATE AS APPLYDATE, TO_NUMBER (0) AS WFCTIMEINSECONDS, TO_NUMBER (0) AS WFCWAGEAMOUNT, TO_NUMBER (0) AS WFCMONEYAMOUNT, T.AMOUNT AS PROJTIMEINSECONDS, T.WAGEAMOUNT AS PROJWAGEAMOUNT, T.MONEYAMOUNT AS PROJMONEYAMOUNT, TO_NUMBER (0) AS SCHEDTIMEINSECONDS, TO_NUMBER (0) AS SCHEDWAGEAMOUNT, TO_NUMBER (0) AS SCHEDMONEYAMOUNT, T.HOMEACCOUNT_SW AS HOMEACCOUNTSW, TO_NUMBER (NULL) AS NOTPAIDSW, CUR.START_DATE AS CURRPAYPERIODSTART, CUR.END_DATE AS CURRPAYPERIODEND, PREV.START_DATE AS PREVPAYPERIODSTART, PREV.END_DATE AS PREVPAYPERIODEND, NXT.START_DATE AS NEXTPAYPERIODSTART, NXT.END_DATE AS NEXTPAYPERIODEND, 'COMBINED PAYCODE PROJECTED TOTALS' AS VPALLTOTALSTYPE, T.EMPLOYEEID, M.GRANDPAYCODEID AS PAYCODEID, M.PAYCODEID AS CHILDPAYCODEID, T.LABORACCTID, VE.PERSONID, LA1.LABORLEV1DSC AS WFCLABORLEVELDSC1, LA1.LABORLEV2DSC AS WFCLABORLEVELDSC2, LA1.LABORLEV3DSC AS WFCLABORLEVELDSC3, LA1.LABORLEV4DSC AS WFCLABORLEVELDSC4, LA1.LABORLEV5DSC AS WFCLABORLEVELDSC5, LA1.LABORLEV6DSC AS WFCLABORLEVELDSC6, LA1.LABORLEV7DSC AS WFCLABORLEVELDSC7, PC.VISIBLE_TO_USER AS PCVISIBLETOUSERSW, PC.EDIT_AFF_SHF_TOTAL AS ASSTOTCETOTSW FROM WTKEMPLOYEE WE, PROJECTEDTOTAL T, PAYCODE1MMFLAT M, PERSON VE, PAYCODE PC, LABORACCT LA1, PAYPERIOD CUR, PAYPERIOD PREV, PAYPERIOD NXT WHERE M.GRANDPAYCODEID = PC.PAYCODEID AND M.PAYCODEID = T.PAYCATID AND M.EFFECTIVEDTM <= T.APPLYDATE AND M.EXPIRATIONDTM > T.APPLYDATE AND WE.EMPLOYEEID = T.EMPLOYEEID AND VE.PERSONID = WE.PERSONID AND LA1.LABORACCTID = T.LABORACCTID AND CUR.PAYRULEID = WE.PAYRULEID AND CUR.START_DT <= SYSDATE AND CUR.END_DT > SYSDATE AND PREV.PAYRULEID = WE.PAYRULEID AND CUR.START_DT = PREV.END_DT AND NXT.PAYRULEID = WE.PAYRULEID AND CUR.END_DT = NXT.START_DT; Appendix F – VP_PERSONCUSTDATA VIEW CREATE OR REPLACE VIEW "TKCSOWNER"."VP_PERSONCUSTDATA" ("PERSONNUM", "PERSONFULLNAME", "CUSTOMDEFNAME", "CUSTOMDEFDSC", "CUSTOMDATA", "CUSTOMDATETIME", "PERSONID", "CUSTOMDATADEFID", "CUSTOMDATETYPEID") AS SELECT PE.PERSONNUM, PE.FULLNM AS PERSONFULLNAME, CD.SHORTNM AS CUSTOMDEFNAME, CD.CUSTOMDATADEFDSC AS CUSTOMDEFDSC, PA.PERSONCSTMDATATXT AS CUSTOMDATA, TO_DATE (NULL) AS CUSTOMDATETIME, PE.PERSONID, PA.CUSTOMDATADEFID, TO_NUMBER (NULL) AS CUSTOMDATETYPEID FROM PERSON PE, CUSTOMDATADEF CD, PERSONCSTMDATA PA WHERE PE.PERSONID = PA.PERSONID AND CD.CUSTOMDATADEFID = PA.CUSTOMDATADEFID AND CD.CSTMDATADEFTYPEID = 1 AND CD.DELETEDSW = 0 UNION SELECT PE.PERSONNUM, PE.FULLNM AS PERSONFULLNAME, CT.SHORTNM AS CUSTOMDEFNAME, TO_CHAR (NULL) AS CUSTOMDEFDSC, TO_CHAR (NULL) AS CUSTOMDATA, PD.ACTUALCUSTOMDTM AS CUSTOMDATETIME, PE.PERSONID, TO_NUMBER (NULL) AS CUSTOMDATADEFID, PD.CUSTOMDATETYPEID FROM PERSON PE, CUSTOMDATETYPE CT, PRSNCSTMDATEMM PD WHERE PE.PERSONID = PD.PERSONID AND CT.CUSTOMDATETYPEID = PD.CUSTOMDATETYPEID AND CT.SITEWIDESCOPESW = 0 [1] Performance data collected on the ATCD database. The vp_alltotals view had over 600,000 rows at execution time for the test, and vp_personcustdata had about 100,000 rows.
[2] Extrapolated estimated time for completion on ATCD based on sample subset query of 18 personnum values. The original query runs too long for a reasonable test execution (does not return resultset).