Archives of the TeradataForum
Message Posted: Wed, 12 Mar 2008 @ 12:56:02 GMT
David Clough wrote:
> 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;
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...
Probably, but this will hardly help.
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|