Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MERGE vs Triggered VIEW  [new]
ПалЪ СанычЪ
Member

Откуда:
Сообщений: 37
Доброго времени суток, коллеги.

Select @@version: 
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
Aug 13 2014 11:36:34 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)


Возник вопрос производительности для творчества идуских авторов для SSIS ETL пакета.

Ситуация:

Идет каждодневная Bulk загрузка в стейдж таблицы из csv. Дельты нет, т.е данные каждый раз все.(Процент изменений предположим <=10%.)
Дальше соответственно идет сравнение стэйджа(около 6 миллионов) и таргета (новые добавлять, существующие обновлять, удаления нет поэтому таблица около 80 миллионов).

Реализовано это следующим образом внутри пакета:
Lookup tasl и если картеж по условию (уникальный набор 3 атрибута) совпадает - идет попытка вставки в "fake" View( с INSTEAD OF Insert trigger'ом ).
Триггер соотвентсвенно делает Update в нужной таблице Target.

Тело триггера:

CREATE TRIGGER dbo.trigger_name
ON dbo.View_name
INSTEAD OF INSERT
AS 

BEGIN
SET NOCOUNT ON;

UPDATE t
SET 
t.col1 = tt.col1,
t.col2= tt.col2,
t.col3= tt.col3,
t.col4= tt.col4,
t.col5= tt.col5
.......
FROM dbo.target_table t 
INNER JOINinserted tt 

ON t.predicateCol1 = tt.predicateCol1
AND t.predicateCol2 = tt.predicateCol2
AND t.predicateCol3 = tt.predicateCol3

END

Пример "fake" View на котором висит триггер:

CREATE VIEW dbo.View_name
AS
SELECT TOP (1) *
FROM dbo.target_table

Я настаиваю на изменении архитектуры в пользу классического MERGE statement+ Partitioning. Но убеждений с обратной стороны (без доказательных и слабо оргумениированных) просто куча.Говорят 100 раз проверили - MERGE хуже.
В связи с чем у меня возник вопрос:существуют ли реальные аргументирванныe плюсы в пользу архитектуры (Lookup +View + trigger instead of insert при обновлении данных) для улучшения производительности?

Заранее спасибо за аргументацию и размышления по теме.
6 мар 18, 19:57    [21241620]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
ПалЪ СанычЪ
Доброго времени суток, коллеги.
(...)
Заранее спасибо за аргументацию и размышления по теме.

«Вы спрашиваете: какой уклон хуже? Нельзя так ставить вопрос. Оба они хуже, и первый, и второй уклоны» (И.В.Сталин).
Вам нужно подумать о том, чтобы вытащить реальную дельту вместо многократного перемалывания одних и тех же данных.
6 мар 18, 20:12    [21241657]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

Откуда:
Сообщений: 7730
ПалЪ СанычЪ
Доброго времени суток, коллеги.

Select @@version: 
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
Aug 13 2014 11:36:34 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)



Возник вопрос производительности для творчества идуских авторов для SSIS ETL пакета.

Ситуация:

Идет каждодневная Bulk загрузка в стейдж таблицы из csv. Дельты нет, т.е данные каждый раз все.(Процент изменений предположим <=10%.)
Дальше соответственно идет сравнение стэйджа(около 6 миллионов) и таргета (новые добавлять, существующие обновлять, удаления нет поэтому таблица около 80 миллионов).

Реализовано это следующим образом внутри пакета:
Lookup tasl и если картеж по условию (уникальный набор 3 атрибута) совпадает - идет попытка вставки в "fake" View( с INSTEAD OF Insert trigger'ом ).
Триггер соотвентсвенно делает Update в нужной таблице Target.

Тело триггера:

CREATE TRIGGER dbo.trigger_name
ON dbo.View_name
INSTEAD OF INSERT
AS 

BEGIN
SET NOCOUNT ON;

UPDATE t
SET 
t.col1 = tt.col1,
t.col2= tt.col2,
t.col3= tt.col3,
t.col4= tt.col4,
t.col5= tt.col5
.......
FROM dbo.target_table t 
INNER JOINinserted tt 

ON t.predicateCol1 = tt.predicateCol1
AND t.predicateCol2 = tt.predicateCol2
AND t.predicateCol3 = tt.predicateCol3

END


Пример "fake" View на котором висит триггер:

CREATE VIEW dbo.View_name
AS
SELECT TOP (1) *
FROM dbo.target_table


Я настаиваю на изменении архитектуры в пользу классического MERGE statement+ Partitioning. Но убеждений с обратной стороны (без доказательных и слабо оргумениированных) просто куча.Говорят 100 раз проверили - MERGE хуже.
В связи с чем у меня возник вопрос:существуют ли реальные аргументирванныe плюсы в пользу архитектуры (Lookup +View + trigger instead of insert при обновлении данных) для улучшения производительности?

Заранее спасибо за аргументацию и размышления по теме.

Наш опыт (давнишний) перехода с ssis lookup + insert/update на ssis bulk + merge c insert/update дал прирост производительности в разы (если не на порядок).

Провести "натурный" эксперимент, вроде, достаточно просто.
6 мар 18, 20:14    [21241665]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

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

Если источником данных является внешняя система, на которую нет влияния, либо изменения в ней под нужды "дельты" невозможны , то "вытащить реальную дельту" может быть очень и очень затруднительно.
6 мар 18, 20:17    [21241672]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
msLex
Если источником данных является внешняя система, на которую нет влияния, либо изменения в ней под нужды "дельты" невозможны , то "вытащить реальную дельту" может быть очень и очень затруднительно.

Не очень затруднительно. Например, в SSIS джойнятся два потока - старых и новых данных, где и определяется дельта. С некоторыми ухищрениями на одной из сторон вполне реально сравнивать миллионы строк в секунду даже на весьма посредственных серверах.
6 мар 18, 20:23    [21241695]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

Откуда:
Сообщений: 7730
.Евгений
msLex
Если источником данных является внешняя система, на которую нет влияния, либо изменения в ней под нужды "дельты" невозможны , то "вытащить реальную дельту" может быть очень и очень затруднительно.

Не очень затруднительно. Например, в SSIS джойнятся два потока - старых и новых данных, где и определяется дельта. С некоторыми ухищрениями на одной из сторон вполне реально сравнивать миллионы строк в секунду даже на весьма посредственных серверах.

Смысл в такой дельте, если для ее формирования нужны все текущие данные с потребителя? Чем это лучше bulk + merge на потребители данных?
6 мар 18, 20:26    [21241707]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
ПалЪ СанычЪ
Member

Откуда:
Сообщений: 37
msLex,

Спасибо за ответ.
Дело в том что в код заглянул -не спал две ночи. Там ужас... Переделывать весь код и архитектуру -нет времени, да и зачем тогда ребята из солнечной страны свой хлеб едят? Переписал кое-что с минимальными затратами пиная и тыкая носом вышеуказанных. (одна проца с 5 часов до 2 мин улучшилось, но вот остались еще вопросы по обновлению данных). Мне главное в регламент уложиться сейчас малой кровью.
Простым стендом боюсь их не врозумить, но наверное прийдется делать.

Еще раз спасибо за фидбэк.
6 мар 18, 20:30    [21241719]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
msLex
Смысл в такой дельте, если для ее формирования нужны все текущие данные с потребителя? Чем это лучше bulk + merge на потребители данных?

Во-первых, не все. Точнее, строки-то все (в худшем случае), а вот столбцы - нет.
Во-вторых, отсутствие блокировок (select + возможный nolock).
6 мар 18, 20:31    [21241723]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
ПалЪ СанычЪ
Member

Откуда:
Сообщений: 37
.Евгений,

Спасибо за ответ.
Дельта не обсуждается. Предположим( это уже она.)

@msLex в данном случае прав. В некоторых ситуация это фактически невозможно по политическим причинам.Так что вопрос поставлен так.
6 мар 18, 20:34    [21241735]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

Откуда:
Сообщений: 7730
.Евгений
Во-первых, не все. Точнее, строки-то все (в худшем случае)

что это за лучший случай, который можно релизовать в SSIS мерж но нельзя в SQL?



.Евгений
а вот столбцы - нет.

А тут чем чтение данных для SSIS мержа отличается от SQL

.Евгений
Во-вторых, отсутствие блокировок (select + возможный nolock).

забудем, что на дворе 2018 и 10 лет как появился RCSI

чтение данных для SSIS с nolock подразумевает изменение данных только дельте, выданной ssis пакетом.
Так чем же S блокировки (которые будут жить даже не до конца стейтмента), будут мешать остальным читателям?
6 мар 18, 20:39    [21241757]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
ПалЪ СанычЪ
.Евгений,

Спасибо за ответ.
Дельта не обсуждается. Предположим( это уже она.)

@msLex в данном случае прав. В некоторых ситуация это фактически невозможно по политическим причинам.Так что вопрос поставлен так.

Каков критический ресурс, потребление которого вы хотите оптимизировать: время блокировок, общее время загрузки?
6 мар 18, 20:41    [21241763]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
msLex
.Евгений
Во-первых, не все. Точнее, строки-то все (в худшем случае)

что это за лучший случай, который можно релизовать в SSIS мерж но нельзя в SQL?
.Евгений
а вот столбцы - нет.

А тут чем чтение данных для SSIS мержа отличается от SQL
.Евгений
Во-вторых, отсутствие блокировок (select + возможный nolock).

забудем, что на дворе 2018 и 10 лет как появился RCSI

чтение данных для SSIS с nolock подразумевает изменение данных только дельте, выданной ssis пакетом.
Так чем же S блокировки (которые будут жить даже не до конца стейтмента), будут мешать остальным читателям?

Я не понял ваши вопросы. Вас интересует, чем обработка данных на стороне ETL лучше, чем на стороне БД?
В первую очередь тем, что позволяет кардинально сократить количество блокировок. SQL merge может надолго забрать всю таблицу себе, в то время как грамотное вычисление дельты позволяет блокировать сравнительно малый участок таблицы.
Во-вторых, это независимость от расположения слоев на различных серверах (что, в общем-то, норма жизни).
6 мар 18, 21:03    [21241813]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
ПалЪ СанычЪ,

По сути то что у вас там реализовано это аналог мёрджа.
Делает инсерт новых записей (скорее всего балком)
И потоковую "вставку" во вьюху
И если эта вставка так же делается балком
и при этом размер пакета большой
то поверить в слова, что тестили и так лучше вполне можно.
6 мар 18, 21:03    [21241815]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
ПалЪ СанычЪ
Member

Откуда:
Сообщений: 37
.Евгений,

Интересует исключительно время. Job'ы выполнятся исключительно ночью в соответствии с регионом.Пользователей/отчетов в это время быть не должно
6 мар 18, 21:54    [21241912]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
ПалЪ СанычЪ
Member

Откуда:
Сообщений: 37
Дедушка,
Спасибо за ответ.
Как раз, по-моему, пакеты небольшие. Надо будет проверить, как и поточную вставку(в сам пакет пока не смотрел)
В общем доверюсь, но на всякий случай проверюсь. Прийдется сделать пару стендов.
Спасибо
6 мар 18, 21:59    [21241927]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

Откуда:
Сообщений: 7730
.Евгений
Я не понял ваши вопросы. Вас интересует, чем обработка данных на стороне ETL лучше, чем на стороне БД?


Если кратко, меня интересует каким образом получение диффа полным сравнением двух наборов на стороне SSIS может оказать лучше чем на стороне SQL Server-а, являющегося одним из источников этих наборов, с учетом того, что в SSIS нет и половины того, что умеет делать SQLServer "сам" при join-е двух НД.
7 мар 18, 11:27    [21242751]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
msLex,

вопрос отчасти холиварный...
всё очень зависит от контекста.

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

в ссис вы предварительно всасываете этот хеш в память и далее лукапом в памяти быстро по нему проходите.
при этом нет необходимости (крайние случаи не рассматриваем) всасывать внешние данные в память целиком они "едут" буферами.

вообще, при подходе ELT вы теряете балк загрузку на этапе стейдж-ХД,
но как я и написал выше важен конкретный контекст.
7 мар 18, 13:39    [21243170]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
msLex
Если кратко, меня интересует каким образом получение диффа полным сравнением двух наборов на стороне SSIS может оказать лучше чем на стороне SQL Server-а, являющегося одним из источников этих наборов, с учетом того, что в SSIS нет и половины того, что умеет делать SQLServer "сам" при join-е двух НД.
Вот сейчас придет a_voronin и все вам растолкует
7 мар 18, 14:17    [21243326]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

Откуда:
Сообщений: 7730
Дедушка
например:
в таблице у вас заранее посчитан hash (лежит отдельным полем)
загрузив внешние данные в стейдж для получения диффа вы будете сравнивать хэши
на холодном кеше (ну в общем случае). можно конечно предварительно прогревать,
но тут вопрос ресурсов (на сервере БД как правило лишнего нет).

в ссис вы предварительно всасываете этот хеш в память и далее лукапом в памяти быстро по нему проходите.
при этом нет необходимости (крайние случаи не рассматриваем) всасывать внешние данные в память целиком они "едут" буферами.


Это называется hash join и это одна из тех вещей, которые SQLServer умеет делать сам.

Дедушка
вообще, при подходе ELT вы теряете балк загрузку на этапе стейдж-ХД,

Честно, не понял
1. где и как при переходе с ETL на ELT происходит потеря балка.
2. как это соотносится со сбором diff-а между 2-мя НД.
7 мар 18, 14:30    [21243370]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
msLex
.Евгений
Я не понял ваши вопросы. Вас интересует, чем обработка данных на стороне ETL лучше, чем на стороне БД?


Если кратко, меня интересует каким образом получение диффа полным сравнением двух наборов на стороне SSIS может оказать лучше чем на стороне SQL Server-а, являющегося одним из источников этих наборов, с учетом того, что в SSIS нет и половины того, что умеет делать SQLServer "сам" при join-е двух НД.

Еще раз.
Во-первых, основная выгода возникает не на этапе чтения, а на этапе модификации.
Во-вторых, выделенный сервер, заточенный под обработку потока данных. Обратите внимание, что серверу SSIS не нужно поддерживать транзакционность (в т.ч. лог, блокировки и др).
7 мар 18, 18:10    [21243860]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
msLex
Member

Откуда:
Сообщений: 7730
.Евгений
Во-первых, основная выгода возникает не на этапе чтения, а на этапе модификации.

как ?
Что такого с модификацией данных умеет делать SSIS чего не умеет SQLServer?
7 мар 18, 18:23    [21243891]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
msLex
Что такого с модификацией данных умеет делать SSIS чего не умеет SQLServer?
вам уже несколько раз ответили.
дело не в том, что ссис умеет что-то чего не умеет сиквел вопрос в том как это происходит.
что касается темы ETL на процедурах или ETL на фреймворке обсуждали уже 100500 раз
7 мар 18, 18:53    [21243927]     Ответить | Цитировать Сообщить модератору
 Re: MERGE vs Triggered VIEW  [new]
.Евгений
Member

Откуда:
Сообщений: 493
msLex
.Евгений
Во-первых, основная выгода возникает не на этапе чтения, а на этапе модификации.

как ?
Что такого с модификацией данных умеет делать SSIS чего не умеет SQLServer?

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

Эта процедура, на мой взгляд, даст вам наглядный и педагогичный ответ.
Если для вас это затруднительно или неинтересно, просто запомните: если большой объем данных можно обработать потоком (словно курсором), то SSIS может оказаться более подходящим исполнителем.
7 мар 18, 19:42    [21243989]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить