我有一個使用者定義的儲存過程在不傳遞顯式值的情況下呼叫時，我只想傳遞所有位置（nvarchar（50）），這是表的主鍵欄位：Monitor_Locations（具有約 850 個條目）

SP 的一部分定義如下（剪裁）。

ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
    @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
   ,@rangeStart as DateTime = '1970-01-01'
   ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
   Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
   INSERT INTO @loclist(Location)
       SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
   INSERT INTO @loclist(Location)
       SELECT
           ML.Location
       FROM Monitor_Locations as ML join
           tvf_splitstring(@Locations) as ss ON 
               ML.Location=ss.Item OR 
               ML.Location like ss.Item+'[_]%'
       ORDER BY ML.Location;
With Deploys as (
   SELECT
       D.Location,
       MIN(D.Start) as Start,
       MAX(D.[Stop]) as Stop
   FROM
       Deployments as D 
   WHERE 
       D.Stop is not NULL
)

……做一堆其他的東西……

為了在將受限站點列表發送到 SP 時提高儲存過程的速度，我想將 WHERE 子句替換為

WHERE 
   CASE
       WHEN D.Stop IS NULL THEN 0
       WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
       WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
   END=1

但是 SP 曾經需要 6-8 秒才能執行，現在需要 2.5 分鐘（對於沒有限制列表的呼叫）。我認為完整列表的每種方式所花費的時間大致相同，因為 CASE 的第二個子句應該很快被觸發，而第三個子句永遠不應該被檢查。

發生什麼了？這段程式碼：

WHERE 
   CASE
       WHEN D.Stop IS NULL THEN NULL
       WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
       WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --else null
   END is not null

此計劃需要大約 10 分鐘的執行時間：

為了對比這裡的WHERE D.Stop is not NULL計劃（6s）：

在某一時刻，此版本的 SP 需要 1 秒，但通過更改 SP 然後再返回，又需要 6 秒。如答案中所述，這可能是由於參數嗅探。

執行時間

我的目標執行時間小於 2 秒，因為這將是 Web 應用程序上經常執行的 SP，它使用它來填充和限制其他使用者選擇。基本上，我不希望這成為一個明顯的瓶頸。最初的執行時間大約為 3 分鐘，但在添加或更改了一些索引之後，這下降到了 6-8 秒的範圍。

週一 (2016-08-29)，在進行重大更改之前 沒有輸入參數的簡單 WHERE：5 秒 帶有 rangeStart 和 rangeEnd 的簡單 WHERE：4 秒 帶有 @Locations 設置為 7 元素 CSV 變數的簡單 WHERE 案例 WHERE：最多 10 分鐘

重新處理 CLR 函式後（請參閱下面的答案） 星期二 (2016-08-30) 沒有輸入參數的簡單或 CASEd WHERE 或帶有 rangeStart 和 rangeEnd 的簡單或 CASEd WHERE：3s Simple 或 CASEd WHERE 有 7 個元素 @Locations：0 -1s

將表變數 @loclist 遷移到臨時表後 #loclist 所有測試的 WHEREs/參數：0-1s

兩大性能問題：

1. 您的 CSV 拆分器功能是主要的性能殺手。將它換成 Jeff Moden 的 DelimitedSplit8k 函式。您可以在此處閱讀所有相關資訊。或者更好的是，如果您使用的是 2008+，請將其換成 CLR 函式或表值參數。查看 Aaron Bertrand對各種 CSV 拆分器功能的性能測試。CLR 是總體上的贏家。
2. 表變數即使只有 1 行也可能是一個很大的性能殺手。將其切換到臨時表並為其添加聚集索引。

在您現在提供的執行計劃中，該函式顯示成本為 0%，但事實並非如此。函式成本較高，但您不會在執行計劃中看到實際成本，除非它是內聯表值函式。

不幸的是，雖然我曾經有 1 秒的執行時間，但通過更改 SP 然後再返回，它又需要 6 秒。

那是參數嗅探的味道。

因為您共享了圖像，所以我無法詳細說明您的問題。我標記了兩個執行計劃之間的主要區別。

在第一個計劃中，SQL Server 為查詢創建了執行計劃並使用了非聚集索引搜尋，但在第二個計劃中，它使用了索引掃描。這是增加總執行時間的主要原因。

索引搜尋：- 僅觸及符合條件的行和包含這些符合條件的行的頁面。

索引掃描：- 觸及表/索引中的每一行，無論它是否合格。

當您在 where 條件下操作數據（列）（使用函式或 case 語句）時，forst SQL Server 會掃描完整的索引/表，然後執行操作數據並匹配您的條件。此過程會增加記憶體使用率、磁碟 IO 並增加執行時間。

包括 Tara Kizer 的建議，我建議

1. 在表中添加一列，並根據您在 WHERE 條件中使用的邏輯在用於將數據插入表中的 SP 中進行更改，並使用 CASE 語句在其上創建索引。它會解決你的問題。

要麼

2. 拉取臨時表中的所有記錄，在其上創建索引並將數據插入到包括 CASE 語句的表中，並從臨時表中檢索數據。

CREATE TABLE #TEMP
(
Location DataType(LENGTH), --Estimated Length
Start DataType(LENGTH),
STOP DataType(LENGTH),
WCondition DataType(LENGTH)
);
CREATE NONCLUSTERED INDEX IDXTMP ON #TEMP(WCONDITION);

INSERT INTO #TEMP
SELECT
       D.Location,
       MIN(D.Start) as Start,
       MAX(D.[Stop]) as Stop,
       CASE
       WHEN D.Stop IS NULL THEN 0
       WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
       WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 END AS Wcondition
   FROM
       Deployments as D

SELECT * FROM #TEMP WHERE W Condition --Put Condition

謝謝

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