MERGE vs. INSERT,UPDATE,DELETE

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

Lookup Transformation

Первый и самый банальный способ – использовать стандартный компонент сервисов интеграции – LOOKUP.


На вход компоненту подаются данные, которые нужно сравнить с целевой таблицей. В свою очередь сам компонент LOOKUP получает ряды из целевой таблицы или SQL-запроса, а на вкладке Columns можно выбрать по каким столбцам будет проводиться сверка.

С данными, которые найдены в целевой таблице все понятно, они попадают в Match Output. С теми, для которых совпадений нет, в зависимости от поставленных целей можно поступить по разному: проигнорировать эти ряды, отправить их в error output, вызвать ошибку выполнения компонента или отправить их в поток No match output.

В рамках поставленной выше задачи нас интересует последний вариант. Если запись в целевой таблице отсутствует, то далее где-то по ходу dataflow будет лежать компонент OLE DB Destination, который, как известно, умеет исключительно вставлять новые записи в таблицу. А по ходу Match Output будет лежать OLE DB Command с инструкцией INSERT…INTO…

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

Коль скоро скорость имеет значение, можно в место целевой таблицы использовать SQL-запрос, чтобы получить только те столбцы, которые реально нужны для целей lookup’a.

Помимо того, что компонент Lookup Transformation весьма не быстрый, вышеописанный метод подразумевает, что далее по ходу выполнения Data Flow Task лежат либо OLE DB Command, либо OLE DB Destination, так что по факту используются отдельные SQL-запросы для каждой операции INSERT,UPDATE,DELETE.

Execute SQL Task
Используем инструкции INSERT, UPDATE, DELETE
Неплохой и достаточно быстрый вариант – вынести INSERT, UPDATE и DELETE за пределы сервисов интеграции – использовать компонент Execute SQL Task, который вынесет эти операции непосредственно на уровень сервера SQL.
Почему предлагается использовать Execute SQL Task, который работает на уровне Control Flow, а не OLE DB Command, работающий на уровене Data Flow? OLE DB Command работает с каждым рядом, который поступает на вход этого компонента, поэтому производительность такого решения не слишком высока, в свою очередь Execute SQL Task закидывает задание на сервер и ждет окончания его выполнения. Естественно SQL-запрос на стороне сервера будет выполнен намного быстрее. Однако для того чтобы этот метод заработал, на стороне сервера должна лежать не только целевая таблица, но и исходные данные, а для этого придется создать временную таблицу, куда этот массив данных упадет.

Так что если мне нужно обновить целевую таблицу информацией, которая лежит, скажем, в документе Excel, я создаю обычный Data Flow Task, в котором забираю исходные данные, фильтрую их, если необходимо, проделываю различные трансформации. Все для того, чтобы загрузить их во временную таблицу на сервере SQL.

Теперь, чтобы обновить информацию в целевой таблице достаточно бросить Execute SQL Task со следующим SQL-запросом:

INSERT INTO Schema.TableB
SELECT     Field1,Field2,...,FieldN
FROM         Support.TableA AS A
-- я использую схему Support для хранения вспомогательных таблиц
WHERE     (NOT EXISTS
(SELECT     A.Field1
FROM          Schema.TableB s AS B
WHERE      (A.Field1 = B.Field1)));
UPDATE Schema.TableB
SET Field3 = Support.TableA.Field3
WHERE Field1  = Support.TableA.Field1 AND Field2 = Support.TableA.Field2

Хотя работать вышеуказанный код будет довольно шустро, INSERT, UPDATE крутятся в разных запросах, что опять же негативно скажется на производительности.

Используем MERGE

MERGE Schema.TableB as target
USING (SELECT Field1,Field2,...,FieldN FROM Support.ReverseTablogix) as source
ON (target.Field1 = source.Field1 AND target.Field2 = source.Field2)
WHEN MATCHED THEN
UPDATE SET target.Field3 = source.Field3
WHEN NOT MATCHED THEN
INSERT (Field1,Field2,...,FieldN)
VALUES (source.Field1,source.Field2,...,source.FieldN);

В общем виде все достаточно просто: целевая таблица, таблица-источник, линкуются по таким-то полям. Вот так одним махом можно решить проблему и апдейтов, и инсертов, и, если потребуется удалений. Более того, условия MATCHED и NOT MATCHED, можно дополнять своими условиями, например:

WHEN MATCHED and target.FieldN < source.FieldN
...action
WHEN MATCHED and target.FieldN > source.FieldN
...action

Плюс результат работы можно вывести в отдельную таблицу для целей ведения логов и анализа:

OUTPUT $action,DELETED.Field1,DELETED.Field2,INSERTED.Field1,INSERTED.Field2

В общем возможности этой функции очень широки, а скорость работы радует. На TechNet есть подробная инфа по MERGE с наглядными и полещными примерами.
Ссылки:
MERGE (Transact-SQL)

Запись опубликована в рубрике Business Intelligence, TSQL с метками , . Добавьте в закладки постоянную ссылку.

One Response to MERGE vs. INSERT,UPDATE,DELETE

  1. Computer Hardware пишет:

    This is really fascinating, You’re an excessively skilled blogger.
    I’ve joined your feed and look ahead to in quest of more of your fantastic post.
    Additionally, I have shared your web site in my social networks

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *