Выбор оператора оптимизатора запроса - вложенные циклы против хеш-совпадения (или слияния)

Одна из моих хранимых процедур выполнялась слишком долго. Взглянув на план выполнения запроса, я смог найти операцию слишком долго. Это был физический оператор вложенного цикла, который имел внешнюю таблицу (строки 65991) и внутреннюю таблицу (строки 19223). На вложенном цикле он показал оценочные строки = 1,268,544,993 (умножив 65991 на 19223), как показано ниже:

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

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

Вопрос: Будет ли хэш-совпадение лучше, чем вложенные циклы в этом сценарии?

Спасибо

Ответ 1

АБСОЛЮТНО. Хэш-матч будет большим улучшением. Создание хэша на более мелкой таблице строк 19,223, а затем исследование в нем с помощью таблицы строк размером 65 991 намного меньше, чем вложенного цикла, требующего сравнения 1 268 544 993 строк.

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

Если вы правильно настроили статистику и по-прежнему имеете проблему, вы можете заставить ее использовать HASH-соединение так:

SELECT *
FROM
   TableA A -- The smaller table
   LEFT HASH JOIN TableB B -- the larger table

Обратите внимание, что в тот момент, когда вы это сделаете, он также заставит порядок соединения. Это означает, что вы должны правильно упорядочить все свои таблицы, чтобы их порядок соединения имел смысл. Как правило, вы должны изучить план выполнения, который уже имеет сервер, и изменить порядок ваших таблиц в запросе, который должен совпадать. Если вы не знакомы с тем, как это сделать, основы заключаются в том, что каждый "левый" вход идет первым, а в графических планах выполнения левый вход является нижним. Для комплексного объединения, включающего множество таблиц, может быть необходимо объединить объединения в круглых скобках или использовать RIGHT JOIN, чтобы оптимальный план выполнения (обменять левый и правый входы, но ввести таблицу в правильной точке в порядке соединения).

Как правило, лучше избегать использования намеков на соединение и форсировать порядок соединения, поэтому сделайте все, что вы можете в первую очередь! Вы можете просмотреть индексы в таблицах, фрагментацию, уменьшить размеры столбцов (например, использовать varchar вместо nvarchar, где Unicode не требуется) или разделить запрос на части (сначала вставить в таблицу temp, затем присоединиться к этому).

Ответ 1

Ответ 2