主页 / dba / 问题 / 104192
Accepted
James Z
Asked: 2015-06-16 20:52:43 +0800 CST

用户定义函数的优化问题

  • 772

我很难理解为什么 SQL 服务器决定为表中的每个值调用用户定义的函数，即使应该只获取一行。实际的 SQL 要复杂得多，但我能够将问题简化为：

select  
    S.GROUPCODE,
    H.ORDERCATEGORY
from    
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT    
    cross apply dbo.GetGroupCode (P.FACTORY) S
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

对于这个查询，SQL Server 决定为 PRODUCT 表中存在的每个单个值调用 GetGroupCode 函数，即使从 ORDERLINE 返回的估计和实际行数是 1（它是主键）：

查询计划

计划资源管理器中的相同计划显示行数：

计划浏览器 表：

ORDERLINE: 1.5M rows, primary key: ORDERNUMBER + ORDERLINE + RMPHASE (clustered)
ORDERHDR:  900k rows, primary key: ORDERID (clustered)
PRODUCT:   6655 rows, primary key: PRODUCT (clustered)

用于扫描的索引是：

create unique nonclustered index PRODUCT_FACTORY on PRODUCT (PRODUCT, FACTORY)

该函数实际上稍微复杂一些，但是类似这样的虚拟多语句函数也会发生同样的事情：

create function GetGroupCode (@FACTORY varchar(4))
returns @t table(
    TYPE        varchar(8),
    GROUPCODE   varchar(30)
)
as begin
    insert into @t (TYPE, GROUPCODE) values ('XX', 'YY')
    return
end

我能够通过强制 SQL 服务器获取前 1 个产品来“修复”性能，尽管 1 是可以找到的最大值：

select  
    S.GROUPCODE,
    H.ORDERCAT
from    
    ORDERLINE L
    join ORDERHDR H
        on H.ORDERID = M.ORDERID
    cross apply (select top 1 P.FACTORY from PRODUCT P where P.PRODUCT = L.PRODUCT) P
    cross apply dbo.GetGroupCode (P.FACTORY) S
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

然后计划形状也变成了我最初期望的样子：

带顶部的查询计划

我也认为索引 PRODUCT_FACTORY 小于聚集索引 PRODUCT_PK 会产生影响，但即使强制查询使用 PRODUCT_PK，计划仍然与原始计划相同，调用了 6655 次函数。

如果我完全省略了 ORDERHDR，那么计划首先从 ORDERLINE 和 PRODUCT 之间的嵌套循环开始，并且该函数只被调用一次。

我想了解这可能是什么原因，因为所有操作都是使用主键完成的，如果它发生在无法轻易解决的更复杂的查询中，如何解决它。

编辑：创建表语句：

CREATE TABLE dbo.ORDERHDR(
    ORDERID varchar(8) NOT NULL,
    ORDERCATEGORY varchar(2) NULL,
    CONSTRAINT ORDERHDR_PK PRIMARY KEY CLUSTERED (ORDERID)
)

CREATE TABLE dbo.ORDERLINE(
    ORDERNUMBER varchar(16) NOT NULL,
    RMPHASE char(1) NOT NULL,
    ORDERLINE char(2) NOT NULL,
    ORDERID varchar(8) NOT NULL,
    PRODUCT varchar(8) NOT NULL,
    CONSTRAINT ORDERLINE_PK PRIMARY KEY CLUSTERED (ORDERNUMBER,ORDERLINE,RMPHASE)
)

CREATE TABLE dbo.PRODUCT(
    PRODUCT varchar(8) NOT NULL,
    FACTORY varchar(4) NULL,
    CONSTRAINT PRODUCT_PK PRIMARY KEY CLUSTERED (PRODUCT)
)
sql-server sql-server-2005

2 个回答

  1. Best Answer

    您获得计划的主要技术原因有以下三个：

    1. 优化器的成本计算框架对非内联函数没有真正的支持。它不会尝试查看函数定义的内部以查看它可能有多昂贵，它只是分配一个非常小的固定成本，并估计函数每次调用时都会产生 1 行输出。这两种建模假设通常都是完全不安全的。这种情况在 2014 年有了非常轻微的改善，启用了新的基数估计器，因为固定的 1 行猜测被固定的 100 行猜测所取代。但是，仍然不支持对非内联函数的内容进行成本计算。
    2. SQL Server 最初折叠连接并应用于单个内部 n 元逻辑连接。这有助于优化器稍后解释加入订单。将单个 n 元联接扩展为候选联接顺序是稍后进行的，并且主要基于启发式。例如，内连接在外连接之前，小表和选择性连接在大表和选择性较少的连接之前，等等。
    3. 当 SQL Server 执行基于成本的优化时，它会将工作分成可选的阶段，以尽量减少花费太长时间优化低成本查询的机会。主要分为三个阶段，搜索 0、搜索 1 和搜索 2。每个阶段都有进入条件，后期阶段比早期阶段能够进行更多的优化器探索。您的查询恰好符合能力最低的搜索阶段，即阶段 0。在那里找到了一个足够低的成本计划，不会进入后面的阶段。

    鉴于分配给 UDF 的小基数估计应用，不幸的是，n 元连接扩展启发式将其重新定位在树中的位置比您希望的要早。

    由于具有至少三个连接（包括应用），该查询还符合搜索 0 优化的条件。您获得的最终物理计划，以及看起来很奇怪的扫描，是基于启发式推断的连接顺序。它的成本足够低，以至于优化器认为该计划“足够好”。UDF 的低成本估计和基数有助于提前完成。

    搜索 0（也称为事务处理阶段）针对低基数 OLTP 类型的查询，最终计划通常具有嵌套循环连接。更重要的是，搜索 0 只运行优化器探索能力的一个相对较小的子集。此子集不包括通过连接（规则PullApplyOverJoin）向查询树上拉应用。这正是测试用例中将 UDF 应用重新定位在连接之上所需要的，以便在操作序列中出现在最后一个（实际上）。

    还有一个问题是优化器可以在朴素嵌套循环连接（连接本身的连接谓词）和相关索引连接（应用）之间做出决定，其中相关谓词使用索引查找应用于连接的内侧。后者通常是所需的计划形状，但优化器能够探索两者。如果成本计算和基数估计不正确，它可以选择非应用 NL 连接，如在提交的计划中（解释扫描）。

    因此，有多个相互作用的原因涉及几个通用优化器功能，这些功能通常可以很好地在短时间内找到好的计划，而不会使用过多的资源。避免任何一个原因足以为示例查询生成“预期的”计划形状，即使是空表：

    计划在禁用搜索 0 的空表上

    没有受支持的方法可以避免搜索 0 计划选择、提前终止优化器或提高 UDF 的成本（除了 SQL Server 2014 CE 模型中对此的有限增强）。这留下了诸如计划指南、手动查询重写（包括TOP (1)想法或使用中间临时表）以及避免成本低的“黑匣子”（从 QO 的角度来看）之类的东西，例如非内联函数。

    重写CROSS APPLYasOUTER APPLY也可以工作，因为它目前可以防止一些早期的连接折叠工作，但是您必须小心保留原始查询语义（例如，拒绝任何NULL可能引入的扩展行，而不会使优化器折叠回交叉申请）。尽管不能保证此行为保持稳定，但您需要注意，因此您需要记住在每次修补或升级 SQL Server 时重新测试任何此类观察到的行为。

    总体而言，适合您的解决方案取决于我们无法为您判断的各种因素。但是，我鼓励您考虑保证在未来始终有效的解决方案，并且尽可能与优化器一起工作（而不是反对）。

    • 30

  2. 看起来这是优化器基于成本的决定，但却是一个相当糟糕的决定。

    如果您将 50000 行添加到 PRODUCT，优化器会认为扫描工作量太大，并为您提供一个计划，其中包含 3 次搜索和一次对 UDF 的调用。

    我在 PRODUCT 中获得 6655 行的计划

    在此处输入图像描述

    在 PRODUCT 中有 50000 行，我得到了这个计划。

    在此处输入图像描述

    我想调用 UDF 的成本被严重低估了。

    在这种情况下可以正常工作的一种解决方法是将查询更改为对 UDF 使用外部应用。无论 PRODUCT 表中有多少行，我都会得到好的计划。

    select  
    S.GROUPCODE,
    H.ORDERCATEGORY
from    
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT    
    outer apply dbo.GetGroupCode (P.FACTORY) S
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01' and
    S.GROUPCODE is not null

    在此处输入图像描述

    在您的情况下，最好的解决方法可能是将您需要的值放入临时表中，然后使用交叉应用到 UDF 查询临时表。这样，您就可以确定 UDF 不会被执行得过多。

    select  
    P.FACTORY,
    H.ORDERCATEGORY
into #T
from    
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT
where   
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

select  
    S.GROUPCODE,
    T.ORDERCATEGORY
from #T as T
  cross apply dbo.GetGroupCode (T.FACTORY) S

drop table #T

    您可以top()在派生表中使用，而不是持久化到临时表，以强制 SQL Server 在调用 UDF 之前评估连接的结果。只需在顶部使用一个非常高的数字，SQL Server 就必须先计算该部分查询的行数，然后才能继续使用 UDF。

    select S.GROUPCODE,
       T.ORDERCATEGORY
from (
     select top(2147483647)
         P.FACTORY,
         H.ORDERCATEGORY
     from    
         ORDERLINE L
         join ORDERHDR H on H.ORDERID = L.ORDERID
         join PRODUCT P  on P.PRODUCT = L.PRODUCT    
     where   
         L.ORDERNUMBER = 'XXX/YYY-123456' and
         L.RMPHASE = '0' and
         L.ORDERLINE = '01'
     ) as T
  cross apply dbo.GetGroupCode (T.FACTORY) S

    在此处输入图像描述

    我想了解这可能是什么原因，因为所有操作都是使用主键完成的，如果它发生在无法轻易解决的更复杂的查询中，如何解决它。

    我真的无法回答，但我认为无论如何我应该分享我所知道的。我不知道为什么要考虑扫描 PRODUCT 表。在某些情况下，这是最好的做法，并且有一些关于优化器如何处理 UDF 的东西我不知道。

    一个额外的观察是，您的查询在 SQL Server 2014 中使用新的基数估计器获得了一个很好的计划。这是因为每次调用 UDF 的估计行数为 100，而不是 SQL Server 2012 及之前版本中的 1。但是它仍然会在扫描版本和搜索版本之间做出相同的基于成本的决定。即使在 SQL Server 2014 中，由于 PRODUCT 中的行数少于 500（在我的情况下为 497），您也可以获得计划的扫描版本。

    • 24

相关问题