以不阻止并行性的方式模拟用户定义的标量函数

我想看看是否有办法欺骗 SQL Server 使用特定的查询计划。

一、环境

想象一下，您有一些在不同进程之间共享的数据。所以，假设我们有一些占用大量空间的实验结果。然后，对于每个过程，我们知道我们想要使用哪一年/哪月的实验结果。

if object_id('dbo.SharedData') is not null
    drop table SharedData

create table dbo.SharedData (
    experiment_year int,
    experiment_month int,
    rn int,
    calculated_number int,
    primary key (experiment_year, experiment_month, rn)
)
go

现在，对于每个过程，我们都将参数保存在表中

if object_id('dbo.Params') is not null
    drop table dbo.Params

create table dbo.Params (
    session_id int,
    experiment_year int,
    experiment_month int,
    primary key (session_id)
)
go

2.测试数据

让我们添加一些测试数据：

insert into dbo.Params (session_id, experiment_year, experiment_month)
select 1, 2014, 3 union all
select 2, 2014, 4 
go

insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
    2014, 3, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
    cross join master.dbo.spt_values as v2
go

insert into dbo.SharedData (experiment_year, experiment_month, rn, calculated_number)
select
    2014, 4, row_number() over(order by v1.name), abs(Checksum(newid())) % 10
from master.dbo.spt_values as v1
    cross join master.dbo.spt_values as v2
go

3. 获取结果

现在，很容易通过以下方式获得实验结果@experiment_year/@experiment_month：

create or alter function dbo.f_GetSharedData(@experiment_year int, @experiment_month int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.SharedData as d
    where
        d.experiment_year = @experiment_year and
        d.experiment_month = @experiment_month
)
go

该计划很好且平行：

select
    calculated_number,
    count(*)
from dbo.f_GetSharedData(2014, 4)
group by
    calculated_number

查询 0 计划

4.问题

但是，为了更通用地使用数据，我想要另一个功能 - dbo.f_GetSharedDataBySession(@session_id int). 因此，直接的方法是创建标量函数，翻译@session_id-> @experiment_year/@experiment_month：

create or alter function dbo.fn_GetExperimentYear(@session_id int)
returns int
as
begin
    return (
        select
            p.experiment_year
        from dbo.Params as p
        where
            p.session_id = @session_id
    )
end
go

create or alter function dbo.fn_GetExperimentMonth(@session_id int)
returns int
as
begin
    return (
        select
            p.experiment_month
        from dbo.Params as p
        where
            p.session_id = @session_id
    )
end
go

现在我们可以创建我们的函数了：

create or alter function dbo.f_GetSharedDataBySession1(@session_id int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.f_GetSharedData(
        dbo.fn_GetExperimentYear(@session_id),
        dbo.fn_GetExperimentMonth(@session_id)
    ) as d
)
go

查询 1 计划

该计划是相同的，除了它当然不是并行的，因为执行数据访问的标量函数使整个计划串行。

所以我尝试了几种不同的方法，比如使用子查询而不是标量函数：

create or alter function dbo.f_GetSharedDataBySession2(@session_id int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.f_GetSharedData(
       (select p.experiment_year from dbo.Params as p where p.session_id = @session_id),
       (select p.experiment_month from dbo.Params as p where p.session_id = @session_id)
    ) as d
)
go

查询 2 计划

或使用cross apply

create or alter function dbo.f_GetSharedDataBySession3(@session_id int)
returns table
as
return (
    select
        d.rn,
        d.calculated_number
    from dbo.Params as p
        cross apply dbo.f_GetSharedData(
            p.experiment_year,
            p.experiment_month
        ) as d
    where
        p.session_id = @session_id
)
go

查询 3 计划

但是我找不到一种方法来编写这个查询，使其与使用标量函数的查询一样好。

几个想法：

1. 基本上我想要的是能够以某种方式告诉 SQL Server 预先计算某些值，然后将它们作为常量进一步传递。
2. 如果我们有一些中间具体化提示，可能会有帮助。我已经检查了几个变体（多语句 TVF 或 cte with top），但到目前为止，没有一个计划比标量函数更好
3. 我知道 SQL Server 2017 的即将改进 - Froid：关系数据库中命令式程序的优化。不过我不确定它是否有帮助。不过，如果在这里被证明是错误的，那就太好了。

附加信息

我正在使用一个函数（而不是直接从表中选择数据），因为它更容易在许多不同的查询中使用，这些查询通常有@session_id一个参数。

我被要求比较实际执行时间。在这种特殊情况下

• 查询 0 运行约 500 毫秒
• 查询 1 运行约 1500 毫秒
• 查询 2 运行约 1500 毫秒
• 查询 3 运行约 2000 毫秒。

计划 #2 有一个索引扫描而不是查找，然后通过嵌套循环上的谓词进行过滤。计划 #3 并没有那么糟糕，但仍然比计划 #0 做更多的工作并且工作得更慢。

假设它dbo.Params很少更改，并且通常有大约 1-200 行，不超过，假设 2000 行是预期的。现在大约有 10 列，我不希望经常添加列。

Params 中的行数不固定，因此每@session_id行都会有一行。那里的列数不固定，这是我不想dbo.f_GetSharedData(@experiment_year int, @experiment_month int)从任何地方调用的原因之一，所以我可以在内部向这个查询添加新列。我很高兴听到对此的任何意见/建议，即使它有一些限制。