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