Преимущества в определении HASH JOIN по сравнению с просто выполнением JOIN?
Каковы преимущества явного выполнения HASH JOIN по сравнению с обычным JOIN (в котором SQL Server определит наилучшую стратегию JOIN)? Например:
select pd.*
from profiledata pd
inner hash join profiledatavalue val on val.profiledataid=pd.id
В приведенном выше упрощенном примере кода я указываю стратегию JOIN, в то время как если я опущу ключевое слово "хэш", SQL Server выполнит MERGE JOIN за кулисами (согласно "фактическому плану выполнения").
5 ответов
Optmiser делает достаточно хорошую работу для повседневного использования. Однако в теории может потребоваться 3 недели, чтобы найти идеальный план в крайнем случае, поэтому есть вероятность, что сгенерированный план не будет идеальным.
Я бы оставил это в покое, если у вас нет очень сложного запроса или огромных объемов данных, которые просто не могут дать хороший план. Тогда я бы обдумал это.
Но со временем, когда данные изменяются / растут или изменяются индексы и т. Д., Ваша подсказка JOIN становится устаревшей и мешает оптимальному плану. Подсказка JOIN может оптимизировать только этот запрос во время разработки с тем набором данных, который у вас есть.
Лично я никогда не указывал подсказку JOIN ни в одном рабочем коде.
Обычно я решал плохое объединение, меняя свой запрос, добавляя / изменяя индекс или разбивая его (например, сначала загрузите временную таблицу). Либо мой запрос был неверным, либо у меня было неявное преобразование типов данных, либо это выявило недостаток в моей схеме и т. Д.
Я видел, как другие разработчики используют их, но только там, где у них были сложные представления, вложенные в сложные представления, и они вызывали более поздние проблемы при рефакторинге.
Редактировать:
Сегодня у меня было преобразование, в котором некоторые коллеги собираются использовать их для принудительного создания неверного плана запросов (с NOLOCK и MAXDOP 1), чтобы "поощрять" переход от устаревших сложных вложенных представлений, которые напрямую вызывает одна из их нижестоящих систем.
Когда попробовать хэш-подсказку, как насчет:
- После проверки, что адекватные индексы существуют по крайней мере в одной из таблиц.
- Попробовав перестроить запрос. Такие вещи, как преобразование объединений в "in" или "существует", изменение порядка объединения (в любом случае это действительно подсказка), перемещение логики из условия where для присоединения к условию и т. Д.
Некоторые основные правила о том, когда хеш-соединение эффективно, это когда условие соединения не существует как индекс таблицы и когда размеры таблиц различны. Если вы ищете техническое описание, есть несколько хороших описаний того, как работает хеш-соединение.
Зачем использовать любые подсказки соединения (хэш / слияние / цикл с побочным эффектом порядка силы)?
- Чтобы избежать чрезвычайно медленного выполнения (.5 -> 10.0s) угловых случаев.
- Когда оптимизатор последовательно выбирает посредственный план.
Предоставленная подсказка, вероятно, будет неидеальной для некоторых обстоятельств, но обеспечивает более последовательно предсказуемое время выполнения. Ожидаемый сценарий наихудшего и наилучшего вариантов должен быть предварительно протестирован при использовании подсказки. Предсказуемое время выполнения критически важно для веб-сервисов, где предпочтительным является строго оптимизированный номинальный запрос [.3s,.6s], а не запрос, который может варьироваться, например, [.25, 10.0s]. Могут происходить большие отклонения во время выполнения, когда статистика обновляется и применяются лучшие практики.
При тестировании в среде разработки следует также отключить "читерство", чтобы избежать отклонений во время выполнения в горячем / холодном режиме. Из другого поста...
CHECKPOINT -- flushes dirty pages to disk
DBCC DROPCLEANBUFFERS -- clears data cache
DBCC FREEPROCCACHE -- clears execution plan cache
Последний параметр может совпадать с подсказкой параметра (перекомпилировать).
MAXDOP и загрузка машины также могут иметь огромное значение во время выполнения. Материализация CTE во временных таблицах также является хорошим механизмом блокировки и кое-чем стоит рассмотреть.
Хеш-объединения распараллеливают и масштабируют лучше, чем любое другое объединение, и отлично подходят для максимизации пропускной способности в хранилищах данных.
Единственный совет, который я когда-либо видел в коде доставки, был OPTION (FORCE ORDER). Глупая ошибка в оптимизаторе SQL-запросов может привести к созданию плана, который попытается объединить нефильтрованный varchar и уникальный идентификатор. Добавление FORCE ORDER заставило его сначала запустить фильтр.
Я знаю, перегрузка столбцов это плохо. Иногда ты должен жить с этим.
Оптимизатор логического плана не гарантирует вам, что он найдет оптимальное решение: точный алгоритм слишком медленный для использования на рабочем сервере; вместо этого используются некоторые жадные алгоритмы.
Следовательно, обоснование этих команд состоит в том, чтобы позволить пользователю указать оптимальную стратегию объединения, в случае, если оптимизатор не может разобраться, что действительно лучше всего принять.