I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function.
Here is my specific example.
I have to update a database with pages that contain 1000 records at a time. (I cannot merge the pages).
About 5% of these records, or 50 rows, are duplicates that need to be 'updated' rather than inserted as brand new.
I figure instead of an "update based on ID, else insert new row" typical function it might be faster to "insert everything" and delete the duplicates, in one shot, at the very end.
parallelism. If I want multiple processes working on this task at the same time, well ... I can run into row locks if I have a big commit size and transactions searching for, and updating, IDs at the same time. With the "insert everything" and delete the 'older' records later, I can have unlimited processes writing data at the same time.
I feel it's easy to optimize one big "delete lookup" at the very end. It looks like the following:
with CTE as ( select primary_id,update_date, rn = row_number()over(partition by primary_id order by update_date desc) from MyTable ) delete from CTE where rn > 1
I mean the performance gains are there -- I'm just wondering if this goes against best practices. Can someone see why inserts + delete duplicates seems to work faster than 'update, if not found, insert'?
I can see one danger is that there is a period of time while the Data Load is running that the Table is not accurate (before the deletions). But isn't this sort of true in the middle of any update process?
This would also be a staging table for a data warehouse, not live in-use data. I'm just wondering why I haven't seen this method more often.