CPU vs Elapsed & Parallelism

我已經在這里和這裡閱讀了執行緒，我知道經過的時間是任務的總持續時間 - 如果經過的時間小於 CPU 時間，則查詢是並行的。

輸入之後，我試圖在我們遇到一些緩慢的區域中提高儲存過程的性能。

現有的 TSQL： 粘貼計劃

如您所見，我們有 6 個參數都是可選的 - 客戶查找，您可以在其中使用多種或僅使用一個變數進行搜尋。

執行時，這個過程是並行的，統計IO和時間如下：

我最初的想法是重寫該WHERE子句以將COALESCE&ISNULL函式替換為簡單( @paramCustomerID IS NULL or c.id = @paramCustomerID )

而且因為我發現 CTE 比子查詢更具可讀性，所以我也更改了查詢的那部分。

這是重寫的執行計劃：粘貼計劃

以及 Statistics IO & Time 的結果：

客戶表的邏輯讀取減少了近 1/3，CPU 時間大幅減少，但經過的時間幾乎翻了一番：新版本為 1.2 秒，現有版本為 0.545。

我無論如何都不是專家，我正在努力學習，但我看到的主要區別是新版本正在執行密鑰查找，而現有版本正在使用並行。

我希望在這裡獲得的建議或知識是哪個版本的儲存過程可以提供最佳性能？如果新版本應該更好，有什麼辦法可以讓它並行執行，這樣經過的時間會更短嗎？

試圖澄清這個問題 -

1. 這可能純粹是主觀的，並且可能在本網站上不受歡迎，但基於所提供的資訊；您將使用哪種程序以最快的速度將結果提供給最終使用者？在 WHERE 子句中具有 COALESCE/ISNULL 函式的 proc 是並行還是經過修改的過程具有較少的邏輯讀取但經過的時間較長？

2. 如果我們選擇不使用動態 SQL，您有什麼建議可以提高修改後的過程的查詢性能？

當我輸入編輯以嘗試澄清時，我確實看到 Max 提供了一些非常有用的資訊。

只是想補充一下，統計解析器資訊是由這個站點提供的。

您的第一個查詢計劃顯示並行性，而您的第二個查詢是純串列的；這就是為什麼第二個版本顯示更長的“持續時間”。

對於發生鍵查找的表，可以通過適當的覆蓋索引來防止鍵查找操作。關於不要盲目創建索引的標準警告在這裡適用 - 不要創建重複的索引，並檢查是否可以通過添加include子句來利用現有索引。例如，表上的鍵查找正在拉取這些列，這是通過掃描索引Customers無法獲得的：IX_CustomersSocialSecurityNumber

[GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].lastName
, [GoOutdoorsTN_TEST].[dbo].[Customers].DocTypeNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseState

如果您在子句中將這些列添加到索引中INCLUDE，則該掃描不需要返回表來獲取這些列，從而使輸出速度更快。

您的查詢使用“廚房水槽”模式；即：

WHERE (@x IS NULL OR someCol = @x)
    AND (@y IS NULL OR someOtherCol = @y)

您通常可以獲得更好的查詢計劃，為每個變體定制，使用動態 SQL 而不是@x IS NULL片斷。虛擬碼將是：

IF @x IS NULL AND @y IS NOT NULL
   SET @where = 'WHERE someOtherCol = @y';
IF @y IS NULL AND @x IS NOT NULL
   SET @where = 'WHERE someCol = @x';
IF @y IS NULL AND @x IS NULL
   SET @where = '';

這允許查詢優化器以更有效的方式使用列統計資訊，因為它只需要考慮每個唯一where子句中出現的列。

另外值得注意的是，我看到您正在使用WITH (NOLOCK)以防止您的查詢受到阻塞的影響。您可能希望確保了解讀取提示READ UNCOMMITTED使用的隔離級別中固有的未送出行的影響。Aaron Bertrand在這裡NOLOCK有一篇很棒的文章

我注意到計劃顯示了computer scalar您正在做的幾個操作員：

= LTRIM(RTRIM(lastName))

您的數據的真實內容周圍真的有空格lastName嗎？如果沒有，擺脫那些不必要的功能將真正幫助查詢處理器提供更好的計劃。

作為展示如何解決廚房水槽問題的一種方式，並且嚴格**用於學習目的，請考慮以下程式碼。

CREATE TABLE dbo.Customers
(
   CustomerID int NOT NULL
       CONSTRAINT PK_Customers
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , FirstName nvarchar(100) NOT NULL
   , LastName nvarchar(100) NOT NULL
   , SSN char(9) NULL
);

一些樣本數據：

INSERT INTO dbo.Customers (FirstName, LastName, SSN)
VALUES ('Joe', 'Belfiore', '012345678')
   , ('Bill', 'Gates', '876543210')
   , ('Hannah', 'Vernon', '123123123');
GO

執行搜尋的儲存過程：

CREATE PROCEDURE dbo.SearchCustomers
(
   @FirstName nvarchar(100) = NULL
   , @LastName nvarchar(100) = NULL
   , @SSN varchar(9) = NULL
)
AS
BEGIN
   /*
       BE AWARE THIS IS PROTOTYPE CODE THAT IS NOT SAFE
       AGAINST SQL INJECTION VULNERABILIES.

       IT IS STRICTLY TO SHOW HOW TO COMPILE A DYNAMIC
       WHERE CLAUSE!
   */
   SET NOCOUNT ON;
   DECLARE @Where nvarchar(max);
   DECLARE @connector nvarchar(max);
   DECLARE @qry nvarchar(max);
   SET @qry = 'SELECT CustomerID, FirstName, LastName, SSN
FROM dbo.Customers c
';
   SET @where = 'WHERE ';
   SET @connector = '';
   IF @LastName IS NOT NULL
   BEGIN
       SET @where = @where + @connector + 'c.LastName LIKE ''%' + @LastName + '%''';
       SET @connector = ' AND ';
   END
   IF @FirstName IS NOT NULL
   BEGIN
       SET @where = @where + @connector + 'c.FirstName LIKE ''%' + @FirstName + '%''';
       SET @connector = ' AND ';
   END
   IF @SSN IS NOT NULL
   BEGIN
       SET @where = @where + @connector + 'c.SSN LIKE ''%' + @SSN + '%''';
       SET @connector = ' AND ';
   END
   IF @connector <> '' SET @qry = @qry + @Where + ';';
   EXEC sys.sp_executesql @qry;
   PRINT @qry;
END
GO

一些測試搜尋：

EXEC dbo.SearchCustomers @FirstName = N'Hannah';
EXEC dbo.SearchCustomers @LastName = N'Vernon';
EXEC dbo.SearchCustomers @SSN = N'994', @LastName = N'V'

“消息”選項卡中顯示的查詢是：

SELECT CustomerID, FirstName, LastName, SSN
   FROM dbo.Customers c
   WHERE c.FirstName LIKE '%Hannah%';

SELECT CustomerID, FirstName, LastName, SSN
   FROM dbo.Customers c
   WHERE c.LastName LIKE '%Vernon%';

SELECT CustomerID, FirstName, LastName, SSN
   FROM dbo.Customers c
   WHERE c.LastName LIKE '%V%' AND c.SSN LIKE '%994%';

在實現該程式碼之前，您確實需要閱讀 Erland Sommarskog關於動態 SQL 的開創性工作。他還有一篇很棒的關於動態搜尋的文章，應該會有所幫助。

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