主页 / dba / 问题 / 228041
Accepted
crokusek
Asked: 2019-01-25 17:15:08 +0800 CST

如何交叉应用以在视图上逐行操作？

  • 772

我们有一个针对单项查询优化的视图（200 毫秒无并行性）：

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

它还适用于一小组静态 ID（~5）。 

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

但是，如果对象来自外部来源，那么它会生成一个缓慢的计划。执行计划显示视图部分的执行分支忽略了 ObjectId 上的谓词，而在原始情况下它使用它们来执行索引查找。

select v.*
  from 
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o  
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

我们不希望投资于“双重”优化非奇异案例的视图。相反，我们“寻求”的解决方案是对每个对象重复调用一次视图，而不求助于 SP。

大多数情况下，以下解决方案逐行调用视图。但是这次不是，甚至不是只有 1 个对象：

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId 
       order by v_.SomeField
    ) v;

有一次我认为有人声称交叉应用在调用 UDF 时保证逐行执行，但这也失败了：

create function FunctionCallingView(@pObjectId bigint)
returns table
as 
return select *
  from OptimizedForSingleObjectIdView 
 where ObjectId = @pObjectId;             

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

添加选项（强制顺序）没有帮助——但是视图中已经有两个散列提示。暂时删除它们并没有帮助并且减慢了单个案例的速度。

这是基于函数的慢速案例的估计计划的片段。1行的估计是正确的。最右边（未显示）是存在不包括前 1 个结果的搜索谓词的地方。这似乎与我们遇到的其他情况类似，在其他情况下，来自表搜索的单个探测值未用作其他地方的搜索谓词。

在此处输入图像描述

sql-server execution-plan

1 个回答

  1. Best Answer

    如果不使用引入新的 T-SQL 执行范围的东西，例如非内联（多语句）表值函数，就不可能完全保证评估外部查询的每一行的视图。BEGIN...END这几乎是针对您之前的问题How to use merge hints to isolate complex queries in SQL Server的建议。

    有一次我认为有人声称交叉应用在调用 UDF 时保证逐行执行

    这不适用于内联表值函数，因为定义在优化开始之前扩展到调用查询中。

    也就是说，您可以做一些事情来强烈鼓励期望的结果。

    执行计划显示视图部分的执行分支忽略了谓词，ObjectId而在原始情况下它使用它们来执行索引查找。

    您期望ObjectId使用索引查找每个驱动行来“在视图内”评估值。这是相关的嵌套循环连接（应用）执行方式。请注意，使用APPLYT-SQL 语言元素并不能保证物理执行将使用应用样式。

    听起来好像 SQL Server 选择使用在Nested Loops JoinObjectId运算符测试的值来执行。这是一种不相关的或简单的嵌套循环连接执行模式。

    这很可能是由您在视图中使用的连接提示引起的。通常应避免连接提示，因为它们极大地限制了优化器的自由度，而不仅仅是针对连接的物理类型。特别是，连接提示还会强制整个查询的连接顺序（就像您使用了FORCE ORDER提示一样）并阻止与聚合放置和策略相关的多项优化，以及其他许多事情。

    如果你真的必须在视图中加入提示（我强烈建议你通常避免这样做），你可能会发现获得你想要的计划形状的最可靠方法是：

    1. 使用视图定义（不引用视图）创建一个内联( RETURNS TABLE) 函数。
    2. @ObjectId作为函数的参数提供。
    3. 以更可能进行索引查找的方式使用函数体内的参数放置谓词。
    4. 如果确实每次使用都应导致查找，则可以使用函数内部的表FORCESEEK提示。
    5. 使用 调用新的内联函数APPLY。

    我通常不喜欢使用特定的语法和提示来强制执行特定的物理计划形状。通过参数化查询并使用计划指南保证计划形状，您可能会取得更大的成功。

    • 10

相关问题