Каковы различия между преобразованиями Merge Join и Lookup в SSIS?

Привет. Я новичок в пакетах SSIS и одновременно пишу пакет и читаю о них.

Мне нужно преобразовать DTS в пакет SSIS, и мне нужно выполнить соединение в двух источниках из разных баз данных и задалось вопросом, что было лучше, чтобы использовать поиск или объединение слияния?

На поверхности они кажутся очень похожими. "Merge Join" требует, чтобы данные были отсортированы перед раздачей, тогда как "Lookup" не требует этого. Любой совет будет очень полезен. Спасибо.

Ответ 1

Снимок экрана # 1 показывает несколько точек для различения Merge Join transformation и Lookup transformation.

Что касается поиска:

Если вы хотите найти соответствие строк в источнике 2 на основе входа источника 1, и если вы знаете, что для каждой строки ввода будет только одно совпадение, я бы предложил использовать операцию поиска. Примером может служить таблица OrderDetails, и вы хотите найти соответствующие Order Id и Customer Number, тогда Lookup - лучший вариант.

Что касается объединения слиянием:

Если вы хотите выполнять объединения, такие как выбор всех Адресов (Главная, Работа, Другое) из таблицы Address для данного Клиента в таблице Customer, тогда вы должны пойти с Merge Join, потому что у клиента может быть 1 или более связанных с ними адресов.

Пример сравнения:

Вот сценарий, демонстрирующий различия в производительности между Merge Join и Lookup. Используемые здесь данные - это одно-единственное соединение, которое является единственным сценарием, общим для сравнения.

У меня есть три таблицы с именем dbo.ItemPriceInfo, dbo.ItemDiscountInfo и dbo.ItemAmount. Создание сценариев для этих таблиц предоставляется в разделе SQL-скриптов.
Таблицы dbo.ItemPriceInfo и dbo.ItemDiscountInfo имеют 13 349 729 строк. Обе таблицы имеют ItemNumber как общий столбец. ItemPriceInfo имеет информацию о ценах, а ItemDiscountInfo имеет информацию о скидках. Скриншот # 2 показывает количество строк в каждой из этих таблиц. Скриншот # 3 показывает верхние 6 строк, чтобы дать представление о данных, присутствующих в таблицах.
Я создал два пакета SSIS для сравнения производительности преобразований объединения и поиска. Оба пакета должны принимать информацию из таблиц dbo.ItemPriceInfo и dbo.ItemDiscountInfo, вычислять общую сумму и сохранять ее в таблице dbo.ItemAmount.
Первый пакет использовал преобразование Merge Join и внутри, что он использовал INNER JOIN для объединения данных. Скриншоты # 4 и # 5 показывают пример выполнения пакета и продолжительность выполнения. Требуется 05 минут 14 секунд 719 миллисекунд, чтобы выполнить пакет слияния с объединением объединения.
Второй пакет использовал преобразование Lookup с полным кешем (который является настройкой по умолчанию). creenshots # 6 и # 7 показывают пример выполнения пакета и продолжительность выполнения. Для выполнения пакета преобразования, основанного на поиске, потребовалось 11 минут 03 секунд 610 миллисекунд. Вы можете столкнуться с предупреждающим сообщением. Информация: The buffer manager has allocated nnnnn bytes, even though the memory pressure has been detected and repeated attempts to swap buffers have failed. Ниже приведена ссылка, в которой рассказывается о том, как вычислять кеш поиска размер. Во время выполнения этого пакета, хотя задача потока данных выполнялась быстрее, очистка трубопровода занимала много времени.
Это значение не означает. Просто это нужно использовать мудро. Я использую это довольно часто в своих проектах, но опять же я не занимаюсь поиском 10+ миллионов строк для ежедневного поиска. Обычно мои задания занимают от 2 до 3 миллионов строк, и для этого производительность действительно хороша. До 10 миллионов строк, оба выполнялись одинаково хорошо. Большую часть времени я заметил, что узкое место оказывается компонентом назначения, а не преобразованиями. Вы можете преодолеть это, имея несколько пунктов назначения. Здесь - пример, показывающий реализацию нескольких пунктов назначения.
Снимок экрана # 8 показывает количество записей во всех трех таблицах. Скриншот # 9 показывает первые 6 записей в каждой из таблиц.

Надеюсь, что это поможет.

Сценарии SQL:

CREATE TABLE [dbo].[ItemAmount](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
    [Discount] [numeric](18, 2) NOT NULL,
    [CalculatedAmount] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemAmount] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemDiscountInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Discount] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemDiscountInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemPriceInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemPriceInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Снимок экрана №1:

Снимок экрана №2:

Снимок экрана №3: 

Снимок экрана №4:

Снимок экрана № 5:

Снимок экрана № 6:

Снимок экрана № 7:

Снимок экрана # 8:

Снимок экрана № 9:

Ответ 2

Merge Join предназначен для получения результатов, аналогичных тому, как JOINs работают в SQL. Компонент Lookup не работает как SQL JOIN. Вот пример, где результаты будут отличаться.

Если у вас есть соотношение "один ко многим" между входом 1 (например, счет-фактурами) и входом 2 (например, строки счета), вы хотите, чтобы результаты объединения этих двух входов включали одну или несколько строк для один счет-фактура.

При объединении слияния вы получите желаемый результат. С Lookup, где вход 2 является источником поиска, выход будет одной строкой на счет-фактуру, независимо от того, сколько строк существует во вводе 2. Я не помню, какая строка из ввода 2 будет иметь данные, m наверняка вы получите предупреждение о дублировании данных, по крайней мере.

Итак, каждый компонент имеет свою собственную роль в SSIS.

Ответ 3

Я предложу третий вариант рассмотрения. Ваш OLE DBSource может содержать запрос, а не таблицу, и вы можете присоединиться к нему. Это не хорошо во всех ситуациях, но когда вы можете использовать его, вам не нужно сортировать заранее.

Ответ 4

Поиск похож на левое соединение в компоненте Merge Join. Слияние может выполнять другие типы соединений, но если это то, что вы хотите, разница в основном состоит в производительности и удобстве.

Их характеристики производительности могут быть очень разными в зависимости от относительного объема данных для поиска (вход для компонента поиска) и количества ссылочных данных (кеш файл поиска или размер источника данных поиска).

например. если вам нужно искать только 10 строк, но набор данных, на который ссылается, составляет 10 миллионов строк. Поиск с использованием режима частичного кеша или без кеша будет быстрее, поскольку он будет извлекать только 10 записей, а не 10 миллионов. Если вам нужно искать 10 миллионов строк, а ссылочный набор данных - 10 строк - полностью кэшированный Lookup, вероятно, быстрее (если эти 10 миллионов строк уже отсортированы в любом случае, и вы можете попробовать Merge Join). Если оба набора данных большие (особенно если больше, чем доступная оперативная память) или большая сортировка сортируются - возможно, лучше выбрать Merge.

Ответ 5

Merge Join позволяет вам присоединяться к нескольким столбцам на основе одного или нескольких критериев, тогда как Lookup более ограничен тем, что он извлекает только одно или несколько значений на основе некоторой соответствующей информации о столбцах - запрос поиска будет для каждого значения в вашем источнике данных (хотя SSIS будет кэшировать источник данных, если это возможно).

Это действительно зависит от того, что содержат ваши два источника данных и как вы хотите, чтобы ваш конечный источник выглядел после слияния. Не могли бы вы предоставить более подробную информацию о схемах в вашем пакете DTS?

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

Ответ 6

существует 2 отличия:

Сортировка:
- объединение слияния требует, чтобы оба входа сортировались одинаково.
- поиск не требует сортировки ввода.
Загрузка запроса базы данных:
- объединение слияния не относится к базе данных, всего 2 входных потока (хотя ссылочные данные обычно имеют форму "select * from table order by join critera" )
- lookup выдаст 1 запрос для каждого (отличного, если кэшированного) значения, к которому его просят присоединиться. Это быстро становится дороже, чем выбранный выше.

Это приводит к: если нет усилий для создания отсортированного списка, и вам нужно больше, чем около 1% строк (выбор одной строки составляет ~ 100x стоимость одной и той же строки при потоковой передаче) (вы не хотите сортировать 10 миллионов строк таблица в памяти..), тогда объединение слияния - это путь.

Если вы ожидаете только небольшого количества совпадений (различные значения выглядели вверх, когда кеширование включено), то поиск лучше.

Для меня, компромисс между ними приходит между 10k и 100k строк, которые нужно искать.

Тот, который быстрее будет зависеть от

общее количество строк для обработки. (если таблица является резидентной, то некоторые данные для ее слияния дешевы)
ожидается количество повторяющихся запросов. (высокая накладная строка для поиска)
если вы можете выбрать отсортированные данные (обратите внимание, что текстовые сортировки влияют на сортировку кода, поэтому будьте осторожны, что то, что рассмотрено в sql, также является тем, что ssis считает отсортированным)
какой процент всей таблицы вы будете искать. (слияние потребует выбора каждой строки, поиск лучше, если у вас есть только несколько строк на одной стороне)
ширина строки (строки на странице могут сильно влиять на затраты на просмотр одиночного поиска и на сканирование) (узкие строки → больше предпочтений для слияния)
порядок данных на диске (легко произвести сортированный вывод, предпочитайте слияние, если вы можете упорядочить поиск, который будет выполняться в порядке физического диска, поиск менее затратный из-за меньшего количества промахов в кеше)
латентность сети между сервером SIS и пунктом назначения (большая латентность → предпочитают слияние)
сколько усилий по кодированию вы хотите потратить (слияние немного сложнее для записи)
сопоставление входных данных - слияние SSIS имеет более простые представления о сортировке текстовых строк, которые содержат не буквенно-цифровые символы, но не являются nvarchar. (это относится к сортировке и получению sql, чтобы испускать сортировку, с которой ssis с удовольствием сливается.)

Ответ 7

Я знаю, что это старый вопрос, но один критический момент, который, как я чувствую, не был охвачен ответами, заключается в том, что, поскольку объединение объединения объединяет два потока данных, оно может объединять данные из любого источника. В то время как при поиске один источник данных должен храниться в OLE DB.