Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 12 Mar 2008 @ 12:56:02 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Stored Procedure Error Handling
 
From:   Dieter Noeth

David Clough wrote:

  Because I'm using a Cursor to read from a Table and then, depending upon the data in the row, Inserting into TEMP_TAB or writing into a more permanent table :  


          > SELECT RN1.OLD_ACCT_ID AS OLD_ACC
          >      , RN1.NEW_ACCT_ID AS CURR_ACC
          >      , RS1.NEW_ACCT_ID AS NEW_ACC
          >         FROM
          >                 RENUM_CAND RN1
          >         LEFT JOIN
          >                 RENUM_SET RS1
          >         ON RN1.NEW_ACCT_ID = RS1.OLD_ACCT_ID
          > DO
          >         IF A_csr.NEW_ACC IS NULL
          >            THEN INSERT INTO  FINAL_SET
          >                    VALUES (A_csr.OLD_ACC,COALESCE(A_csr.CURR_ACC,A_csr.OLD_ACC));
          >            ELSE INSERT INTO  TEMP_SET
          >                    VALUES (A_csr.OLD_ACC,A_csr.NEW_ACC);
          >                  SET ACCT_CT = ACCT_CT + 1;
          >            END IF;
  This works but is horribly slowly ! This, I guess is because every Insert is committing, one at a time into an non-empy table.  


No, it's because of single row inserts.

There's one basic rule: Cursors are evil if used to process large data sets. This is also true in other DBMSs, but it's especially evil in a parallel system: a cursor in Teradata runs on a single node (your session's PE) and it's running serially, fetch next, fetch next, fetch next...


  If I compiled in ANSI mode would I then be able to control the Commits by only doing a Commit every 1000 rows or so ?  


Probably, but this will hardly help.


  Would this help or am I just stuck with the performance as I've currently got it ?  


  Would it be the same if I transferred the Insert statements into a Macro and then invoked the Macro instead ? Just thinking aloud.  


Just replace the cursor with SQL:

     insert into FINAL_SET VALUES
     select ...
     where NEW_ACC IS NULL
     ;insert into TEMP_SET VALUES
     select ...
     where NEW_ACC IS NOT NULL
     ;

If it's a MultiStatement Request, it's probably materializing the select once and then reuses it for both inserts.

That "SET ACCT_CT = ACCT_CT + 1;" might be replaced with a ROW_NUMBER.

Maybe you don't even need that volatile table at all.


Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016