Преимущества в указании HASH JOIN за то, что он просто делает JOIN?

Ответ 1

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

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

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

Лично я никогда не указывал подсказку JOIN в любом производственном коде.

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

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

Edit:

Сегодня у меня было преобразование, когда некоторые коллеги собираются использовать их для принудительного плохуго плана запросов (с NOLOCK и MAXDOP 1), чтобы "поощрять" миграцию от унаследованных сложных вложенных представлений, которые напрямую связаны с одним из их нижестоящих системных вызовов.

Ответ 2

Когда нужно попробовать хеш-подсказку, как насчет:

• После проверки наличия достаточных индексов по крайней мере на одном из таблицы.
• После попытки перенастройки запроса. Такие вещи, как преобразование присоединяется к "in" или "exists", меняя порядок соединения (что является действительно подсказка в любом случае), перемещение логики из условия where для соединения условия и т.д.

Некоторые основные правила о том, когда хеш-соединение эффективны, - это когда условие объединения не существует в качестве индекса таблицы и когда размеры таблиц различны. Если вы ищете техническое описание, есть некоторые хорошие описания о том, как работает хеш-соединение.

Зачем использовать любые подсказки соединения (хэш/слияние/цикл с побочным эффектом порядка силы)?

• Чтобы избежать крайне медленного выполнения (.5 → 10.0s) угловых случаев.
• Когда оптимизатор последовательно выбирает посредственный план.

Приведенные подсказки, скорее всего, не идеальны для некоторых обстоятельств, но обеспечивают более стабильно прогнозируемое время автономной работы. Ожидаемый худший случай и наилучшие сценарии должны быть предварительно проверены при использовании подсказки. Предсказуемое время автономной работы имеет решающее значение для веб-служб, где предпочтительным является оптимизированный номинальный [.3s,.6s] запрос, который может варьироваться от [.25, 10.0s]. Большие различия во времени выполнения могут произойти с обновленной статистикой и последующими передовыми методами.

При тестировании в среде разработки необходимо также отключить "обман", чтобы избежать отклонений в режиме "горячая/холодная". Из другого post...

CHECKPOINT -- flushes dirty pages to disk
DBCC DROPCLEANBUFFERS -- clears data cache
DBCC FREEPROCCACHE -- clears execution plan cache

Последняя опция может совпадать с подсказкой опции (перекомпилировать).

MAXDOP и загрузка машины также могут иметь огромное значение во время выполнения. Материализация CTE в временных таблицах также является хорошим механизмом блокировки и что-то для рассмотрения.

Ответ 3

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

Ответ 4

Единственный намек, который я когда-либо видел в транспортном коде, был OPTION (FORCE ORDER). Глупая ошибка в оптимизаторе запросов SQL создаст план, который попытался бы присоединиться к нефильтрованному varchar и уникальному идентификатору. Добавление FORCE ORDER заставило его сначала запустить фильтр.

Я знаю, что перегрузка столбцов плохая. Иногда вам нужно жить с ним.

Ответ 5

Оптимизатор логического плана не гарантирует вам, что он находит оптимальное решение: точный алгоритм слишком медленный для использования на производственном сервере; вместо этого используются некоторые жадные алгоритмы.

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