Существует довольно часто ситуация, когда необходимо выполниться, ВСТАВЛЯЮТ, ОБНОВЛЕНИЕ или Оператор удаления на основе некоторого условия. И мой вопрос состоит в том, добавляет ли влияние на производительности запроса, СУЩЕСТВУЕТ ли перед командой.
Пример
IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
Что относительно ВСТАВОК или УДАЛЯЕТ?
Я не совсем уверен, но у меня сложилось впечатление, что этот вопрос действительно касается upsert, то есть следующей атомарной операции:
ОБНОВИТЬ
цель; ВСТАВИТЬ
строку в цель; УДАЛИТЬ
строку из цели. Разработчики, ставшие администраторами баз данных, часто наивно пишут это построчно, например:
-- For each row in source
IF EXISTS(<target_expression>)
IF @delete_flag = 1
DELETE <target_expression>
ELSE
UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>
ELSE
INSERT target (<target_columns>)
VALUES (<source_values>)
Это чуть ли не худшее, что вы можете сделать по нескольким причинам:
Это состояние гонки. Строка может исчезнуть между IF EXISTS
и последующим DELETE
или UPDATE
.
Это расточительно. Для каждой транзакции выполняется дополнительная операция; возможно, это тривиально, но это полностью зависит от того, насколько хорошо вы проиндексировали.
Хуже всего - это следовать итеративной модели, размышляя об этих проблемах на уровне одной строки. Это окажет наибольшее (наихудшее) влияние на общую производительность.
Одна очень незначительная (и я подчеркиваю второстепенная) оптимизация - в любом случае просто попытаться выполнить ОБНОВЛЕНИЕ
; если строка не существует, @@ ROWCOUNT
будет равно 0, и затем вы можете «безопасно» вставить:
-- For each row in source
BEGIN TRAN
UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>
IF (@@ROWCOUNT = 0)
INSERT target (<target_columns>)
VALUES (<source_values>)
COMMIT
В худшем случае это все равно будет выполнять две операции для каждой транзакции, но, по крайней мере, есть шанс выполнения только одного, а также устраняет условие гонки (своего рода).
Но настоящая проблема в том, что это все еще делается для каждой строки в источнике.
До SQL Server 2008 вам приходилось использовать неудобную трехэтапную модель, чтобы справиться с этим на заданном уровне (все же лучше, чем построчно):
BEGIN TRAN
INSERT target (<target_columns>)
SELECT <source_columns> FROM source s
WHERE s.id NOT IN (SELECT id FROM target)
UPDATE t SET <target_columns> = <source_columns>
FROM target t
INNER JOIN source s ON t.d = s.id
DELETE t
FROM target t
WHERE t.id NOT IN (SELECT id FROM source)
COMMIT
Как я уже сказал, производительность на этом уровне была довольно низкой. , но все же намного лучше, чем подход «по одной строке за раз». Однако в SQL Server 2008 наконец-то появился синтаксис MERGE , так что теперь все, что вам нужно сделать, это:
MERGE target
USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE <target_columns> = <source_columns>
WHEN NOT MATCHED THEN INSERT (<target_columns>) VALUES (<source_columns>)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Вот и все. Одно заявление. Если вы используете SQL Server 2008 и вам нужно выполнить любую последовательность INSERT
, UPDATE
и DELETE
в зависимости от того, существует ли уже строка - ] даже если это всего одна строка - нет никаких оправданий, чтобы не использовать MERGE
.
Вы можете даже ВЫВОДИТЬ
строки, затронутые MERGE
, в табличную переменную, если вам нужно потом узнать, что было сделано. Просто, быстро и без риска. Сделай это.
Это бесполезно для одного обновления / удаления / вставки.
Возможно, увеличивает производительность, если после условия if несколько операторов.
В последнем случае лучше написать
update a set .. where ..
if @@rowcount > 0
begin
..
end
IF EXISTS
в основном выполняет SELECT - тот же самый, что и UPDATE.
Таким образом, это снизит производительность - если нечего обновлять, вы выполнили такой же объем работы (UPDATE запросил бы такое же отсутствие строк, что и ваш выбор), и если есть что обновить, вы сделали ненужный выбор.
Да, это повлияет на производительность (степень влияния на производительность будет зависеть от ряда факторов). Фактически вы выполняете один и тот же запрос «дважды» (в вашем примере). Спросите себя, нужно ли вам защищаться в своем запросе и в каких ситуациях строка не будет там? Кроме того, с помощью оператора обновления затронутые строки, вероятно, являются лучшим способом определить, было ли что-то обновлено.
Вы не должны делать этого для UPDATE
и DELETE
, так как если и есть влияние на производительность, то оно не положительное.
Для INSERT
могут возникнуть ситуации, когда ваш INSERT
вызовет исключение (UNIQUE CONSTRAINT
violation etc), и в этом случае вы захотите предотвратить его с помощью IF EXISTS
и обработать его более изящно.
Я бы вызвал базу данных AcmeInvoice (или другое подходящее имя) и Invoicer2 схемы.
Мои причины следующие: Acmeinvoice означает, что я группирую все эти объекты/данные приложений вместе. Поэтому он может быть перемещен как одно устройство на другие машины (резервное копирование/восстановление или отсоединение/присоединение).
Схема будет Invoicer2. Приложения изменяются, возможно, в будущем у вас будет Invoicer21 (вы создаете схему) или, возможно, модуль или система отчетов (схема отчетов). Я считаю, что использование схем позволяет мне разделить данные/процедуры в одной базе данных на различные группы, что упрощает администрирование разрешений.
-121--4817551-Часть ответа может включать в себя происхождение RE в практических вычислениях. Первоначально они были теоретической концепцией из теории автоматов и теории формального языка , пока сам Кен Томпсон не написал реальную реализацию и не использовал их в qed и ed (1) .
Оригинальная версия имела только жадный синтаксис и поэтому не было решения даже принять.
-121--1339678-Ни
UPDATE … IF (@@ROWCOUNT = 0) INSERT
, ни
IF EXISTS(...) UPDATE ELSE INSERT
не узорами работать так, как ожидалось при высокой параллелизма. Оба могут потерпеть неудачу. Оба могут давать сбои очень часто. MERGE - король - он держится намного лучше. Давайте проведем стресс-тестирование и посмотрим сами.
Вот таблица, которую мы будем использовать:
CREATE TABLE dbo.TwoINTs
(
ID INT NOT NULL PRIMARY KEY,
i1 INT NOT NULL ,
i2 INT NOT NULL ,
version ROWVERSION
) ;
GO
INSERT INTO dbo.TwoINTs
( ID, i1, i2 )
VALUES ( 1, 0, 0 ) ;
ЕСЛИ СУЩЕСТВУЕТ (...) Образец THEN часто выходит из строя при высокой параллелизма.
Вставьте или обновите строки в цикле, используя следующую простую логику: если строка с заданным идентификатором существует, обновите ее, а в противном случае вставьте новую. Следующий цикл реализует эту логику. Вырезать и вставить его на две вкладки, переключиться в текстовый режим на обеих вкладках и запустить их одновременно.
-- hit Ctrl+T to execute in text mode
SET NOCOUNT ON ;
DECLARE @ID INT ;
SET @ID = 0 ;
WHILE @ID > -100000
BEGIN ;
SET @ID = ( SELECT MIN(ID)
FROM dbo.TwoINTs
) - 1 ;
BEGIN TRY ;
BEGIN TRANSACTION ;
IF EXISTS ( SELECT *
FROM dbo.TwoINTs
WHERE ID = @ID )
BEGIN ;
UPDATE dbo.TwoINTs
SET i1 = 1
WHERE ID = @ID ;
END ;
ELSE
BEGIN ;
INSERT INTO dbo.TwoINTs
( ID, i1, i2 )
VALUES ( @ID, 0, 0 ) ;
END ;
COMMIT ;
END TRY
BEGIN CATCH ;
ROLLBACK ;
SELECT error_message() ;
END CATCH ;
END ;
Когда мы запустим этот сценарий одновременно на двух вкладках, мы сразу получим огромное количество нарушений первичного ключа на обеих вкладках. Это показывает, насколько ненадежен образец IF EXISTS при его выполнении в режиме высокой параллелизма.
Примечание: этот пример также демонстрирует, что небезопасно использовать SELECT MAX (ID) + 1 или SELECT MIN (ID) -1 в качестве следующего доступного уникального значения, если мы делаем это при параллелизме.
В большинстве случаев этого делать не следует. В зависимости от вашего уровня транзакции вы создали условие гонки, теперь в вашем примере здесь это не имеет большого значения, но данные могут быть изменены от первого выбора до обновления. И все, что вы сделали, - это заставили SQL выполнять больше работы
. Лучший способ узнать наверняка - это проверить два различия и посмотреть, какое из них дает вам соответствующую производительность.
Есть небольшой эффект, поскольку вы выполняете одну и ту же проверку дважды, по крайней мере, в вашем примере:
IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
Требуется запрос, посмотрите, есть ли любые, если истина, то:
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
Требуется запрос, посмотреть какие ... одна и та же проверка дважды без причины. Теперь, если условие, которое вы ищете, проиндексировано, это должно быть быстро, но для больших таблиц вы можете увидеть некоторую задержку только потому, что вы запускаете select.
Это во многом повторяет предыдущие (по времени) пять (нет, шесть) (нет, семь) ответов, но:
Да, структура IF EXISTS, которую вы имеете, в общем и целом удвоит работу, выполняемую базой данных. Хотя IF EXISTS "остановится", когда найдет первый совпадающий ряд (ему не нужно находить их все), это все равно дополнительные и в конечном итоге бессмысленные усилия - для обновлений и удалений.
Так что в любом случае вы в конечном итоге прочитаете всю таблицу или индекс по крайней мере один раз. Но, зачем вообще беспокоиться о IF EXISTS?
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
или аналогичный DELETE будет работать нормальнонезависимо от того, найдены ли строки для обработки. Нет строк, таблица сканируется, ничего не изменяется, готово; 1+ строк, таблица сканируется, все, что должно быть, изменяется, снова готово. Один проход, никакой суеты, никакой суматохи, никакой необходимости беспокоиться о том, "не изменил ли базу данных другой пользователь между моим первым и вторым запросом".
INSERT - это ситуация, когда это может быть полезно - проверить наличие строки перед ее добавлением, чтобы избежать нарушения первичного или уникального ключа. Конечно, вы должны беспокоиться о параллелизме - что если кто-то другой пытается добавить этот ряд одновременно с вами? Если обернуть все это в один INSERT, то все это будет обработано в неявной транзакции (вспомните свои свойства ACID!):
INSERT Contacs (col1, col2, etc) values (val1, val2, etc) where not exists (select 1 from Contacs where col1 = val1)
IF @@rowcount = 0 then <didn't insert, process accordingly>
Производительность оператора IF EXISTS
:
IF EXISTS(SELECT 1 FROM mytable WHERE someColumn = someValue)
зависит от индексов, присутствующих для удовлетворения запроса.
Если вы используете MySQL, то вы можете использовать insert ... on duplicate.
IF EXISTS....UPDATE (ЕСЛИ СУЩЕСТВУЕТ)
Не делайте этого. Это заставляет проводить два сканирования/поиска вместо одного.
Если update не находит совпадения в предложении WHERE, стоимость оператора update - это просто поиск/сканирование.
Если же он находит соответствие, и если вы предваряете его IF EXISTS, ему приходится искать одно и то же соответствие дважды. А в параллельной среде то, что было верно для EXISTS, может быть уже не верно для UPDATE.
Именно поэтому в операторах UPDATE/DELETE/INSERT допускается предложение WHERE. Используйте его!