All Forums Database
njvijay 19 posts Joined 08/09
11 Oct 2011
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?

Adeel Chaudhry 773 posts Joined 04/08
11 Oct 2011

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

-- If you are stuck at something .... consider it an opportunity to think anew.

njvijay 19 posts Joined 08/09
11 Oct 2011

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

 

 

AbeK 24 posts Joined 08/09
11 Oct 2011

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.

Adeel Chaudhry 773 posts Joined 04/08
12 Oct 2011

Do share results of your benchmarking for our learning.

 

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.