Merge Vs Delete/Insert

Database
Enthusiast

Merge Vs Delete/Insert

Recently I encountered an interesting discussion with one of my fellow developer.

I have a following scenario:

I have staged data with 56 millions rows. Some of the column values are really huge like Varchar(2500). I need to UPSERT stage table with target table. I thought to choose 'MERGE' since it is fastest and does block level updates/inserts. My friend claimed that merge statement takes more time on huge volume. Instead, he suggested to go with DELETE/Insert approach.

I am little skeptical about using DELETE via Stage/Target joins.

What do you recommend?

Tags (2)
4 REPLIES
Teradata Employee

Re: Merge Vs Delete/Insert

Hi,

It can vary from case to case depending on index selection and stats on the table .... have you benchmarked MERGE and DELETE/INSERT approaches?

Generally MERGE should be faster in execution, but I have seen DELETE/INSERT queries working faster.

HTH!

Regards, MAC

Enthusiast

Re: Merge Vs Delete/Insert

//have you benchmarked MERGE and DELETE/INSERT approaches?//

I am going to.

//Generally MERGE should be faster in execution, but I have seen DELETE/INSERT queries working faster.//

This is what my friend's claim too. I have worked on big table with less columns. Merge was too fast.

FYI. Stage and Target table schemas are exactly same, and same PI.

Enthusiast

Re: Merge Vs Delete/Insert

Merge statements are definitely faster by atleast 30%, from my experience, but the most important benefit is error handling.
One thing to watchout for is for tables with PPI, it could get tricky with a UPI/PPI combination.
Teradata Employee

Re: Merge Vs Delete/Insert

Do share results of your benchmarking for our learning.

Regards, MAC