主页 / user-42489

James Z's questions

Martin Hope
James Z
Asked: 2015-06-16 20:52:43 +0800 CST
  • 26

我很难理解为什么 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