Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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;

Reply With Quote
  #2 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:44 PM.


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