Archives of the TeradataForum

Message Posted: Tue, 01 Jun 2010 @ 09:09:31 GMT

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

<-- Anonymously Posted: Tuesday, June 01, 2010 05:00 -->

Hi All,

Good day. I have written stored procedure using CURSUR to update the rows in a table. The procedure is given below. Because similar procedure in Oracle takes few seconds to update the table, but in Teradata it is keep on running more than 20 minutes. Is it not advisable to process more ( thousands of ) rows using Teradata cursor ?

Please suggest how to use the CURSOR to increase the performance of this procedure. Thanks a lot.

The procedure is given below.

     REPLACE PROCEDURE WORKDATABASE.ln_update2(OUT nrwsupd INTEGER) BEGIN

     declare v_the_date DATE;
     declare v_dim_loan decimal(15,0);
     declare v_load_id DECIMAL(18,0);
     declare v_active char(1);

     DECLARE newvar INTEGER DEFAULT 0;

     DECLARE  cur1 CURSOR   FOR
     SELECT
     f.the_date,
     f.dim_loan,
     f.load_id,
     f.active
     FROM   WORKDATABASE.TMP1 t1, WORKDATABASE.dim1 l, WORKDATABASE.fct1 f
            WHERE       f.the_date = DATE '2010-04-14'
                    AND l. source_system='RB'
                    AND t1.id = l.id
                    AND l.dimension_key = f.dim_loan
                    and f.active in ('Y','N')  ;
     --  the above select query return 300K rows

     OPEN  cur1 ;

     WHILE (SQLCODE = 0)
     DO
     FETCH cur1 into   v_the_date , v_dim_loan, v_load_id,v_active  ;

     -- To Test , how much time it will take to update 1000 rows, I have put this  IF
     condition

     IF newvar < 1000 THEN

     UPDATE  WORKDATABASE.fct1
                SET     RECORD_LOAD_DTTM=current_timestamp(0)
                  WHERE   dim_loan = v_dim_loan
                  AND load_id = v_load_id
                  and the_date =v_the_date
                  and active =v_active;

     SET newvar = newvar + 1;

     END IF ;

       END WHILE;
         CLOSE  cur1;

     SET nrwsupd = newvar;
     END;

Thanks

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