Performance Comaprsion Between UPSERT/MERGE INTO/ DELETE AND INSERT

Database
tsk
Enthusiast

Performance Comaprsion Between UPSERT/MERGE INTO/ DELETE AND INSERT

Hi I have a batch job that has to update the target table records from a staging table. if there is  a matching primary key or else insert it as a new record.

Which of the following is the best option in terms of performance:

1) UPSERT

2)MERGE INTO

3) DELETE AND INSERT

The performance is very critical for me because the view that is built on my target table has to be available 24*7 with minimum latency and provide the latest record as much as we can,

Any inputs are highly appreciated.

1 REPLY
Enthusiast

Re: Performance Comaprsion Between UPSERT/MERGE INTO/ DELETE AND INSERT

 We converted a bunch of delete/insert jobs into merge into, results were anywhere from a 10% hit to 30% savings depending on the number of updates (more updates, more savings).  We used the merge into even with the performance hit for ETL restartability.   We were burning cpu & missing SLAs, and the merge into lets the L1 ops recover a load rather than waking the on call L2/L3.  There's an orange book on merge into processing, it's worth reading.