Производительность запросов SQL Server - устранение необходимости в Hash Match (Inner Join)

У меня есть следующий запрос, который делает очень мало и является примером типа соединений, которые я выполняю по всей системе.

select t1.PrimaryKeyId, t1.AdditionalColumnId
from TableOne t1
    join TableTwo t2 on t1.ForeignKeyId = t2.PrimaryKeyId
    join TableThree t3 on t1.PrimaryKeyId = t3.ForeignKeyId
    join TableFour t4 on t3.ForeignKeyId = t4.PrimaryKeyId
    join TableFive t5 on t4.ForeignKeyId = t5.PrimaryKeyId
where 
    t1.StatusId = 1
    and t5.TypeId = 68

Есть индексы во всех столбцах соединения, однако производительность невелика. Проверка плана запроса показывает много Hash Match (Inner Joins), когда я действительно хочу видеть присоединение вложенных циклов.

Количество записей в каждой таблице выглядит следующим образом:

select count(*) from TableOne

= 64393

select count(*) from TableTwo

= 87245

select count(*) from TableThree

= 97141

select count(*) from TableFour

= 116480

select count(*) from TableFive

= 62

Каков наилучший способ повысить производительность этого типа запросов?

Ответ 1

Первые мысли:

Изменить на EXISTS (изменения equi-join для полусоединения)
Вам нужно иметь индексы на t1.StatusId, t5.TypeId и INCLUDE t1.AdditionalColumnID

Я бы не стал беспокоиться о вашем методе соединения...

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

select t1.PrimaryKeyId, t1.AdditionalColumnId
from
    TableOne t1
where 
    t1.Status = 1
    AND EXISTS (SELECT *
        FROM
          TableThree t3
          join TableFour t4 on t3.ForeignKeyId = t4.PrimaryKeyId
          join TableFive t5 on t4.ForeignKeyId = t5.PrimaryKeyId
        WHERE
          t1.PrimaryKeyId = t3.ForeignKeyId
          AND
          t5.TypeId = 68)
    AND EXISTS (SELECT *
        FROM
          TableTwo t2
        WHERE
          t1.ForeignKeyId = t2.PrimaryKeyId)

Индекс для tableOne.. один из

(Status, ForeignKeyId) INCLUDE (AdditionalColumnId)
(ForeignKeyId, Status) INCLUDE (AdditionalColumnId)

Указатель для tableFive... возможно (typeID, PrimaryKeyId)

Изменить: обновленные JOINS и EXISTS для соответствия исправлениям вопроса

Ответ 2

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

Вы можете использовать подсказку forceseek для принудительного поиска индекса:

inner join TableTwo t2 with (FORCESEEK) on t1.ForeignKeyId = t2.PrimaryKeyId

В качестве альтернативы вы можете принудительно связать цикл с ключевым словом loop:

inner LOOP join TableTwo t2 on t1.ForeignKeyId = t2.PrimaryKeyId

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