Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 8th, 2005, 11:35 AM
Registered User
 
Join Date: Mar 2005
Location: farmington hills, mi, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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;

 
Old March 8th, 2005, 04:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

You could use a table variable or a temporary table; store the results there, have a identity RowID field, and then loop through each record individually.

Brian




Similar Threads
Thread Thread Starter Forum Replies Last Post
Efficiency Kev_Akas PHP Databases 1 May 13th, 2008 09:24 AM
Code Efficiency rsm42 ASP.NET 1.0 and 1.1 Basics 12 August 8th, 2007 11:36 AM
efficiency qn - writing and saving xml file ak Classic ASP XML 1 March 2nd, 2004 05:26 PM
Function Efficiency Ben Access VBA 3 November 20th, 2003 04:08 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.