code efficiency
I'm trying to figure out how to make my load process more efficient than running it through cursors, can anyone offer any suggestions on how I should go about this? See coding below, it selects from two different tables ent_aa and x1040_individual to load data into x1040_return, but it goes through a process of checks and initialization first, now I want to update records that are already loaded and insert new ones but the processing time is ridiculous I need help! I want to change the way it is being process but not sure what route to take, should I use CASE Statements?
PROCEDURE x1040_return_load (p_year IN NUMBER) IS
CURSOR ent_aa_cursor IS
SELECT pssn_num
,pssn_val_cd
,sssn_num
,sssn_val_cd
,tx_yr
,tx_pd_num
,prim_name_ctrl_txt
,sec_name_ctrl_txt
,cyc_orig_extract_num
,CYC_UPDATED_NUM
,PRIMARY_STRATUM_CD
,WEIGHT
,WGT_5PCT_SRS_1040_NUM
,WGT_10PCT_SRS_SB_NUM
,flng_sts_cd
FROM ifm_bld.ent_aa_stg
WHERE tx_yr = p_year;
CURSOR x1040_individual_cur(ssn in varchar2, val_cd in varchar2, txyr in number, name_ctrl in varchar2) IS
SELECT INDIVIDUAL_ID
,ssn_num
,ssn_name_ctrl_txt
,act_ssn_num
FROM x1040_individual
WHERE act_ssn_num = ssn
AND ssn_val_cd = val_cd
AND tx_yr = txyr
AND ssn_name_ctrl_txt = decode (val_cd, 0, ssn_name_ctrl_txt,
1, ifm_utils.get_name_ctrl(val_cd,name_ctrl));
v_load_num NUMBER := 0;
v_row_count NUMBER := 0;
v_seq_num NUMBER := 0;
v_individual_id NUMBER := 0;
v_pssn_num VARCHAR2(9) := ' ';
v_sssn_num VARCHAR2(9) := '000000000';
v_act_sssn_num VARCHAR2(9) := ' ';
v_sssn_val_cd VARCHAR2(1) := '.';
v_sssn_name VARCHAR2(4) := ' ';
v_table_count NUMBER := 0;
v_update_count NUMBER := 0;
v_date_updated DATE := SYSDATE;
v_last_cycle_updated NUMBER(6) := '000000';
----------------------------------------------------------------------
BEGIN
----------------------------------------------------------------------
--Insert Table 1040_individual comment 'BEGIN' into Load Log Table
SELECT load_num_seq.nextval
INTO v_load_num
FROM dual;
INSERT INTO load_log
VALUES (v_date_updated, v_load_num, p_year || ' SET X1040 Return cgen',
'INITIATED',NULL,NULL,NULL,NULL);
COMMIT;
FOR ent_aa_rec in ent_aa_cursor LOOP
-- Get Primary SSN's indiv_id
v_individual_id := 0;
v_pssn_num := '000000000';
FOR indiv_rec in x1040_individual_cur(ent_aa_rec.pssn_num, ent_aa_rec.pssn_val_cd, ent_aa_rec.tx_yr, ent_aa_rec.PRIM_NAME_CTRL_TXT) LOOP
v_individual_id := indiv_rec.individual_id;
v_pssn_num := indiv_rec.ssn_num;
END LOOP;
IF ent_aa_rec.sssn_num = '.........' THEN
v_sssn_num := '000000000';
v_act_sssn_num := '.........';
v_sssn_val_cd := '1';
v_sssn_name := '....';
ELSIF ent_aa_rec.flng_sts_cd = '2' THEN
v_sssn_num := '000000000';
v_sssn_val_cd := '1';
v_sssn_name := nvl(ent_aa_rec.sec_name_ctrl_txt,' ');
v_act_sssn_num := ent_aa_rec.SSSN_NUM;
-- Get Secondary SSN's indiv_id
FOR indiv_rec in x1040_individual_cur(ent_aa_rec.sssn_num, ent_aa_rec.sssn_val_cd, ent_aa_rec.tx_yr, ent_aa_rec.sec_name_ctrl_txt) LOOP
v_sssn_num := indiv_rec.ssn_num;
v_act_sssn_num := ent_aa_rec.SSSN_NUM;
v_sssn_val_cd := ent_aa_rec.sssn_val_cd;
v_sssn_name := ifm_utils.get_name_ctrl(ent_aa_rec.sssn_val_cd, ent_aa_rec.sec_name_ctrl_txt);
END LOOP;
ELSE
v_sssn_num := ifm_utils.get_enc_ssn(ent_aa_rec.sssn_num);
v_act_sssn_num := ent_aa_rec.sssn_num;
v_sssn_val_cd := ent_aa_rec.sssn_val_cd;
IF ent_aa_rec.sssn_val_cd = 0 AND
ent_aa_rec.sec_name_ctrl_txt IS NULL THEN
v_sssn_name := ' ';
ELSE
v_sssn_name := ifm_utils.get_name_ctrl(ent_aa_rec.sssn_val_cd, ent_aa_rec.sec_name_ctrl_txt);
END IF;
END IF;
BEGIN
SELECT last_cycle_updated
INTO v_last_cycle_updated
FROM cycle_updated;
END;
BEGIN
INSERT INTO x1040_return
(individual_id, pssn_num, pssn_val_cd, tx_yr
,act_pssn_num, sssn_num,sssn_val_cd, ACT_SSSN_NUM, tx_prd_num
,cyc_orig_extract_num, CYC_UPDATED_NUM, PRIM_STRATUM_CD,wgt_num, WGT_5PCT_SRS_1040_NUM
,WGT_10PCT_SRS_SB_NUM, IFM_BOD_CD, BAL_DUE_AFTER_REMIT_AMT
,DATA_ASSMD_PRES_SCH_B_IND,sssn_name_ctrl_txt, date_created, flng_sts_cd)
VALUES (v_individual_id, v_pssn_num, ent_aa_rec.pssn_val_cd, ent_aa_rec.tx_yr
,ent_aa_rec.pssn_num, v_sssn_num, v_sssn_val_cd, v_act_sssn_num, ent_aa_rec.tx_pd_num
,ent_aa_rec.cyc_orig_extract_num, ent_aa_rec.CYC_UPDATED_NUM
,ent_aa_rec.PRIMARY_STRATUM_CD, ent_aa_rec.weight
,ent_aa_rec.WGT_5PCT_SRS_1040_NUM
,ent_aa_rec.WGT_10PCT_SRS_SB_NUM, 0, 0, 0, v_sssn_name, sysdate, ent_aa_rec.flng_sts_cd);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
IF ent_aa_rec.cyc_updated_num > v_last_cycle_updated
THEN
BEGIN
UPDATE x1040_return
SET cyc_orig_extract_num = ent_aa_rec.cyc_orig_extract_num
,CYC_UPDATED_NUM = ent_aa_rec.CYC_UPDATED_NUM
,PRIM_STRATUM_CD = ent_aa_rec.PRIMARY_STRATUM_CD
,wgt_num = ent_aa_rec.weight
,WGT_5PCT_SRS_1040_NUM = ent_aa_rec.WGT_5PCT_SRS_1040_NUM
,WGT_10PCT_SRS_SB_NUM = ent_aa_rec.WGT_10PCT_SRS_SB_NUM
,sssn_name_ctrl_txt = v_sssn_name
,date_updated = sysdate
,flng_sts_cd = ent_aa_rec.flng_sts_cd
WHERE act_pssn_num = ent_aa_rec.pssn_num
AND tx_yr = ent_aa_rec.tx_yr
AND pssn_val_cd = ent_aa_rec.pssn_val_cd;
EXCEPTION
WHEN OTHERS
THEN
UPDATE load_log SET status = 'FAILED'
,complete_time = SYSDATE
,error_stack = 'X1040 Return ERROR:'||','||ent_aa_rec.pssn_num||','||DBMS_UTILI TY.FORMAT_ERROR_STACK
WHERE load_num = v_load_num
AND load_step = p_year || 'X1040 Return Return Load - BEGIN TABLE';
END;
END IF;
End;
v_table_count := v_table_count + 1;
v_row_count := v_row_count + 1;
v_update_count := v_update_count + 1;
--COMMIT every 1,000 rows
--IF v_update_count = 1000 THEN
-- v_update_count := 0;
-- COMMIT;
--END IF;
END LOOP;
COMMIT;
--Insert into Load Log Table the comment the process was completed
UPDATE load_log SET status = 'COMPLETE'
,complete_time = SYSDATE
,runtime = calc_runtime_func(start_time)
,table_rowcount = v_row_count
WHERE load_num = v_load_num
AND load_step = p_year || ' SET X1040 Return cgen';
COMMIT;
--Insert into Load Log Table the comment the process was NOT completed
EXCEPTION
WHEN OTHERS THEN
v_row_count := SQL%ROWCOUNT;
ROLLBACK;
UPDATE load_log SET status = 'FAILED'
,complete_time = SYSDATE
,runtime = calc_runtime_func(start_time)
,table_rowcount = v_row_count
,error_stack = DBMS_UTILITY.FORMAT_ERROR_STACK
WHERE load_num = v_load_num
AND load_step = p_year || ' SET X1040 Return cgen';
COMMIT;
END x1040_Return_load;
|
