I have requirement to to write a Cursor1 calling another Cursor2. Wondering, which might perform better.. 1) using CALL db.Cursor2_name ( ) in FOR...DO part of of Cursor1 or 2) nesting another FOR..DO of Cursor2 in Cursor1's FOR...DO part ?
For best performance, avoid CURSORs (which limit processing to one row at a time) and redesign your application to use SQL operations which can exploit the parallel nature of the Teradata database. If you can't eliminate both cursors, at least try to eliminate the inner one.
That said, putting a nested FOR loop in the same stored procedure is clearly less overhead than introducing another stored procedure call in between. (You CALL stored procedures, not CURSORs.)
Unfortunately, we may not able to get away with inner Cursor2 at all. Allow me to put more details as below (as I guess your further reply perhaps may add corrections or better approch).
Problem: We basically have some 30 tables in DW - affected with "Broken on History" (i.e. chronoligical order is incorrect on CAPTR_DT & CHG_DT fields). Attempt is to re-correct them, but ONLY looking to DW table's existing rows we have on hand (and not look back to pre-stage or Sources as not available..)
Cursor1 : fetch distinct "IP_num" as key from main IP table. There are around +25000 IP_Num items.
Cursor2: Take each distinct IP_num from above cursor1 & CALL (or now as you said use nested FOR ..DO instead) cursor2 to bascially fix chronological order.. Idea is go from 1st row of Cursor2 fetched untill End-of-Cursor2 (one by one) & see if any DELTA has been applied when comapred to previous saved row.. Thus keep good rows having appropriate DELTA applied on & Remove / Expire unwanted bad rows as we traverse along in Cursor2 for that distinct IP-num from Cursor1.