狡猾的 T-SQL 查詢執行讓我發瘋

SQL Server 2019。這是xml 計劃要點的連結。

你好，我很想知道為什麼這個查詢在找不到包含其中一種狀態的記錄時需要 0.02 秒才能執行。當它找到具有包含狀態之一的記錄時，它往往會快得多。我猜這是因為一旦找到匹配的 1 行，查詢就會停止。

SELECT TOP 1 IDNum,
            FORMAT(Date, 'M/d/yy') AS theDate,
            Status,
            Rate
FROM   theDB
      INNER JOIN DomainTable
              ON theDB.IDNum = DomainTable.IDNum
WHERE  DomainIP = '127.0.0.1'
      AND status IN ( 'Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP',
                      'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z' )
ORDER  BY theDB.IDNum DESC 

（DomainIP屬於DomainTable，其他屬於DB）

在執行計劃中，最大的成本是 33% 的 TOP N SORT 有 29% 的聚集索引搜尋 DomainTable 上的一個鍵查找正在使用 29% DomainTable 上 IP 的索引搜尋為 9%

我的問題是：

有什麼辦法可以讓 TOP N 不那麼重？

0.02 秒並沒有那麼慢，但這個查詢也很輕。所以我想盡可能地優化它。

DomainTable 中只有四個條目，其中 IP 為 127.0.0.1，因此基本上需要 0.2 秒來決定返回哪個條目，結果答案是沒有一個，因為其中任何一個的狀態都不正確. 有沒有辦法只創建一個索引，將所有這些資訊保存在 RAM 或其他東西中？

有什麼辦法可以讓 TOP N 不那麼重？

我認為這裡對執行計劃的工作方式存在一些誤解。

這個數字只是估計的成本，這是 SQL Server 用來確定最有效的執行計劃的模型。它不會在執行時更新，因此即使操作員使用的資源很少，估計成本仍會顯示為與創建計劃時一樣。

查看您提供的執行時計劃，Sentry One Plan Explorer 可以更容易地看出一些“昂貴”的操作符根本沒有執行（它們“變灰”）：

我有一段時間理解為什麼這個查詢需要 0.02 秒才能執行

我不確定您是如何在這裡測量時間的，但執行計劃表明整個查詢的執行時間不到一毫秒，編譯時間為 9 毫秒。請參閱計劃 XML 的摘錄：

<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="9" CompileCPU="9" CompileMemory="736">
...
<QueryTimeStats CpuTime="0" ElapsedTime="0" />

Hannah 對查詢的一般改進有一些很好的建議，一定要檢查一下。但我認為也值得指出這一點。

您也許可以使用indexed view消除 TopN 排序，但我不確定此時的成本是否值得。

嘗試創建以下索引（我正在使用我認為類似於索引名稱命名約定的內容）：

CREATE INDEX DomainTable$DomainIP$IDNum
ON dbo.DomainTable (DomainIP, IDNum DESC);

CREATE INDEX theDB$idx001
ON dbo.theDB (IDNum DESC)
INCLUDE (theDate, [Status], Rate);

第一個索引允許 SQL Server 在 DomainIP 上進行查找，這將非常快，並且它包括 IDNum 列，按降序排列，這將消除查詢計劃中的鍵查找操作，並消除所需的降序排序。

第二個索引也可能允許 SQL Server 消除排序 - 看看它是否有助於或阻礙查詢執行。如果您為您的問題添加一個最小、完整且可驗證的範例，我將能夠為您進行測試。

無論如何，請考慮對您的查詢進行以下修改：

IF OBJECT_ID(N'tempdb..#statii', N'U') IS NOT NULL
BEGIN
   DROP TABLE #statii;
END
CREATE TABLE #statii 
(
   [status] varchar(20) NOT NULL
       PRIMARY KEY CLUSTERED
);

INSERT INTO #statii ([status])
VALUES ('Active')
   , ('To ReActivate')
   , ('To Deactivate')
   , ('Deactivate ASAP')
   , ('SUSPENDED')
   , ('SUSPENDED X')
   , ('SUSPENDED Y')
   , ('SUSPENDED Z');

SELECT TOP 1 theDB.IDNum
   , CONVERT(varchar(30), theDB.theDate, 22)
   , theDB.[Status]
   , theDB.Rate 
FROM theDB 
   INNER JOIN DomainTable ON theDB.IDNum = DomainTable.IDNum 
   INNER JOIN #statii s ON theDB.[status] = s.[status]
WHERE DomainTable.DomainIP = '127.0.0.1' 
ORDER BY theDB.IDNum DESC;

有幾點需要注意：

1. 我沒有使用該WHERE ... IN (...)子句，而是將這些值插入到臨時表中並加入它們。您可能不會注意到這種方法在性能上的巨大提升，但是隨著IN (...)子句中的項目列表變得越來越大，性能差異變得越來越明顯。IN (...)如果事實證明它會妨礙性能，您可能希望將其恢復為子句。我認為值得測試。
2. 不要使用FORMAT- 它很慢。在可能的情況下使用CONVERT或CAST。
3. 使用表別名，就像我為#statii表所做的那樣。
4. 大寫關鍵字，保持數據類型小寫。
5. 從不 1使用關鍵字作為列名。我看著你，status。該列應該命名為表BlahStatus的Blah名稱，或狀態的種類，或某事。

---

1 - 好的，也許永遠不會有點苛刻。幾乎從不。

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