我的问题是,TOP n子句如何(以及为什么)影响查询的执行计划？
以下是我案例中正在进行的简化版本：
该查询匹配来自两个表A和B的行.
如果没有TOP子句,优化器估计表A中将有19k行,表B中将有46k行.返回的实际行数为A,16k,B为13k.哈希匹配用于连接这两个结果集共69行(然后应用排序).此查询很快发生.
当我添加TOP 1001时,优化器不使用哈希匹配;相反,它首先对表A中的结果进行排序(相同估计/实际为19k / 16k)并对表B进行嵌套循环.表B的估计行数现在为1,奇怪的是直接TOP n影响对B的估计执行次数(索引搜索) – 它似乎总是2n 1,或者在我的情况下是2003.如果我改变TOP n,这个估计会相应地改变.当然,由于这是一个嵌套连接,实际执行次数是16k(表A中的行数),这会减慢查询速度.
实际情况稍微复杂一点,但它捕获了基本的想法/行为.使用索引搜索搜索两个表.这是sql Server 2008 R2企业版.
解决方法
I would have guessed that when a query includes TOP n the database
engine would run the query ignoring the the TOP clause,and then at
the end just shrink that result set down to the n number of rows that
was requested. The graphical execution plan seems to indicate this is
the case — TOP is the “last” step. But it appears there is more going
on.
上述方式的措辞让我觉得你可能对查询的执行方式有不正确的心理描述.查询计划中的运算符不是一个步骤(上一步的完整结果集由下一步计算.
sql Server使用流水线执行模型,其中每个运算符公开Init(),GetRow()和Close()等方法.正如GetRow()名称所示,运算符根据需要一次生成一行(根据其父运算符的要求).这在联机丛书Logical and Physical Operators reference中有记录,在我的博客Why Query Plans Run Backwards中有更多详细信息.这种一次一行的模型对于形成查询执行的良好直觉至关重要.
My question is,how (and why) does a
TOPn clause impact the execution
plan of a query?
一些逻辑操作(如TOP,半连接和FAST n query hint)会影响查询优化器成本执行计划替代方案的方式.基本思想是,一个可能的计划形状可能比优化为返回所有行的不同计划更快地返回前n行.
例如,索引嵌套循环连接通常是返回少量行的最快方法,但是对于较大的集合,散列或合并连接与扫描可能更有效.查询优化器对这些选择的理由是通过在逻辑运算树中的特定点设置Row Goal.
行目标会修改查询计划备选方案的计算方式.它的本质是优化器首先将每个运算符计算成本,就像需要完整的结果集一样,在适当的点设置行目标,然后在计划树中向下工作,估计它预期需要检查的行数满足行目标.
例如,逻辑TOP(10)在逻辑查询树中的特定点处设置10的行目标.修改导致行目标的运算符的成本以估计他们需要生成多少行以满足行目标.此计算可能变得复杂,因此使用fully worked example和带注释的执行计划更容易理解所有这些.行目标可以影响连接类型的选择,或者搜索和查找是否优先于扫描.关于here的更多细节.
与往常一样,基于行目标选择的执行计划受优化程序的推理能力和提供给它的信息质量的影响.并非每个具有行目标的计划在实践中都会更快地生成所需的行数,但根据成本计算模型,它会.
如果行目标计划证明不会更快,通常有方法可以修改查询或向优化程序提供更好的信息,以便自然选择的计划是最佳的.在您的情况下哪个选项适当取决于当然的细节.行目标功能通常非常有效(尽管在并行执行计划中使用时需要注意bug).
您的特定查询和计划可能不适合此处的详细分析(如果您愿意,请务必提供实际的执行计划),但希望此处列出的想法可以帮助您取得进展.
