Kristen-173977 (9/23/2015)

---

fabriziodb (9/23/2015)

---

delete insert is 2 operation, i think other way is most expensive:

1) insert the rows from parameter that are not in the table

2) update the rows that are both in table and parameter

3) delete the rows that are in table but not in parameter

I think "it depends".

Are most of your calls to the Sproc:

Data exists (i.e. will be both DELETE and INSERT)

or mostly Data is new (i.e. will be only Insert)

Your case (3) to delete stale data is not actually represented in your code example above. (But I do understand that that is required to "synchronise" data). How often does scenario 3 happen?

EDIT: Re-reading your code I can now see that you might be deleted X-rows matching @key and inserting back Y-rows in their place, perhaps not all of which match on all clustered index or primary key columns.

If you do UPDATE and the "width" of the data does not change, and some/many of the columns do not change (in particular columns which are present in non-clustered indexes, and assuming that you do have some such indexes) then update will be MUCH faster than DELETE / INSERT.

You could use MERGE which may well give you the best possible performance.

i'll try to set the isolation to read uncommitted.

That would absolutely be my last choice, unless data quality / integrity was not important (I don't think I've ever been in a situation where it wasn't, but that won't be true for ever situation)