为什么SQL连接选择次优查询计划？

好的,所以我意识到这是一个非常模糊的问题,但请耐心等待.

我在很多场合遇到过这个问题,并且有不同的和不相关的查询.下面的查询需要很多分钟才能执行:

SELECT 
FROM 
    LEFT JOIN (SELECT  FROM  ) ON 

但是,只需添加连接提示,它就可以在几秒钟内查询执行:

SELECT 
FROM 
    LEFT HASH JOIN (SELECT  FROM  ) ON 

奇怪的是,提示中指定的JOIN类型并不是真正改善性能的因素.这似乎是因为提示导致优化器单独执行子查询然后加入.如果我为子查询创建一个表值函数(不​​是内联函数),我会看到相同的性能改进.例如

SELECT 
FROM 
    LEFT JOIN dbo.MySubQueryFunction() ON 

任何人都有任何想法为什么优化器在这种情况下是如此愚蠢？

如果这些表中的任何一个是表变量,则优化器使用0行的错误估计,并且通常选择嵌套循环作为连接技术.

这是因为缺乏有关表格的统计数据.

优化器是一种算法.它不是愚蠢或聪明,它按照编程方式工作.

Hash join 意味着在较小的行源上构建哈希表,这就是必须首先执行内部查询的原因.

在第一种情况下,优化器可能选择了a nested loop.它将连接条件推送到内部查询中,并使用附加谓词在每次迭代时执行内部查询.它可能找不到这个谓词的适当索引,并且full table scan确实在每次迭代中都发生了.

除非您发布确切的查询以及表中有多少行,否则很难说为什么会发生这种情况.

使用表函数,不可能将连接条件推送到内部查询中,这就是为什么它只执行一次.