В SQL ОБНОВЛЕНИЕ всегда быстрее, чем DELETE+INSERT?

Каждая запись в базу данных имеет множество потенциальных побочных эффектов.

Удалить: необходимо удалить строку, обновить индексы, проверить внешние ключи и, возможно, удалить каскадом и т. Д. Insert: строка должна быть выделена - это может быть вместо удаленной строки, а может и нет; должны быть обновлены индексы, проверены внешние ключи и т. д. Обновление: необходимо обновить одно или несколько значений; возможно, данные строки больше не помещаются в этот блок базы данных, поэтому необходимо выделить больше места, что может каскадировать в несколько перезаписываемых блоков или привести к фрагментированным блокам; если значение имеет ограничения внешнего ключа, они должны быть проверены и т. д.

Для очень небольшого числа столбцов или если обновляется вся строка, удаление + вставка может быть быстрее, но проблема ограничения FK является большой. Конечно, возможно, у вас сейчас нет ограничений FK, но всегда ли это будет правдой? А если у вас есть триггер, проще написать код, обрабатывающий обновления, если операция обновления действительно является обновлением.

Еще одна проблема, о которой следует подумать, заключается в том, что иногда вставка и удаление удерживают разные блокировки, чем обновление. БД может заблокировать всю таблицу во время вставки или удаления, в отличие от простой блокировки одной записи, пока вы обновляете эту запись.

В конце я бы предложил просто обновить запись, если вы хотите обновить ее. Затем проверьте статистику производительности вашей БД и статистику для этой таблицы, чтобы увидеть, нужно ли улучшить производительность. Все остальное преждевременно.

Пример из системы электронной коммерции, над которой я работаю: мы сохраняли данные транзакции кредитной карты в базе данных в двухэтапном подходе: сначала напишите частичную транзакцию, чтобы указать, что мы начали процесс. Затем, когда данные авторизации будут возвращены из банка, обновите запись. Мы МОЖЕМ удалить, а затем снова вставить запись, но вместо этого мы просто использовали обновление. Наш администратор базы данных сказал нам, что таблица была фрагментирована, потому что БД выделяла только небольшой объем пространства для каждой строки, и обновление вызвало цепочку блоков, так как добавило много данных. Однако вместо того, чтобы переключаться на DELETE + INSERT, мы просто настроили базу данных, чтобы всегда выделять всю строку, это означает, что обновление может использовать предварительно выделенное пустое пространство без проблем. Изменения кода не требуется, код остается простым и понятным.

Это зависит от продукта. Может быть реализован продукт, который (под обложками) преобразует все UPDATE в (транзакционно упакованные) DELETE и INSERT. При условии, что результаты соответствуют семантике UPDATE.

Я не говорю, что знаю какой-либо продукт, который делает это, но это совершенно законно.

Чем больше таблица (количество и размер столбцов), тем дороже становится удаление и вставка, а не обновление. Потому что вы должны заплатить цену UNDO и REDO. Операции DELETE занимают больше места UNDO, чем UPDATE, а ваш REDO содержит вдвое больше операторов, чем необходимо.

Кроме того, это совершенно неправильно с точки зрения бизнеса. Подумайте, насколько сложнее было бы понять условный контрольный журнал в этой таблице.

Есть несколько сценариев, включающих массовое обновление всех строк в таблице, где быстрее создать новую таблицу с использованием CTAS из старой таблицы (применяя обновление в проекции предложения SELECT), удалив старую таблицу и переименование новой таблицы. Побочные эффекты - создание индексов, управление ограничениями и обновление привилегий, но это стоит учитывать.

Очевидно, что ответ зависит от того, какую базу данных вы используете, но UPDATE всегда можно реализовать быстрее, чем DELETE + INSERT. Поскольку операции в памяти в любом случае в большинстве случаев тривиальны, учитывая базу данных на жестком диске, ОБНОВЛЕНИЕ может изменить поле базы данных на месте на жестком диске, в то время как удаление удалит строку (оставив пустое пространство) и вставит новую строка, возможно, до конца таблицы (опять же, это все в реализации).

Другая, небольшая проблема заключается в том, что когда вы ОБНОВЛЯЕТЕ одну переменную в одной строке, другие столбцы в этой строке остаются такими же . Если вы удалите, а затем выполните INSERT,

В вашем случае, я считаю, что обновление будет быстрее.

Помните индексы!

Вы определили первичный ключ, он, скорее всего, автоматически станет кластеризованным индексом (по крайней мере, SQL Сервер так и делает). Кластерный индекс означает, что записи физически размещаются на диске в соответствии с индексом. Сама операция DELETE не вызовет особых проблем, даже после удаления одной записи индекс остается правильным. Но когда вы ВСТАВЛЯЕТЕ новую запись, движок БД должен будет поместить эту запись в правильное место, что при определенных обстоятельствах вызовет некоторую «перетасовку» старых записей, чтобы «освободить место» для новой. Там, где это замедлит работу.

Индекс (особенно кластерный) работает лучше всего, если значения когда-либо увеличиваются, поэтому новые записи просто добавляются к хвосту.

Имейте в виду, что фактическая фрагментация, которая происходит, когда выполняется DELETE + INSERT, в отличие от правильно реализованного UPDATE, будет иметь большое значение со временем.

Вот почему, например, REPLACE INTO, который реализует MySQL, не рекомендуется, а не использовать синтаксис INSERT INTO ... ON DUPLICATE KEY UPDATE ...

Одна команда в одной строке всегда должна быть быстрее, чем две в той же строке. Так что лучше было бы только ОБНОВЛЕНИЕ.

РЕДАКТИРОВАТЬ настроить таблицу:

create table YourTable
(YourName  varchar(50)  primary key
,Tag int
)

insert into YourTable values ('first value',1)

запустить это, что занимает 1 секунду в моей системе (sql server 2005):

SET NOCOUNT ON
declare @x int
declare @y int
select @x=0,@y=0
UPDATE YourTable set YourName='new name'
while @x<10000
begin
    Set @x=@x+1
    update YourTable set YourName='new name' where YourName='new name'
    SET @y=@y+@@ROWCOUNT
end
print @y

запустить это, что заняло 2 секунды в моей системе:

SET NOCOUNT ON
declare @x int
declare @y int
select @x=0,@y=0
while @x<10000
begin
    Set @x=@x+1
    DELETE YourTable WHERE YourName='new name'
    insert into YourTable values ('new name',1)
    SET @y=@y+@@ROWCOUNT
end
print @y

Только что попытался обновить 43 поля в таблице с 44 полями, оставшееся поле было первичным кластеризованным ключом.

Обновление заняло 8 секунд.

Удаление + Вставка выполняется быстрее, чем минимальный интервал времени, который "Статистика клиента" сообщает через SQL Management Studio.

Peter

MS SQL 2008