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时都必要记着从头测试任何此类调查到的举动.

总的来说,得当您的办理方案取决于我们无法为您判定的各类身分.可是,我会勉励您思量担保未来始终有用的办理方案,并尽也许与优化器一路行使(而不是阻挡).

（编辑：湖南网）

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