Archives of the TeradataForum
Message Posted: Tue, 01 Jun 2010 @ 09:09:31 GMT
<-- Anonymously Posted: Tuesday, June 01, 2010 05:00 -->
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;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|