Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted.

My question is, how this will impact the performance?

We are using SQL Server 2012.

• Table statistics (will these get outdated faster?)

• Fragmentation

Please post your experiences.

Thanks in advance!!

Using a DELETE + INSERT pattern instead of also involving UPDATEs can be highly problematic. IMO, it's an anti-pattern. In 99.9% of cases, it's better to use UPDATES when possible.

Usually the DELETE + INSERT pattern is implemented such that the subset of rows is blindly deleted in the target, which can create a tremendous amount of unnecessary write activity because the data is always re-written even if nothing changed.

Not only does this put load on the I/O subsystem, but it can also kill data access concurrency unless snapshot isolation is used (which will multiply the wasted disk write workload). If the default isolation level is used and the data is concurrently read, this pattern can produce deadlocks very readily and in large quantity.

Having said that, one needs to implement UPDATEs carefully because they still have the potential to rewrite rows that haven't changed. Thankfully that's solved easily with a bit of extra code. All in all, it's well worth the effort to use UPDATEs despite the slight added code complexity.

Depending on what you're doing, it may be easier and more reliable to send the entire desired state as a whole to the database using something like a table-valued parameter and sorting things out there instead of on the client side.

StackExchange Database Administrators Q#60310, answer score: 3