You 'small' POC is a VERY SMALL POC and not accurate at all:
Here is a counterexample:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA01,
NO JOURNAL,
NO FALLBACK,
CHECKSUM = DEFAULT
( ID_N DECIMAL(12,0) NOT NULL,
C_TXT CHAR(25) NULL
)
PRIMARY INDEX (ID_N)
;
*** Table has been created.
*** Total elapsed time was 1 second.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 ( ID_N, C_TXT)
SELECT CAST(NUM_COL AS DECIMAL(12,0)), NUM_COL FROM MY_DB.MY_BIG_SOURCE_TABLE;
*** Insert completed. 53315508 rows added.
*** Total elapsed time was 37 seconds.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE DW_USUARIO.PRUEBA02,
NO JOURNAL,
NO FALLBACK,
CHECKSUM = DEFAULT
( ID_N DECIMAL(12,0) NOT NULL,
C_TXT CHAR(25) NULL
)
PRIMARY INDEX (ID_N)
;
*** Table has been created.
*** Total elapsed time was 1 second.
--Now we populate this second table with roughly the 10% of the rows
BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA02 ( ID_N, C_TXT)
SELECT CAST(NUM_COL AS DECIMAL(12,0)), NUM_COL FROM MY_DB.MY_BIG_SOURCE_TABLE WHERE NUM_COL MOD 10 = 0;
*** Insert completed. 4442219 rows added.
*** Total elapsed time was 10 seconds.
--Now, the DELETE-INSERT:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
DELETE FROM MY_DB.PRUEBA01 a
WHERE EXISTS (SELECT NULL
FROM MY_DB.PRUEBA02 b
WHERE a.ID_N = b.ID_N )
;INSERT INTO MY_DB.PRUEBA01 ( ID_N, C_TXT) SELECT ID_N, 'UPDATED' FROM MY_DB.PRUEBA02
;
*** Delete completed. 4442219 rows removed.
*** Total elapsed time was 25 seconds.
[edited later: forgot to paste the INSERT part :( ]
*** Insert completed. 4442219 rows added.
[/edited later]
--Now, the UPDATE:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
UPDATE MY_DB.PRUEBA01 a
SET C_TXT='UPDATED'
WHERE EXISTS (SELECT NULL
FROM MY_DB.PRUEBA02 b
WHERE a.ID_N = b.ID_N )
;
*** Update completed. 4442219 rows changed.
*** Total elapsed time was 3 seconds.
And, IN THESE CIRCUMSTANCES, the UPDATE wins (3 sec vs. 25 sec).
what's the moral of the story?
1.- It depends on many things...
2.- Don't take anything for granted based on small POC's.
3.- Test, test, test.
Cheers.
Carlos.
