sql-server – 用户定义函数的优化问题

我有一个问题,理解为什么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)
)

解决方法

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

鉴于分配给UDF的小基数估计适用,不幸的是,n-ary连接扩展启发式重新定位它在树中的位置比您希望的要多.

由于具有至少三个连接(包括应用),该查询还有资格进行搜索0优化.您获得的最终物理计划,具有奇怪的扫描,是基于启发式推断的连接顺序.它的成本足够低,优化器认为该计划“足够好”. UDF的低成本估算和基数有助于早期完成.

搜索0(也称为事务处理阶段)以低基数OLTP类型查询为目标,最终计划通常以嵌套循环连接为特征.更重要的是,搜索0只运行优化器探索能力的一小部分.此子集不包括通过连接提取应用查询树(规则PullApplyOverJoin).这正是在测试用例中将UDF应用重新定位在连接上方所需的内容,以便在操作序列中显示为最后一个(就像它一样).

还有一个问题,优化器可以决定天真嵌套循环连接(连接本身的连接谓词)和相关索引连接(应用),其中相关谓词使用索引搜索应用于连接的内侧.后者通常是所需的计划形状,但优化器能够探索两者.使用不正确的成本计算和基数估算,它可以选择非应用NL连接,如在提交的计划中(解释扫描).

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

没有支持的方法来避免搜索0计划选择,早期优化器终止或改进UDF的成本(除了SQL Server 2014 CE模型中的有限增强).这留下了诸如计划指南,手动查询重写(包括TOP(1)想法或使用中间临时表)和避免计算成本低的“黑盒子”(从QO的角度来看),如非内联函数.

重写CROSS APPLY作为OUTER APPLY也可以工作,因为它当前阻止了一些早期的join-collapsing工作,但你必须小心保留原始查询语义(例如,拒绝任何可能引入的NULL扩展行,而不是优化器折叠回交叉应用).您需要注意,虽然不能保证此行为保持稳定,因此每次修补或升级SQL Server时都需要记住重新测试任何此类观察到的行为.

总的来说,适合您的解决方案取决于我们无法为您判断的各种因素.但是,我会鼓励您考虑保证将来始终有效的解决方案,并尽可能与优化器一起使用(而不是反对).

（编辑：大连站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!