MAX(Version) 的子選擇需要很長時間，儘管只有 ~20K 記錄

我有一個非常複雜的數據模型。如果不解釋正在建模的內容，我無法理解大多數 SQL 範例，因此我將嘗試解釋。

Mainlines -> Releases -> Overlays -> 校準 <- 參數

那麼，校準是 Mainline-Release-Overlay 鍊和參數的子項。這是我想要返回的集合。

現在，複雜性在於——為了節省空間——我們儲存基本 Mainline 的校準，然後只儲存在 Release 和 Overlay 級別發生更改時的差異。這會產生大約 16K 行的“基本”校準集，然後每個版本進行數百次更改，並且每個疊加層可能僅進行一些更改。發生參數刪除。為了跟踪這一點，Calibrations 有一個狀態欄位 (tinyint)，它設置為 1 以表示刪除。

疊加層會連續進行版本化。因此，為了獲得完整的“校準”，我們需要查詢 Calibrations 表以獲取參數數據的最新版本，直至特定的 Overlay 版本號。參數元數據可能會更改，但名稱保持不變，因此這些校準可能會引用不同版本的參數，儘管名稱相同。

到目前為止，以下內容已經完美且即時地執行（帶有以下警告）：

SELECT c.Parameter_ParameterID, p.Designation AS Name, c.Data, o.Version
FROM Calibrations c, Parameters p, Overlays o, Releases r
WHERE r.Mainline_MainlineID = 9
   AND o.Release_ReleaseID = r.ReleaseID
   AND c.Overlay_OverlayID = o.OverlayID
   AND c.Parameter_ParameterID = p.ParameterID
   AND o.Version =
       (SELECT MAX(o1.Version)
           FROM Parameters p1, Calibrations c1, Overlays o1, Releases r1
           WHERE r1.Mainline_MainlineID = 9
           AND o1.Release_ReleaseID = r1.ReleaseID
           AND c1.Overlay_OverlayID = o1.OverlayID
           AND c1.Parameter_ParameterID = p1.ParameterID
           AND p1.Designation = p.Designation  -- New condition
           AND o1.Version <= 68)
   AND c.Status != 1 -- Changed from boolean to tinyint
ORDER BY Version DESC, Name

自從我們開始（正確）跟踪參數版本以來，問題就出現了，因此參數的內部匹配不再可以通過 ParameterID，而是通過指定（即名稱）。起初這不是問題，但現在校準狀態不再是用於刪除的布爾值。現在也可以設置為2表示新引入，3表示重新插入。

添加這兩個更改導致此查詢需要 10-15 分鐘才能執行！這怎麼可能呢？即使我扭曲了查詢以將每一行連接到每一行，也不應該花這麼長時間！

這裡有兩件令人抓狂的事情。一個是，當這最初成為一個問題時，我在參數指定欄位上放置了一個索引，它使它再次工作。現在，刪除和重新創建它並沒有什麼不同。此外，還有一個特殊情況，如果我在數據庫中正好有兩條 Mainline，它仍然會立即執行。如果我只有一個或兩個以上，它需要永遠。

我嘗試過各種註釋部分，例如o1.Version <=條件和c.Status !=條件，有時它會再次起作用。我無法掌握各種情況下的差異，而且我已經走到了盡頭。我知道我距離讓它再次快速執行還有一個小技巧，但它顯然超出了我的能力範圍，這對我下個月的測試計劃來說是一個阻礙。

我已經程式了 35 年，但我以前從未像這樣處理過 SQL，而 SSMS 用於顯示解釋計劃的工具對我來說毫無意義。我想了解基本問題並得到解決。

長查詢的執行計劃。註釋掉時查詢的

執行計劃c.Status != 1

完整的分析需要訪問執行計劃、表和索引定義以及數據庫統計資訊（或數據庫本身的副本）。這可能是不現實的，所以這裡有一些一般性的觀察，以及一個可能的解決方案供您嘗試。（嚴格來說，這個問題可能超出了本網站的範圍。）

一般背景

SQL Server 查詢優化器是負責為查詢表示的邏輯結果規範選擇執行計劃的組件。即使是適度複雜的查詢，也有大量可能的物理計劃。優化器使用啟發式和成本估算從它探索的有限計劃空間中進行選擇。

提供給優化器的資訊質量（包括數據庫設計、索引和目前統計資訊的準確性）都對優化器選擇的計劃在實踐中表現良好的可能性有很大影響。如果設計是相關的、索引良好的並且具有代表性的統計數據，那麼好的計劃選擇將是常態。否則，所有賭注都取消。

有問題的查詢是中等複雜的，雖然它的意圖對大多數人來說是相當清楚的，但優化器將其視為與其他任何查詢一樣的 SQL 查詢。具有多個連接和聚合的相關子查詢MAX意味著潛在的計劃搜尋空間將很大，並且估計可能不准確（由於累積的錯誤，如果沒有別的）。優化器很可能最終選擇了一個計劃，如果它的假設得到證實，它會很好地工作，但在現實中可能會表現得很糟糕。

查詢和潛在的重寫

從廣義上講，您似乎在向 SQL Server 詢問每個指定的最高版本，並限製版本號。表達此查詢的另一種方法（可能具有更可預測的性能）是對行進行編號（分區和排序適當），然後返回每個組中排名第一的單行：

WITH MaxVersionPerDesignation AS
(
   -- Do the joins and number the rows
   -- per designation, in descending
   -- Version order
   SELECT 
       c.Parameter_ParameterID, 
       p.Designation AS Name, 
       c.Data, 
       o.[Version],
       rn = ROW_NUMBER() OVER (
           PARTITION BY p.Designation
           ORDER BY o.[Version] DESC)
   FROM dbo.Releases AS r
   JOIN dbo.Overlays AS o
       ON o.Release_ReleaseID = r.ReleaseID
   JOIN dbo.Calibrations AS c
       ON c.Overlay_OverlayID = o.OverlayID
   JOIN dbo.[Parameters] AS p
       ON p.ParameterID = c.Parameter_ParameterID
   WHERE 
       r.Mainline_MainlineID = 9
       AND c.[Status] <> 1
       AND o.[Version] <= 68
)
-- Return the row with the highest Version per designation
SELECT
   MVPD.Parameter_ParameterID,
   MVPD.Name,
   MVPD.Data,
   MVPD.[Version]
FROM MaxVersionPerDesignation AS MVPD
WHERE
   -- Row #1 per Designation
   MVPD.rn = 1
ORDER BY 
   MVPD.[Version] DESC, 
   MVPD.Designation;

希望上面 SQL 中的邏輯相當容易理解。

上傳計劃分析

查詢優化器生成一個執行計劃，該計劃嘗試對每外行執行一次子查詢。如果外部查詢只產生一行，這將是一個好主意，但遺憾的是它在執行時限定了 16,794 行（使用SQL Sentry Plan Explorer擷取的執行計劃）：

這種錯誤估計是您的問題的根本原因。最有可能的是，基表上的統計資訊不能代表目前數據。您應該刷新這些統計數據並製定一個持續的計劃以確保統計數據保持合理的最新狀態。錯誤的 1 行估計的另一個副作用是優化器認為整個查詢的成本非常低，幾乎是微不足道的，因此不會花很長時間檢查備選方案。

無論如何，子查詢（除了相關性和額外的謂詞外，與外部查詢非常相似）完全執行了 16,794 次（每次使用不同Designation的值）。Version這當然是一個糟糕的策略（但對於 1 個外行來說就可以了）。

累積效果是生成超過 2.82 億行（在所有 16k 次迭代中）。這導致上面顯示的計劃片段中的 Key Lookup 執行了 2.82 億次。後一個事實可能是性能不佳的主要原因，儘管在任何情況下執行相關子查詢 16,794 次都不會很快（如果優化器知道 16,794 行將驅動它，它就不會選擇此策略）。

使用最佳索引（在比我現在可以做的更詳細的分析之後），可以使用CROSS APPLYandTOP 1而不是MAX（請參閱此相關問題）重寫查詢以獲得可能的最佳性能，但您可能會找到ROW_NUMBER替代方案（再次，請參閱相關問題）表現足夠好，具有良好的計劃選擇穩定性。

鍵查找：

您可以通過在索引中包含Parameter_ParameterID、Data和Status列來消除鍵查找（在長計劃中） 。IX_Overlay_OverlayID例如：

CREATE INDEX [IX_Overlay_OverlayID]
ON dbo.Callibrations (Overlay_OverlayID)
INCLUDE (Parameter_ParameterID, Data, Status)
WITH (DROP_EXISTING = ON);

這可能不會導致優化器在不更新統計資訊的情況下選擇更好或更穩定的計劃，但我不提它是我的疏忽。

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