我如何通過更改查詢或添加索引來調整以下 proc

我遇到了一個執行速度非常慢的 proc 大約一個小時的問題：

請原諒，因為我必須從系統中寫出整個程式碼才能粘貼到這裡。當我複制該資訊時，我將使用有關表架構和索引的更多詳細資訊來編輯問題：

SP 如下所示

CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
   SET value1 = 33, statusdate = @today
from 
   bigtable t
   INNER JOIN anotherbigtable t1 on t.id=t1.id
   INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
   t1.paydate <= @parameter2
   AND t.value1=32
   AND t1.payid =2

bigtable 大約有 800 M 行，並在 id 上聚集為 PK，在 userid（第一個鍵）上聚集為 NC，tabletypeid（第二個鍵）

anotherbigtable 大約是。300 M 行

@parameter2大多數時候接收為 NULL 而@parameter1 變化

記憶體計劃如下所示

我要做的第一件事是避免在繁重的 SQL 操作中使用表變數。您應該改用臨時表。最初或將表變數 @table1 選擇到臨時表中，然後在更新語句中使用該臨時表。

表變數在歷史上被認為是瓶頸，因為它們沒有維護統計數據，即使微軟在 SQL 2014 中對它們進行了改進，它們仍然表現不佳。這是一篇關於他們的好 Brent Ozar 文章：https ://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/

您可能還受益於在“anotherbg table”上使用強制搜尋提示。但首先從上述更改開始。

供參考：http ://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1

以及關於表提示的 MS Docs：https ://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

通常，查詢提示只能由有經驗的人在非常特殊的情況下使用，但我發現對於較大的表，有時 SQL 引擎會在索引搜尋確實是更好的選擇時嘗試進行索引掃描。（更新“anotherbg table”上的表統計資訊也可能更自然地解決您的問題。）

所以這是情境性的，您需要進行測試，但強制搜尋索引提示可能會有所幫助。

這是帶有索引提示的更新查詢的樣子：

UPDATE bigtable
   SET value1 = 33, statusdate = @today
from 
   bigtable t
   INNER JOIN anotherbigtable t1 WITH (FORCESEEK) on t.id=t1.id
   INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
   t1.paydate <= @parameter2
   AND t.value1=32
   AND t1.payid =2

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