Roman Pekar's questions

我不确定我发现的是否是一个错误，但它确实看起来像。我找不到太多关于它的信息，所以我决定把它放在这里。

因此，简而言之，在分区表上定义的触发器中访问内部表（inserted和）时，我面临着糟糕的性能。deleted

为了测试这个问题，我创建了一些简单的表，完全相同，但一个是分区的，另一个不是：

create table [dbo].[Test1](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc) on ps_part(part_id)
);

create table [dbo].[Test2](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc)
);

然后我用一些数据填充了表格。我现在没有数据生成脚本，我只是使用了一些本地数据，但是这些表中有大约 473 个不同的分区和大约 383M 行。

然后我刚刚测试了这些表的更新速度有多快，使用非常简单的查询，例如

update dbo.Test1 set cost = cost + 0.1 where part_id = ??;
update dbo.Test1 set cost = cost - 0.1 where part_id = ??;

update dbo.Test2 set cost = cost + 0.1 where part_id = ??;
update dbo.Test2 set cost = cost - 0.1 where part_id = ??;

结果是分区表的逻辑平均更新时间约为2 秒，非分区表的平均更新时间约为4 秒。

然后我在两个表上创建了简单的触发器

alter trigger [dbo].[Test1__changed] on [dbo].[Test1]
after insert,update,delete
as 
begin
    set nocount on;

    select a.part_id
    into #temp11111111
    from (
        select r.part_id from inserted as r
        union
        select r.part_id from deleted as r
    ) as a;
end

之后我尝试了相同的测试查询，结果非常奇怪 - 在分区表上，查询平均需要3 分钟才能完成，而在非分区表上，时间与没有触发器的情况相似 -大约4 秒。

你知道为什么会发生这种情况吗？有什么办法可以解决这个问题？

我想看看是否有办法欺骗 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)从任何地方调用的原因之一，所以我可以在内部向这个查询添加新列。我很高兴听到对此的任何意见/建议，即使它有一些限制。

最近，我开始研究我们系统中某些仓库行为部分的列存储索引。

我无法实现的是使聚合下推工作。我已经检查了数据类型和所有要求，但仍然无法正常工作。可以肯定的是，我只是从这个问题中运行了查询，我发现所有数据类型的执行计划都完全相同。

SQL Server 的版本是SP1-CU5。

你知道我还能检查什么吗？

更新：我想我明白了，我有标准版，这是标准版的限制之一。