Um aplicativo legado tem um trabalho noturno que chama repetidamente algum procedimento de armazenamento usando um TVP e passa em lotes de 10.000 ids que estão em ordem sequencial que ele precisa processar. Agora que os IDs estão na casa dos milhões, parece que esse processo está demorando visivelmente mais. Quase o mesmo número de chamadas em lote está sendo executado todas as noites, mas pelo perfil parecia que o procedimento estava ficando mais lento. Verificamos os culpados usuais, reconstruímos os índices e atualizamos as estatísticas nas tabelas em uso e tentamos fazer uma recompilação no procedimento. Mas nada corrigiu a regressão.
O procedimento faz algum processamento e retorna alguns resultados, cada um com uma cardinalidade de talvez 10.000 linhas. Um dos meus colegas olhou para ele e corrigiu a regressão de desempenho atualizando o procedimento de armazenamento simplesmente adicionando o seguinte ao topo da consulta:
select id into #t from @ids
e substituindo todos os usos de @idspor #t.
Fiquei impressionado com essa correção simples e estava tentando entendê-la mais. Tentei criar uma reprodução muito simples.
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
E aqui está o meu benchmark simples.
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
A execução deste benchmark na minha máquina produz os seguintes resultados:
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
Os resultados mostram que a abordagem tvp parece desacelerar à medida que os ids internos aumentam, enquanto a tabela temporária permanece bastante consistente. Alguém tem uma idéia de por que referenciar um tvp com valores maiores é mais lento que uma tabela temporária?
