5

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.

  1. Table statistics (will these get outdated faster?)
  2. Fragmentation

Please post your experiences.

Thanks in advance!!

  • Fragmentation will really depend on the structure of the table in question. If possible, can you put the DDL for the table creation in your question? – Thomas Stringer Mar 6 '14 at 12:44
  • So, is fragmentation not dependent on operations (insert / update/ delete)? – blob Mar 6 '14 at 12:52
  • That's not what I was getting at. It indeed will be dependent on the operations, but in order to see at the magnitude we'd really need to see the table. – Thomas Stringer Mar 6 '14 at 13:09
  • Are you asking because you want to compare a DELETE+INSERT pattern versus a DELETE+UPDATE+INSERT pattern? – Jon Seigel Mar 6 '14 at 14:29
  • DELETE + INSERT Vs INSERT + UPDATE , instead of updates I am deleting and re-inserting the data – blob Mar 6 '14 at 15:39
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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