ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / MERGE vs Triggered VIEW
23 сообщений из 23, страница 1 из 1
MERGE vs Triggered VIEW
    #39611466
ПалЪ СанычЪ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПалЪ СанычЪ
Гость
Доброго времени суток, коллеги.

Код: sql
1.
2.
3.
4.
5.
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.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
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 на котором висит триггер:

Код: sql
1.
2.
3.
4.
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 при обновлении данных) для улучшения производительности?

Заранее спасибо за аргументацию и размышления по теме.
...
Рейтинг: 0 / 0
06.03.2018, 19:57
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611470
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
ПалЪ СанычЪДоброго времени суток, коллеги.
(...)
Заранее спасибо за аргументацию и размышления по теме.
«Вы спрашиваете: какой уклон хуже? Нельзя так ставить вопрос. Оба они хуже, и первый, и второй уклоны» (И.В.Сталин).
Вам нужно подумать о том, чтобы вытащить реальную дельту вместо многократного перемалывания одних и тех же данных.
...
Рейтинг: 0 / 0
06.03.2018, 20:12
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611471
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
ПалЪ СанычЪДоброго времени суток, коллеги.

Код: sql
1.
2.
3.
4.
5.
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.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
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 на котором висит триггер:

Код: sql
1.
2.
3.
4.
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 дал прирост производительности в разы (если не на порядок).

Провести "натурный" эксперимент, вроде, достаточно просто.
...
Рейтинг: 0 / 0
06.03.2018, 20:14
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611474
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
.ЕвгенийВам нужно подумать о том, чтобы вытащить реальную дельту вместо многократного перемалывания одних и тех же данных.
Если источником данных является внешняя система, на которую нет влияния, либо изменения в ней под нужды "дельты" невозможны , то "вытащить реальную дельту" может быть очень и очень затруднительно.
...
Рейтинг: 0 / 0
06.03.2018, 20:17
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611480
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
msLexЕсли источником данных является внешняя система, на которую нет влияния, либо изменения в ней под нужды "дельты" невозможны , то "вытащить реальную дельту" может быть очень и очень затруднительно.
Не очень затруднительно. Например, в SSIS джойнятся два потока - старых и новых данных, где и определяется дельта. С некоторыми ухищрениями на одной из сторон вполне реально сравнивать миллионы строк в секунду даже на весьма посредственных серверах.
...
Рейтинг: 0 / 0
06.03.2018, 20:23
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611484
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
.ЕвгенийmsLexЕсли источником данных является внешняя система, на которую нет влияния, либо изменения в ней под нужды "дельты" невозможны , то "вытащить реальную дельту" может быть очень и очень затруднительно.
Не очень затруднительно. Например, в SSIS джойнятся два потока - старых и новых данных, где и определяется дельта. С некоторыми ухищрениями на одной из сторон вполне реально сравнивать миллионы строк в секунду даже на весьма посредственных серверах.
Смысл в такой дельте, если для ее формирования нужны все текущие данные с потребителя? Чем это лучше bulk + merge на потребители данных?
...
Рейтинг: 0 / 0
06.03.2018, 20:26
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611486
ПалЪ СанычЪ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПалЪ СанычЪ
Гость
msLex,

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

Еще раз спасибо за фидбэк.
...
Рейтинг: 0 / 0
06.03.2018, 20:30
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611487
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
msLexСмысл в такой дельте, если для ее формирования нужны все текущие данные с потребителя? Чем это лучше bulk + merge на потребители данных?
Во-первых, не все . Точнее, строки-то все (в худшем случае), а вот столбцы - нет.
Во-вторых, отсутствие блокировок (select + возможный nolock).
...
Рейтинг: 0 / 0
06.03.2018, 20:31
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611489
ПалЪ СанычЪ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПалЪ СанычЪ
Гость
.Евгений,

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

@msLex в данном случае прав. В некоторых ситуация это фактически невозможно по политическим причинам.Так что вопрос поставлен так.
...
Рейтинг: 0 / 0
06.03.2018, 20:34
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611493
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
.ЕвгенийВо-первых, не все . Точнее, строки-то все ( в худшем случае )
что это за лучший случай, который можно релизовать в SSIS мерж но нельзя в SQL?



.Евгенийа вот столбцы - нет.
А тут чем чтение данных для SSIS мержа отличается от SQL

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

чтение данных для SSIS с nolock подразумевает изменение данных только дельте, выданной ssis пакетом.
Так чем же S блокировки (которые будут жить даже не до конца стейтмента), будут мешать остальным читателям?
...
Рейтинг: 0 / 0
06.03.2018, 20:39
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611494
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
ПалЪ СанычЪ.Евгений,

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

@msLex в данном случае прав. В некоторых ситуация это фактически невозможно по политическим причинам.Так что вопрос поставлен так.
Каков критический ресурс, потребление которого вы хотите оптимизировать: время блокировок, общее время загрузки?
...
Рейтинг: 0 / 0
06.03.2018, 20:41
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611506
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
msLex.ЕвгенийВо-первых, не все . Точнее, строки-то все ( в худшем случае )
что это за лучший случай, который можно релизовать в SSIS мерж но нельзя в SQL?
.Евгенийа вот столбцы - нет.
А тут чем чтение данных для SSIS мержа отличается от SQL
.ЕвгенийВо-вторых, отсутствие блокировок (select + возможный nolock).
забудем, что на дворе 2018 и 10 лет как появился RCSI

чтение данных для SSIS с nolock подразумевает изменение данных только дельте, выданной ssis пакетом.
Так чем же S блокировки (которые будут жить даже не до конца стейтмента), будут мешать остальным читателям?
Я не понял ваши вопросы. Вас интересует, чем обработка данных на стороне ETL лучше, чем на стороне БД?
В первую очередь тем, что позволяет кардинально сократить количество блокировок. SQL merge может надолго забрать всю таблицу себе, в то время как грамотное вычисление дельты позволяет блокировать сравнительно малый участок таблицы.
Во-вторых, это независимость от расположения слоев на различных серверах (что, в общем-то, норма жизни).
...
Рейтинг: 0 / 0
06.03.2018, 21:03
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611508
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
ПалЪ СанычЪ,

По сути то что у вас там реализовано это аналог мёрджа.
Делает инсерт новых записей (скорее всего балком)
И потоковую "вставку" во вьюху
И если эта вставка так же делается балком
и при этом размер пакета большой
то поверить в слова, что тестили и так лучше вполне можно.
...
Рейтинг: 0 / 0
06.03.2018, 21:03
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611526
ПалЪ СанычЪ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПалЪ СанычЪ
Гость
.Евгений,

Интересует исключительно время. Job'ы выполнятся исключительно ночью в соответствии с регионом.Пользователей/отчетов в это время быть не должно
...
Рейтинг: 0 / 0
06.03.2018, 21:54
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611531
ПалЪ СанычЪ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ПалЪ СанычЪ
Гость
Дедушка,
Спасибо за ответ.
Как раз, по-моему, пакеты небольшие. Надо будет проверить, как и поточную вставку(в сам пакет пока не смотрел)
В общем доверюсь, но на всякий случай проверюсь. Прийдется сделать пару стендов.
Спасибо
...
Рейтинг: 0 / 0
06.03.2018, 21:59
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611720
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
.ЕвгенийЯ не понял ваши вопросы. Вас интересует, чем обработка данных на стороне ETL лучше, чем на стороне БД?

Если кратко, меня интересует каким образом получение диффа полным сравнением двух наборов на стороне SSIS может оказать лучше чем на стороне SQL Server-а, являющегося одним из источников этих наборов, с учетом того, что в SSIS нет и половины того, что умеет делать SQLServer "сам" при join-е двух НД.
...
Рейтинг: 0 / 0
07.03.2018, 11:27
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611804
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
msLex,

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

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

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

вообще, при подходе ELT вы теряете балк загрузку на этапе стейдж-ХД,
но как я и написал выше важен конкретный контекст.
...
Рейтинг: 0 / 0
07.03.2018, 13:39
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611845
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
msLexЕсли кратко, меня интересует каким образом получение диффа полным сравнением двух наборов на стороне SSIS может оказать лучше чем на стороне SQL Server-а, являющегося одним из источников этих наборов, с учетом того, что в SSIS нет и половины того, что умеет делать SQLServer "сам" при join-е двух НД.Вот сейчас придет a_voronin и все вам растолкует
...
Рейтинг: 0 / 0
07.03.2018, 14:17
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611852
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Дедушканапример:
в таблице у вас заранее посчитан hash (лежит отдельным полем)
загрузив внешние данные в стейдж для получения диффа вы будете сравнивать хэши
на холодном кеше (ну в общем случае). можно конечно предварительно прогревать,
но тут вопрос ресурсов (на сервере БД как правило лишнего нет).

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

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

Дедушкавообще, при подходе ELT вы теряете балк загрузку на этапе стейдж-ХД,
Честно, не понял
1. где и как при переходе с ETL на ELT происходит потеря балка.
2. как это соотносится со сбором diff-а между 2-мя НД.
...
Рейтинг: 0 / 0
07.03.2018, 14:30
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39611997
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
msLex.ЕвгенийЯ не понял ваши вопросы. Вас интересует, чем обработка данных на стороне ETL лучше, чем на стороне БД?

Если кратко, меня интересует каким образом получение диффа полным сравнением двух наборов на стороне SSIS может оказать лучше чем на стороне SQL Server-а, являющегося одним из источников этих наборов, с учетом того, что в SSIS нет и половины того, что умеет делать SQLServer "сам" при join-е двух НД.
Еще раз.
Во-первых, основная выгода возникает не на этапе чтения, а на этапе модификации.
Во-вторых, выделенный сервер, заточенный под обработку потока данных. Обратите внимание, что серверу SSIS не нужно поддерживать транзакционность (в т.ч. лог, блокировки и др).
...
Рейтинг: 0 / 0
07.03.2018, 18:10
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39612012
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
.ЕвгенийВо-первых, основная выгода возникает не на этапе чтения, а на этапе модификации.
как ?
Что такого с модификацией данных умеет делать SSIS чего не умеет SQLServer?
...
Рейтинг: 0 / 0
07.03.2018, 18:23
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39612024
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
msLexЧто такого с модификацией данных умеет делать SSIS чего не умеет SQLServer?вам уже несколько раз ответили.
дело не в том, что ссис умеет что-то чего не умеет сиквел вопрос в том как это происходит.
что касается темы ETL на процедурах или ETL на фреймворке обсуждали уже 100500 раз
...
Рейтинг: 0 / 0
07.03.2018, 18:53
| Ответить | Цитировать | Написать  
MERGE vs Triggered VIEW
    #39612039
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
msLex.ЕвгенийВо-первых, основная выгода возникает не на этапе чтения, а на этапе модификации.
как ?
Что такого с модификацией данных умеет делать SSIS чего не умеет SQLServer?
Я предлагаю поступить так:
Во-первых, вы сочините мердж SQL, аналогичный описанному. Откроете его план, изучите тему (при необходимости) и напишете, какие ресурсозначимые действия, по-вашему, производил SQL сервер.
Во-вторых, посетители форума внесут свои коррективы в ваш список.
В-третьих, мы скажем вам, каких действий или ресурсов не требуется SSIS и, соответственно, почему он может работать быстрее.

Эта процедура, на мой взгляд, даст вам наглядный и педагогичный ответ.
Если для вас это затруднительно или неинтересно, просто запомните: если большой объем данных можно обработать потоком (словно курсором), то SSIS может оказаться более подходящим исполнителем.
...
Рейтинг: 0 / 0
07.03.2018, 19:42
| Ответить | Цитировать | Написать  
23 сообщений из 23, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / MERGE vs Triggered VIEW
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]