IF EXISTS до INSERT, UPDATE, DELETE для оптимизации

Часто возникает ситуация, когда вам нужно выполнить инструкцию INSERT, UPDATE или DELETE на основе некоторого условия. И мой вопрос заключается в том, влияет ли влияние на производительность запроса IF EXISTS перед командой.

Пример

IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
    UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1

Как насчет INSERT или DELETE?

Ответ 1

Я не совсем уверен, но у меня создается впечатление, что этот вопрос действительно касается upsert, который является следующей атомной операцией:

Если строка существует как для источника, так и для цели, UPDATE target;
Если строка существует только в источнике, INSERT строка в цель;
(необязательно) Если строка существует в целевом, но не источнике, DELETE строка из целевого объекта.

Разработчики-повернутые администраторы баз данных часто наивно записывают его по очереди, например:

-- 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.
Это расточительно. Для каждой транзакции выполняется дополнительная операция; возможно, это тривиально, но это зависит полностью от того, насколько хорошо вы проиндексировали.
Хуже всего - он следует за итеративной моделью, думая об этих проблемах на уровне одной строки. Это будет иметь наибольшее (худшее) влияние всех на общую производительность.

Одна очень незначительная (и я подчеркиваю небольшая) оптимизация - это просто попробовать 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 вам пришлось использовать неудобную 3-ступенчатую модель для решения этой проблемы на заданном уровне (еще лучше, чем строка за строкой):

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 в зависимости от того, существует ли уже эта строка - , даже если это только одна строка - существует no извинение не использовать MERGE.

Вы можете даже OUTPUT строки, затронутые MERGE, в переменную таблицы, если вам нужно будет узнать после этого, что было сделано. Просто, быстро и без риска. Сделайте это.

Ответ 2

Это не полезно только для одного обновления/удаления/вставки.
Возможно добавление производительности, если несколько операторов после условия. В последнем случае лучше писать

update a set .. where ..
if @@rowcount > 0 
begin
    ..
end

Ответ 3

Вы не должны делать это для UPDATE и DELETE, как если бы производительность воздействовала на производительность, она не была положительной.

Для INSERT могут возникнуть ситуации, когда ваш INSERT вызовет исключение (UNIQUE CONSTRAINT нарушение и т.д.), и в этом случае вы можете предотвратить его с помощью IF EXISTS и обработать его более изящно.

Ответ 4

Ни

UPDATE … IF (@@ROWCOUNT = 0) INSERT

ни

IF EXISTS(...) UPDATE ELSE INSERT

шаблоны работают, как ожидалось, при высоком concurrency. Оба могут потерпеть неудачу. Оба могут терпеть неудачу очень часто. 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 ) ;

IF EXISTS (...) THEN шаблон часто терпит неудачу при высоком concurrency.

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

-- 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 ;

Когда мы запускаем этот script одновременно на двух вкладках, мы сразу получим огромное количество нарушений первичного ключа на обеих вкладках. Это показывает, насколько ненадежным является шаблон IF EXISTS, когда он выполняется при высоком concurrency.

Примечание. В этом примере также показано, что небезопасно использовать SELECT MAX (ID) +1 или SELECT MIN (ID) -1 в качестве следующего доступного уникального значения, если мы делаем это под concurrency.

Ответ 5

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

Лучший способ узнать наверняка - проверить два различия и посмотреть, какая из них дает вам соответствующую производительность.

Ответ 6

IF EXISTS будет в основном делать SELECT - то же, что и UPDATE.

Как таковой, он будет снижать производительность - если ничего не обновить, вы сделали то же самое количество работы (UPDATE запросил бы такое же отсутствие строк, что и ваш выбор), и если что-то обновить, вы делали нужный выбор.

Ответ 7

Производительность оператора IF EXISTS:

IF EXISTS(SELECT 1 FROM mytable WHERE someColumn = someValue)

зависит от индексов, присутствующих для выполнения запроса.

Ответ 8

Есть небольшой эффект, так как вы делаете одну и ту же проверку дважды, по крайней мере, в своем примере:

IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)

Требуется выполнить запрос, посмотреть, есть ли какие-либо, если true:

UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1

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

Ответ 9

Это в значительной степени повторяет предыдущие (по времени) пять (нет, шесть) (нет, семь) ответов, но:

Да, структура IF EXISTS, которая у вас есть, удвоит работу, выполняемую базой данных. Если IF EXISTS "остановится", когда найдет первую совпадающую строку (ей не нужно их найти), она по-прежнему добавляет и в конечном итоге бессмысленное усилие - для обновлений и удалений.

Если таких строк нет, IF EXISTS будет полностью сканировать (таблица или индекс), чтобы определить это.
Если существует одна или несколько таких строк, IF EXISTS будет достаточно читать таблицу/индекс, чтобы найти первую, а затем UPDATE или DELETE затем перечитает, что таблица снова найдет ее и обработает. он будет читать "остальную часть" таблицы, чтобы узнать, есть ли еще что-либо для обработки. (Достаточно быстро, если правильно проиндексирован, но все же.)

Итак, так или иначе, вы в конце концов будете читать всю таблицу или индекс хотя бы один раз. Но, зачем вообще беспокоиться об ИСПЫТАНИЯХ?

UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1

или аналогичный DELETE будет работать нормально, есть ли какие-либо строки, которые будут обработаны. Никаких строк, таблицы не проверено, ничего не изменилось, все готово; 1+ строк, сканированных таблиц, все, что должно быть изменено, сделано снова. Один проход, без суеты, без шума, не нужно беспокоиться о том, "изменила ли база данных другой пользователь между моим первым запросом и моим вторым запросом".

INSERT - это ситуация, когда это может быть полезно - проверьте, присутствует ли строка перед ее добавлением, чтобы избежать нарушений первичного или уникального ключа. Конечно, вы должны беспокоиться о concurrency - что, если кто-то пытается добавить эту строку одновременно с вами? Объединение всего этого в один 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>

Ответ 10

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

Ответ 11

Если вы используете MySQL, вы можете использовать insert... в дубликате.

Ответ 12

IF EXISTS....UPDATE

Не делай этого. Он заставляет два сканирования/ищет вместо одного.

Если обновление не находит совпадения в предложении WHERE, стоимость оператора обновления - это просто поиск/сканирование.

Если он найдет совпадение, и если вы предисловите его w/IF EXISTS, он должен найти одно и то же совпадение дважды. И в параллельной среде то, что было верно для EXISTS, может быть больше недействительным для UPDATE.

Именно поэтому инструкции UPDATE/DELETE/INSERT допускают предложение WHERE. Используйте его!