当TVP数值变大时，使用TVP的过程会变慢吗？

Question

遗留应用程序有一个夜间作业，它使用TVP反复调用某些存储过程，并以顺序顺序传递10000个it，这些it是它需要处理的。既然is是数百万，这个过程似乎要花费更长的时间。每晚运行的批处理电话数量大致相同，但从分析来看，这个过程似乎越来越慢。我们检查了常见的罪魁祸首，重建了索引，更新了正在使用的表上的统计数据，并试图在过程中重新编译。但是没有什么能解决这个问题。

该过程进行一些处理，并返回几个结果，每个结果的基数大约为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方法似乎随着内部ids的增大而减慢，因为临时表保持了相当的一致性。有人知道为什么引用值较大的tvp比临时表慢吗？

Answer 1

即使将表变量用作参数(TVP)，也会给出非常差的基数估计，而不是更精确地估计的临时表。随着TVP与临时表中使用的数据量的增加，这种差异尤其明显。如果您仔细查看了每个实现的执行计划中的估计行数和实际行数，您应该会看到临时表的估计要准确得多。

您可以在这个Jeremiah Peschka哨所中阅读更多关于TVP及其缺点的内容。具体而言，Gotchas节：

首先:不能更改作为表值参数传入的表变量。不管出现什么价值观，你都被困住了。不能应用插入、更新或删除。第二:表值参数仍然是表变量--它们得到可怕的基数估计。我们可以使用相同的技术来解决这两个问题--将TVP的内容复制到临时表中。

此外，过程中使用的TVP可能会导致参数嗅探问题，如另一个职位详细信息。这个引用为基数估计增加了一些细节，不管实际表变量有多大，您都会遇到TVP：

表变量(除非您重新编译，或者使用跟踪标志)将根据您使用的基数估计值的哪个版本来显示1行或100行估计值。旧版本猜测1行，新版本猜测100行。

这是一个关于基数估计问题的额外的好文章，表变量的结果，由Pinal。

之所以有一个糟糕的基数估计(例如在本例中为低估)，一个关键的原因是它将导致SQL引擎不足提供必要的服务器资源来处理查询和服务数据。例如，您的查询请求的内存可能比处理它所需的要少得多，因为基数估计值很低，使得SQL引擎认为返回的行比实际要少得多。表变量越大，估计值与实际值之间的差异就越大。

在可能的情况下，您应该尽可能地选择临时表，因为它们比Table变量具有更多的性能好处，并且可以完成表变量所能做的几乎所有事情。在需要使用Table变量的情况下，首先将其选择到临时表中，然后在后续查询中使用该临时表。