主页 / dba / 问题 / 285526
Accepted
Michael B
Asked: 2021-02-17 19:21:43 +0800 CST

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

  • 772

遗留应用程序有一个夜间作业，它使用 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 比临时表慢？

sql-server-2016 table-valued-parameters

1 个回答

  1. Best Answer

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

    您可以在这篇 Jeremiah Peschka 帖子中了解更多关于 TVP及其缺点的信息。特别是陷阱部分：

    首先：作为表值参数传入的表变量不能更改。无论出现什么值，您都会陷入困境。不能应用插入、更新或删除。

    第二：表值参数仍然是表变量——它们得到可怕的基数估计。

    我们可以使用相同的技术解决这两个问题——将 TVP 的内容复制到临时表中。

    此外，程序中使用的TVP可能会导致参数嗅探问题，正如其他帖子所详述的那样。无论实际表变量有多大，这句话都会为您将遇到的TVP基数估计添加一些细节：

    表变量（除非您重新编译或使用跟踪标志）将运动 1 或 100 行估计，具体取决于您使用的基数估计器的版本。老版本猜1行，新版本猜100行。

    这是一篇关于基数估计问题的又一篇好文章，由Pinal Dave 撰写。

    在这种情况下，基数估计错误（例如低估）的一个关键原因是，它会导致 SQL 引擎未充分配置必要的服务器资源来处理查询和提供数据。例如，您的查询请求的内存可能比处理它所需的内存少得多，因为低基数估计使 SQL 引擎认为返回的行数比实际要少得多。表变量越大，估计值与实际值之间的差异越大。

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

    • 3

相关问题