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