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

Скажем, у меня есть простая таблица, которая имеет следующие поля:

ID: int, автоинкрементный (идентификатор), первичный ключ
Имя: varchar (50), уникальный, имеет уникальный индекс
Тег: int

Я никогда не использую поле ID для поиска, потому что мое приложение всегда основывается на работе с полем "Имя".

Мне нужно время от времени менять значение тега. Я использую следующий тривиальный код SQL:

UPDATE Table SET Tag = XX WHERE Name = YY;

Я задавался вопросом, знает ли кто-нибудь, что выше всегда быстрее, чем:

DELETE FROM Table WHERE Name = YY;
INSERT INTO Table (Name, Tag) VALUES (YY, XX);

Опять же - я знаю, что во втором примере ID изменен, но это не имеет значения для моего приложения.

Ответ 1

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

Контроллер транзакций (генерирующий родительский образец), содержащий два запроса JDBC: оператор "Удалить" и "Вставка"
Отдельный запрос JDBC, содержащий оператор Update.

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

DEL + INSERT - Средний: 62 мс

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

Результаты:

Ответ 2

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

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

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

Ответ 3

Одна команда в той же строке всегда должна быть быстрее двух в той же строке. Поэтому 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 @[email protected]+1
    update YourTable set YourName='new name' where YourName='new name'
    SET @[email protected][email protected]@ROWCOUNT
end
print @y

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

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

Ответ 4

Я боюсь, что тело вашего вопроса не связано с вопросом о титуле.

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

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

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

Просто google для

"Дорогое прямое обновление" * "sql server"
"отложенное обновление" * "sql server"

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

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

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

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

Ответ 5

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

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

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

Петр

MS SQL 2008

Ответ 6

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

Update:

Ищите строку, используя PK.
Прочитайте строку с диска.
Проверьте, какие значения изменились.
Поднимите onUpdate Trigger с заселенными: NEW и: OLD variables
Запись новых переменных на диск (вся строка)

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

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

Отметьте строки как удаленные (только в PK).
Вставьте новые строки в конец таблицы.
Обновить индекс PK с местоположением новых записей.

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

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

Ответ 7

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

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

Ответ 8

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

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

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

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

Ответ 9

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

Является ли выбор лучше, чем другой, или вы ввернули оба пути?

Ответ 10

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

Другая, второстепенная проблема заключается в том, что при обновлении одной переменной в одной строке остальные столбцы в этой строке остаются неизменными. Если вы УДАЛИТЬ, а затем выполните INSERT, вы рискуете забыть о других столбцах и, следовательно, оставить их (в этом случае вам нужно будет выполнить SELECT до вашего DELETE, чтобы временно сохранить ваши другие столбцы, прежде чем записывать их обратно с INSERT).

Ответ 11

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

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

Ответ 12

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

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

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

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

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

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

Ответ 13

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

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

Если вы собираетесь нарушать правила работы кода, тогда вам лучше наброситься на это, количественно объяснить причину, а не смутное представление о том, что "этот путь быстрее", Подумайте, что такое "быстрее".

Ответ 14

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