"1.- It depends on many things..." point is very true
i upgraded my POC records to 7.5 million and tested :)..but in case of multistatement request (delete;insert) its taking more time comparing to first executing delete & then insert (as two transactions)..
DELETE FROM SGAM_DB.PRUEBA01 a WHERE EXISTS (SELECT NULL FROM SGAM_DB.PRUEBA02 b WHERE a.ID_N = b.ID_N ) ;
*** Delete completed. 7567268 rows removed. *** Total elapsed time was 2 seconds.
INSERT INTO SGAM_DB.PRUEBA01 ( ID_N, C_TXT) SELECT ID_N, 'UPDATED' FROM SGAM_DB.PRUEBA02;
*** Insert completed. 7567268 rows added. *** Total elapsed time was 3 seconds.
UPDATE SGAM_DB.PRUEBA01 a SET C_TXT='UPDATED' WHERE EXISTS (SELECT NULL FROM SGAM_DB.PRUEBA02 b WHERE a.ID_N = b.ID_N ) ;
*** Update completed. 7567268 rows changed. *** Total elapsed time was 2 seconds.
Nobody has mentioned this yet but the MERGE statement introduced in Teradata 12 simply blows the performance of both DEL/INS and UPDATE away. I saw measurable performance improvement by 60% or more. The syntax may be a little long but it is worth the benefit.