问题[table-valued-parameters](dba)

遗留应用程序有一个夜间作业，它使用 TVP 重复调用一些存储过程，并按顺序传入需要处理的 10,000 个 id 的批次。现在 ID 数以百万计，看来这个过程需要的时间明显更长。每晚运行的批处理调用数量大致相同，但从分析来看，该过程似乎变得越来越慢。我们检查了通常的罪魁祸首，重建了索引并更新了正在使用的表上的统计信息，并尝试在程序上重新编译。但没有什么能解决回归问题。

该过程进行一些处理并返回一些结果，每个结果的基数可能为 10000 行。我的一位同事查看了它并通过简单地将以下内容添加到查询顶部来更新存储过程来修复性能回归：

select id into #t from @ids

@ids并用替换所有用法#t。

我对这个简单的修复感到惊讶，并试图更多地理解它。我试图创建一个非常简单的复制品。

create table dbo.ids
(
   id int primary key clustered,
   timestamp
);

create type dbo.tvp as table(id int primary key clustered)

insert into dbo.ids(id)
select row_number() over (order by 1/0)
from string_split(space(1414),' ') a,string_split(space(1414),' ') b
go
create or alter procedure dbo.tvp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from @ids t
            where t.id = i.id     
        );
end 
go
create or alter procedure dbo.temp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    select * into #t from @ids
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from #t t
            where t.id = i.id     
        );
end

这是我的简单基准。

set nocount on;
declare @s nvarchar(4000)=
'declare @ids tvp;
insert into @ids(id)
select @init + row_number() over (order by 1/0)
from string_split(space(99),char(32)) a,string_split(space(99),char(32)) b
declare @s datetime2 = sysutcdatetime()
create table #d(_ int)
insert into #d
exec dbo.tvp_proc @ids
print concat(right(concat(space(10),format(@init,''N0'')),10),char(9),datediff(ms, @s, sysutcdatetime()))',
@params nvarchar(20)=N'@init int'
print 'tvp result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000
select @s=replace(@s,'tvp_proc','temp_proc')
print 'temp table result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000

在我的机器上运行这个基准会产生以下结果：

tvp result
10,000,000  653
 1,000,000  341
   100,000  42
    10,000  12
temp table result
10,000,000  52
 1,000,000  60
   100,000  57
    10,000  59

结果表明，tvp 方法似乎随着内部 id 变大而变慢，而临时表保持相当一致。任何人都知道为什么引用具有较大值的 tvp 比临时表慢？

TVP 中使用的表类型是否有最佳实践或策略？例如，给定以下内容：

CREATE TABLE dbo.Colors (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Shapes (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Materials (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Items (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
    ColorId int FOREIGN KEY REFERENCES dbo.Colors (ID),
    ShapeId int FOREIGN KEY REFERENCES dbo.Shapes (ID),
    MaterialId int FOREIGN KEY REFERENCES dbo.Materials (ID),
);

如果您实现了一个存储过程来搜索需要支持通过 TVP 选择多种颜色、多种形状和多种材料的项目（想想 UI 中的复选框列表），您会创建三种单独的表类型，一种用于每个 TVP，还是您会创建一个类型以在所有三个中使用它？

换句话说，这：

CREATE TYPE dbo.ColorIds AS TABLE (Id int);
CREATE TYPE dbo.ShapeIds AS TABLE (Id int);
CREATE TYPE dbo.MaterialIds AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds ColorIds READONLY,
    @ShapeIds ShapeIds READONLY,
    @MaterialIds MaterialIds READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

与此相对：

CREATE TYPE dbo.Ids AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds Ids READONLY,
    @ShapeIds Ids READONLY,
    @MaterialIds Ids READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

样本是故意设计的；真正的用例包括更多的表，虽然有不同的列，但都有一个ID int主键。因此，我个人更倾向于后者。它的开销要少得多，但我很想知道这样做是否有任何缺点我应该注意。这当然仅适用于 TVP 和 TVP （我永远不会在真实表或任何其他更永久性的结构中混合不同的实体。）

在此期间，您命名表类型和 TVP 的命名约定是什么？