當 TVP 數值變大時，使用 TVP 的程序會變慢？

遺留應用程序有一個夜間作業，它使用 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），也給出了非常差的基數估計，而不是更準確地估計的臨時表。****隨著TVP與Temp Table中使用的數據量增加，這種差異尤其明顯。如果您仔細查看每個實現的執行計劃中的****估計行數與實際行數，您應該會發現臨時表的估計要準確得多。

您可以在這篇 Jeremiah Peschka 文章中了解更多關於 TVP及其缺點的資訊。特別是陷阱部分：

首先：作為表值參數傳入的表變數不能更改。無論出現什麼值，您都會陷入困境。不能應用插入、更新或刪除。

第二：表值參數仍然是表變數——它們得到可怕的基數估計。

我們可以使用相同的技術解決這兩個問題——將 TVP 的內容複製到臨時表中。

此外，程序中使用的TVP可能會導致參數嗅探問題，正如其他文章所詳述的那樣。無論實際表變數有多大，這句話都會為您將遇到的TVP****基數估計添加一些細節：

表變數（除非您重新編譯或使用跟踪標誌）將運動 1 或 100 行估計，具體取決於您使用的基數估計器的版本。老版本猜1行，新版本猜100行。

這是一篇關於基數估計問題的又一篇好文章，由Pinal Dave 撰寫。

---

在這種情況下，基數估計錯誤（例如低估）的一個關鍵原因是，它會導致 SQL 引擎未充分配置必要的伺服器資源來處理查詢和提供數據。例如，您的查詢請求的記憶體可能比處理它所需的記憶體少得多，因為低基數估計使 SQL 引擎認為返回的行數比實際要少得多。表變數越大，估計值與實際值之間的差異越大。

---

您幾乎應該總是盡可能選擇臨時表，因為它們比****表變數具有更多的性能優勢，並且幾乎可以完成表變數可以做的所有事情等等。在需要使用表變數的情況下，首先將其選擇到臨時表中，然後在後續查詢中使用該臨時表是可行的方法。

引用自：https://dba.stackexchange.com/questions/285526