使用視窗函式優化子查詢

由於我的性能調整技能似乎永遠不夠用，我總是想知道是否可以針對某些查詢執行*更多優化。*這個問題涉及的情況是嵌套在子查詢中的 Windowed MAX 函式。

我正在探勘的數據是各種大型集合組上的一系列事務。我有 4 個重要欄位，交易的唯一 ID，一批交易的組 ID，以及與相應唯一交易或交易組關聯的日期。大多數情況下，組日期與批次的最大唯一交易日期匹配，但有時會通過我們的系統進行手動調整，並且在擷取組交易日期後會發生唯一日期操作。此手動編輯不會按設計調整組日期。

我在此查詢中確定的是唯一日期在組日期之後的那些記錄。以下範例查詢建構了我的場景的大致等效項，並且 SELECT 語句返回我正在尋找的記錄，但是，我是否以最有效的方式接近此解決方案？這需要一段時間才能在我的事實表載入過程中執行，因為我的記錄計數高 9 位數字，但主要是我對子查詢的蔑視讓我想知道這裡是否有更好的方法。我並不擔心任何索引，因為我相信這些索引已經到位；我正在尋找的是一種替代查詢方法，它可以實現相同的目標，但效率更高。歡迎任何回饋。

CREATE TABLE #Example
(
   UniqueID INT IDENTITY(1,1)
 , GroupID INT
 , GroupDate DATETIME
 , UniqueDate DATETIME
)

CREATE CLUSTERED INDEX [CX_1] ON [#Example]
(
   [UniqueID] ASC
)


SET NOCOUNT ON

--Populate some test data
DECLARE @i INT = 0, @j INT = 5, @UniqueDate DATETIME, @GroupDate DATETIME

WHILE @i < 10000
BEGIN

   IF((@i + @j)%173 = 0)
   BEGIN
       SET @UniqueDate = GETDATE()+@i+5
   END
   ELSE
   BEGIN
       SET @UniqueDate = GETDATE()+@i
   END

   SET @GroupDate = GETDATE()+(@j-1)

   INSERT INTO #Example (GroupID, GroupDate, UniqueDate)
   VALUES (@j, @GroupDate, @UniqueDate)

   SET @i = @i + 1

   IF (@i % 5 = 0)
   BEGIN
       SET @j = @j+5
   END
END
SET NOCOUNT OFF

CREATE NONCLUSTERED INDEX [IX_2_4_3] ON [#Example]
(
   [GroupID] ASC,
   [UniqueDate] ASC,
   [GroupDate] ASC
)
INCLUDE ([UniqueID])

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT UniqueID
    , GroupID
    , GroupDate
    , UniqueDate
FROM (
   SELECT UniqueID
        , GroupID
        , GroupDate
        , UniqueDate
        , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate
   FROM #Example
   ) calc_maxUD
WHERE maxUniqueDate > GroupDate
   AND maxUniqueDate = UniqueDate

DROP TABLE #Example

dbfiddle在這裡

我假設沒有索引，因為您沒有提供任何索引。

馬上，以下索引將消除計劃中的排序運算符，否則可能會消耗大量記憶體：

CREATE INDEX IX ON #Example (GroupID, UniqueDate) INCLUDE (UniqueID, GroupDate);

在這種情況下，子查詢不是性能問題。如果有的話，我會考慮消除視窗函式（MAX…OVER）以避免嵌套循環和表假離線構造的方法。

使用相同的索引，下面的查詢乍一看可能效率較低，它確實對基表進行了 2 到 3 次掃描，但由於缺少 Spool 操作符，它在內部消除了大量讀取。我猜它的性能仍然會更好，特別是如果您的伺服器上有足夠的 CPU 核心和 IO 性能：

SELECT e.UniqueID
    , e.GroupID
    , e.GroupDate
    , e.UniqueDate
FROM (
   SELECT GroupID, MAX(UniqueDate) AS maxUniqueDate
   FROM #Example
   GROUP BY GroupID) AS agg
INNER JOIN #Example AS e ON agg.GroupID=e.GroupID
WHERE agg.maxUniqueDate > e.GroupDate
   AND agg.maxUniqueDate = e.UniqueDate
OPTION (MERGE JOIN);

（注意：我添加了一個MERGE JOIN查詢提示，但如果您的統計資訊正常，這可能會自動發生。最好的做法是盡可能留下這樣的提示。）

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