表值參數性能問題

我不知道這是否是我如何使用它們或 Microsoft 的實現的問題，但 SQL 2008 表值參數非常緩慢。

一般來說，如果我需要使用 TVP，那是因為我有很多記錄——目前它們似乎對於最少記錄以外的任何東西都慢得無法使用。

我在.Net中這樣稱呼他們：

// get the data
DataTable data = GetData();

com.CommandText = "sprocName"

// create the table-value parameter
var tvp = com.Parameters.AddWithValue("data", data);
tvp.SqlDbType = SqlDbType.Structured;

com.ExecuteNonQuery();

我跑了探查器看看為什麼，實際的 SQL 語句是這樣的：

declare @data table ...

insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )

sprocName(@data)

不過，這是一種非常緩慢的方法。如果它這樣做會更快：

insert into @data ( ... fields ... ) 
values ( ... values ... ),
      ( ... values ... ),
      -- for each row
      ( ... values ... )

我不確定為什麼它不使用更新、更快的語法。或者甚至在引擎蓋下使用SqlBulkCopy.

新語法是在 SQL 2008 中添加的，但 TVP 也是如此（我認為）。

有沒有辦法讓它做到這一點？或者我錯過了什麼？

如果 TVP 比其他選項“明顯慢”，那麼很可能您沒有正確實施它們。

1. 您不應該使用 DataTable，除非您的應用程序在將值發送到 TVP 之外還使用了它。使用該IEnumerable<SqlDataRecord>介面更快並且使用更少的記憶體，因為您不會複製記憶體中的集合只是為了將其發送到數據庫。我在以下地方記錄了這一點：

• 如何在最短的時間內插入 1000 萬條記錄？（這裡還有很多額外的資訊和連結）
• 將字典傳遞給儲存過程 T-SQL
• 從應用程序將數據流式傳輸到 SQL Server 2008（在 SQLServerCentral.com 上；需要免費註冊）

2. 您不應該使用AddWithValueSqlParameter，儘管這不太可能是性能問題。但是，它應該是：

SqlParameter tvp = com.Parameters.Add("data", SqlDbType.Structured);
tvp.Value = MethodThatReturnsIEnumerable<SqlDataRecord>(MyCollection);

3. TVP 是表變數，因此不維護統計資訊。意思是，他們向查詢優化器報告只有 1 行。所以，在你的過程中，要麼：

• 對使用 TVP 的任何查詢使用語句級重新編譯，而不是簡單的 SELECT：OPTION (RECOMPILE)
• 創建本地臨時表（即 single #）並將 TVP 的內容複製到臨時表中
• 您可以嘗試將集群主鍵添加到使用者定義的表類型
• 如果使用 SQL Server 2014 或更新版本，您可以嘗試使用 In-Memory OLTP / memory-optimized tables。請參閱：使用記憶體優化更快的臨時表和表變數

關於您看到的原因：

insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )

代替：

insert into @data ( ... fields ... ) 
values ( ... values ... ),
      ( ... values ... ),

如果這實際上是正在發生的事情，那麼：

• 如果插入是在事務中完成的，那麼就沒有真正的性能差異

• 較新的值列表語法（即VALUES (row1), (row2), (row3)）僅限於 1000 行，因此對於沒有該限制的 TVP 來說不是一個可行的選擇。但是，這不太可能是使用單個插入的原因，因為這樣做時沒有限制INSERT INTO @data (fields) SELECT tab.[col] FROM (VALUES (), (), ...) tab([col])，我在此處記錄了這一點： 表值建構子的最大行數。反而…

• 原因很可能是進行單獨插入允許將值從應用程式碼流式傳輸到 SQL Server：

  1. 使用迭代器（即IEnumerable<SqlDataRecord>上面 #1 中提到的），應用程式碼發送從方法返回的每一行，並且
  2. 建構VALUES (), (), ...列表，即使採用這種INSERT INTO ... SELECT FROM (VALUES ...)方法（不限於 1000 行），仍然需要在將任何數據發送到 SQL Server之前建構整個 VALUES列表。如果有很多數據，那麼建構超長字元串會花費更長的時間，並且在建構時會佔用更多的記憶體。

另請參閱 SQL Server 客戶諮詢團隊的這份白皮書：使用 TVP 最大化吞吐量

引用自：https://stackoverflow.com/questions/6071818