我有以下查询，这是非常少的，并且是整个系统中正在进行的连接的一个例子。
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
所有连接列都有索引，但性能并不好。检查查询计划显示了很多哈希匹配(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
解决方法
第一想法：
>更改为EXISTS(更改等于连接到半连接)
>您需要在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)
一个表的索引
>(Status，ForeignKeyId)INCLUDE(AdditionalColumnId)
>(ForeignKeyId，Status)INCLUDE(AdditionalColumnId)
tableFive的索引大概(typeID，PrimaryKeyId)
编辑：更新JOINS和EXISTS以匹配问题修复
