delete & insert vs update

Tools & Utilities
Enthusiast

delete & insert vs update

hi all,

can anyone tell me the best way in updating a record ( with efficiency)

delete & insert

or

update
9 REPLIES
Enthusiast

Re: delete & insert vs update

It is based on your data volume :) Plus configuration & performance of the server too.

If you want to update very less amount of data (ex out of 1 million records) some 1 to 5% records you want update, then we can consider going for UPDATE dml.

In case out of 1 million you want to update 10 to 50% records, then go for DELETE and INSERT

Enthusiast

Re: delete & insert vs update

thanks for helping me out....
Junior Supporter

Re: delete & insert vs update


>>"If you want to update very less amount of data (ex out of 1 million records) some 1 to 5% records you want update, then we can consider going for UPDATE dml."

>>"In case out of 1 million you want to update 10 to 50% records, then go for DELETE and INSERT"

and you stated that because...

Cheers.

Carlos.
Enthusiast

Re: delete & insert vs update

Hi Carlos,

The % given here are very generic, there is no exact hard line..

But always there is a huge time difference, when we try to UPDATE a table with/without residual condition(s). So if we reduce the number of rows to update it is always pretty fast.

In case of updating more number of records, we can use MLOAD Delete and MLOAD Import..

here is my small POC result:

A Table contains 9000 records (Multiset with NUPI) and want to update around 2000 records.

Only UPDATE DML takes 2.07 seconds
Delete and Insert (via BTEQ) totally takes 0.36 seconds

In the same table with another residual condition , UPDATE for around 400 records it execute within 0.09 seconds :)
Junior Supporter

Re: delete & insert vs update

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.
Enthusiast

Re: delete & insert vs update

"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.

Re: delete & insert vs update

what is the difference between the vartext and data file.actually my question is when we are use this "VARTEXT" and "DATA" file type in bteq in terabata.

Re: delete & insert vs update

1.- Generally avoid updating your primary index column(s);
2.- As it has been already mentioned - test your case;

Re: delete & insert vs update

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.