Loop Join в SQL Server 2008

Question

Я не ясно о рабочей разнице между запросами, упомянутыми ниже.

Конкретно мне неясно концепция OPTION(LOOP JOIN),

1-й подход: это традиционное соединение, которое стоит дороже, чем все ниже.

SELECT * 
FROM [Item Detail] a
LEFT JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (FORCE ORDER);

2-й подход: включает OPTION в утверждении с отсортированными данными, просто оптимизирован.

SELECT * 
FROM [Item Detail] a
LEFT LOOP JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (FORCE ORDER);

3-й подход: здесь, мне не ясно, как работает запрос и включает OPTION с loop join!!?

SELECT * 
FROM [Item Detail] a
LEFT LOOP JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (LOOP JOIN);

Кто-нибудь может объяснить разницу и способ работы и преимущества каждого из них перед другими?

Примечание: это не вложенные ИЛИ хеш-циклы!

Answer 1

Из подсказок запросов (Transact-SQL)

FORCE ORDER Указывает, что порядок соединения, указанный в синтаксисе запроса, сохраняется при оптимизации запроса. Использование FORCE ORDER не влияет на возможное поведение перестановки ролей оптимизатора запросов.

также

{ LOOP | Мердж | HASH } JOIN Указывает, что все операции соединения выполняются командами LOOP JOIN, MERGE JOIN или HASH JOIN во всем запросе. Если указано несколько подсказок о соединении, оптимизатор выбирает наименее дорогую стратегию объединения из разрешенных.

Расширенные концепции настройки запросов

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

Если два входа объединения не малы, но отсортированы по столбцу соединения (например, если они были получены путем сканирования отсортированных индексов), объединение слиянием является самой быстрой операцией соединения.

Хеш-объединения могут эффективно обрабатывать большие, несортированные, неиндексированные входные данные.

И советы по соединению (Transact-SQL)

Советы по соединению указывают, что оптимизатор запросов применяет стратегию соединения между двумя таблицами

Ваш вариант 1 говорит оптимизатору сохранить порядок соединения как есть. Итак JOIN Тип может быть определен оптимизатором, поэтому может быть MERGE JOIN,

Ваш вариант 2 говорит оптимизатору использовать LOOP JOIN для этого конкретного JOIN, Если бы были какие-либо другие объединения в FROM раздел, оптимизатор сможет решить за них. Кроме того, вы указываете порядок соединений, принимаемых для оптимизатора.

Ваш последний вариант OPTION (LOOP JOIN) будет обеспечивать LOOP JOIN через все объединения в запросе.

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