5

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.

Two reasons:

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

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

Improve this question
0

1 Answer 1

Reset to default
7

There are many reasons that an insert and delete can be faster in practice than an single update that achieves the same end result. I am not even going to attempt to list all the considerations, but for example:

  • Updates that affect an index key might appear to do an in-place update from the execution plan, but this is not the case at the lowest level. An update that affects an index key results in a separate delete and insert at the Storage Engine level in SQL Server 2005 onward.

  • Updates that change the value a key in a unique index generally mean that SQL Server has to introduce a Split/Sort/Collapse combination in the execution plan to avoid transient unique key violations. The Split explicitly turns each update into separate delete and insert operations. The cost of the extra operators (particularly the Sort) can often result in reduced performance. Plans that insert or delete to a unique index do not require this same protection, though Split operators may still be seen for other reasons.

  • There are internal optimizations available for inserts that simply do not apply to updates (or deletes). Some of these are associated with minimal logging, and some require a hint or trace flag (like 610). Other optimizations do not require trace flags or hints, and are not exposed in execution plans.

I mean the performance gains are there

This is the key point. I can tell you that my experience has been that updates and deletes are generally less optimized in SQL Server than inserts, but the key is to test in your environment. An insert and delete will not always be faster than a single update, in much the same way that separate operations will not always be faster than a single MERGE (though that is probably even more common).

There are even cases where an insert followed by an update (!) can be faster than a single update. Though not directly related to your example, you may find the details of that in my article, "Optimizing Update Queries" interesting.

Also related:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.