В SQL всегда ли UPDATE быстрее, чем DELETE + INSERT?

Слишком поздно с этим ответом, но поскольку я столкнулся с аналогичным вопросом, я провел тест с JMeter и сервером MySQL на том же компьютере, где я использовал:

1. Контроллер транзакции (генерирующий родительский образец), содержащий два запроса JDBC: оператор Delete и оператор Insert.
2. Отдельный запрос JDBC, содержащий оператор обновления.

Проведя тест на 500 петель, я получил следующие результаты:

DEL + INSERT - в среднем: 62 мс

Обновление - в среднем: 30 мс

Результаты:

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

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

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

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

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

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

Боюсь, что основная часть вашего вопроса не связана с заглавным вопросом.

Если ответить на заголовок:

В SQL всегда ли UPDATE быстрее, чем DELETE + INSERT?

тогда ответ НЕТ!

Просто погуглите

• «Дорогостоящее прямое обновление» * «sql server»
• "отложенное обновление" * "сервер sql"

Такое обновление (я) приводит к более дорогостоящей (более сложной) реализации обновления через вставку + обновление, чем прямая вставка + обновление. Это те случаи, когда

• один обновляет поле уникальным (или первичным) ключом или
• когда новые данные не помещаются (больше) в выделенное пространство строки перед обновлением (или даже максимальный размер строки), что приводит к фрагментации,
• и т.п.

Мой быстрый (не исчерпывающий) поиск, не претендующий на охват одного, дал мне [1], [2]

[1]
Операции обновления
(Sybase® SQL Server Performance and Tuning Guide
Глава 7: Оптимизатор запросов SQL Server)
http://www.lcard.ru/~nail/sybase/perf/11500.htm
[2]
Операторы UPDATE могут реплицироваться как пары DELETE / INSERT
http://support.microsoft.com/kb/238254

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

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

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

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

Операция Delete + Insert выполняется быстрее, чем минимальный интервал времени, который сообщает «Статистика клиента» через SQL Management Studio.

Питер

MS SQL 2008

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

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

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

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

Вопрос скорости не имеет значения без конкретной проблемы скорости.

Если вы пишете код SQL для изменения существующей строки, вы ОБНОВЛЯЕТЕ ее. Все остальное неверно.

Если вы собираетесь нарушить правила того, как должен работать код, вам лучше иметь чертовски хорошую количественную причину для этого, а не расплывчатое представление «Так будет быстрее», когда у вас нет никаких идея, что такое «быстрее».

Что делать, если у вас есть несколько миллионов строк. Каждая строка начинается с одного фрагмента данных, возможно, с имени клиента. По мере сбора данных для клиентов их записи должны обновляться. Теперь предположим, что набор клиентских данных распределяется по множеству других машин, с которых они позже собираются и помещаются в базу данных. Если у каждого клиента есть уникальная информация, вы не сможете выполнить массовое обновление; то есть нет никаких критериев where-clause, которые можно было бы использовать для обновления нескольких клиентов за один раз. С другой стороны, вы можете выполнять объемные вставки. Итак, вопрос может быть лучше сформулирован следующим образом: лучше ли выполнять миллионы отдельных обновлений или лучше скомпилировать их в большие массовые операции удаления и вставки. Другими словами, вместо «update [table] set field = data where clientid = 123» миллионы раз вы делаете «delete from [table], где clientid in ([все клиенты будут обновлены]); вставьте в [таблицу] значения (данные для client1), (данные для client2) и т. д.

Один из вариантов лучше другого, или вы попали в обе стороны?

Удалить + Вставить почти всегда быстрее, потому что обновление требует гораздо большего количества шагов.

Обновлять:

1. Найдите строку с помощью PK.
2. Прочтите строку с диска.
3. Проверить, какие значения изменились
4. Поднимите триггер onUpdate с заполненными: NEW и: OLD переменными.

5. Записать новые переменные на диск (вся строка)

   (Это повторяется для каждой обновляемой строки)

Удалить + Вставить:

1. Пометить строки как удаленные (только в ПК).
2. Вставьте новые строки в конец таблицы.

3. Обновите индекс PK, указав расположение новых записей.

   (Это не повторяется, все можно выполнить за один блок операции).

Использование Insert + Delete фрагментирует вашу файловую систему, но не так быстро. Выполнение ленивой оптимизации в фоновом режиме всегда освобождает неиспользуемые блоки и полностью упаковывает таблицу.

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

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

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

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

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

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

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

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

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

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

Мой сценарий - большое количество отдельных обновлений по сравнению с массовым удалением / массовой вставкой. У меня есть исторические данные о продажах для нескольких клиентов за несколько лет. Пока я не получу проверенные данные (15-е число следующего месяца), я буду корректировать показатели продаж каждый день, чтобы отразить текущее состояние, полученное из другого источника (это означает перезапись не более 45 дней продаж каждый день для каждого клиента). Изменений может не быть, а может быть несколько изменений. Я могу либо закодировать логику, чтобы найти различия и обновить / удалить / вставить затронутые записи, либо я могу просто сдуть вчерашние числа и вставить сегодняшние числа. Очевидно, что этот последний подход проще, но если он собирается убить производительность таблицы из-за оттока, то стоит написать дополнительную логику, чтобы идентифицировать несколько (или ни одной) записей, которые изменились, и только обновлять / удалять / вставлять их.

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

Записи, которые, вероятно, будут извлечены вместе, будут вставлены одновременно и, следовательно, должны быть физически близко друг к другу. Компенсирует ли это потерю производительности из-за оттока при таком подходе, и лучше ли это, чем затраты на отмену / повтор всех этих отдельных обновлений записей?

В определенных случаях Delete + Insert сэкономит ваше время. У меня есть таблица с 30000 нечетными строками, и есть ежедневное обновление / вставка этих записей с использованием файла данных. В процессе загрузки генерируется 95% операторов обновления, поскольку записи уже существуют, и 5% вставок для тех, которых не существует. В качестве альтернативы, загрузка записей файла данных во временную таблицу, удаление целевой таблицы для записей в временной таблице с последующей вставкой того же файла из временной таблицы показали 50% выигрыш во времени.