Why Large Modifications Are Never Fast

Well, Stop


How many times have you seen something like this?

honeypot

You can replace “Update” with “Insert” or “Delete”, but the story is the same.

Large modifications are typically not fast modifications, for a number of reasons. To understand why, we need to think about the process as a whole.

Modi-Fi


Why large modifications are slow can root-cause to a number of places:

  1. Updates and Deletes run single-threaded, no matter what
  2. Inserts can go parallel, but the rules are often prohibitive
  3. Halloween Protection Spools can be really slow
  4. You probably have a bunch of nonclustered indexes that need to be read so that…
  5. All those indexes can be modified, and written to the transaction log as well
  6. If your database is synchronously replicated anywhere, you have to wait on that ack
  7. There are no Batch Mode modifications

Usually when modifications need to hit a significant number of rows, I want to think about batching things. That might not always be possible, but it’s certainly a kinder way to do things.

If you can’t batch inserts and deletes, you may want to think about using partitioning to quickly switch data in and out.

And of course, don’t use MERGE.

Thanks for reading!



5 thoughts on “Why Large Modifications Are Never Fast

  1. Is the MERGE stmt going to put a stick between the wheels? (as we literally say in Italy)
    Holy ship!

Leave a Reply

Your email address will not be published. Required fields are marked *