為什麼這個 CTE 會立即返回大部分結果，但需要幾分鐘才能完成？

我有一個大致這樣的模式（這是我實際模式的簡化）：

CREATE TABLE foo (
   key1 NUMERIC(6) NOT NULL,
   key2 VARCHAR(32) NOT NULL,
   val VARCHAR(255) NULL,
   CONSTRAINT foo_pk PRIMARY KEY (key1, key2) --PK on key1, key2
   )
GO
CREATE TABLE bar (
   key1 NUMERIC(6) NOT NULL,
   key2 VARCHAR(32) NOT NULL,
   val VARCHAR(255) NULL,
   CONSTRAINT bar_pk PRIMARY KEY (key1, key2) --PK on key1, key2
   )
GO
CREATE TABLE aliases (
   id VARCHAR(32) NOT NULL PRIMARY KEY,
   text VARCHAR(255) NOT NULL,
   CONSTRAINT aliases_uk UNIQUE (text) --PK on id, unique constraint on text
   )
GO

INSERT INTO aliases (id, text)
SELECT REPLACE(NEWID(), '-', ''), 'Text1' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text2' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text3' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text4' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text5' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text6' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text7' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text8' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text9' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text10' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text11' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text12' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text13' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text14' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text15' UNION ALL
SELECT REPLACE(NEWID(), '-', ''), 'Text16';
GO

BEGIN
   DECLARE @i INT = 1;
   WHILE (@i <= 234)
   BEGIN
       INSERT INTO foo (key1, key2, val) SELECT @i, id, id FROM aliases;
       SET @i = @i + 1;
   END;
   INSERT INTO bar (key1, key2, val) SELECT key1 + 234, key2, val FROM foo;
END;
GO

我想獲取所有key1和key2對的列表（使用使用者友好的名稱s 連接key2使用者友好的別名vals 。我最初的嘗試如下所示：

WITH
foos_and_bars (key1, key2, val) AS (
   SELECT key1, key2, val FROM foo UNION ALL
   SELECT key1, key2, val FROM bar),
texts (key1, key2_name, val_text, rnum) AS (
   SELECT key1, a1.text, a2.text, ROW_NUMBER() OVER (PARTITION BY key1, key2 ORDER BY val)
   FROM foos_and_bars
   JOIN aliases a1 ON a1.id = foos_and_bars.key2
   LEFT JOIN aliases a2 ON a2.id = foos_and_bars.val),
partitioned (key1, key2_name, val_text, rnum, maxnum) AS (
   SELECT key1, key2_name, val_text, rnum, MAX(rnum) OVER (PARTITION BY key1, key2_name)
   FROM texts),
recurse (key1, key2_name, val_texts, rnum, maxnum) AS (
   SELECT key1, key2_name, val_text, rnum, maxnum
   FROM partitioned
   WHERE rnum = 1
   UNION ALL
   SELECT r.key1,
       r.key2_name, 
       CAST(r.val_texts + CHAR(13) + CHAR(10) + p.val_text AS VARCHAR(255)),
       p.rnum,
       r.maxnum
   FROM recurse r
   JOIN partitioned p
   ON p.key1 = r.key1
   AND p.key2_name = r.key2_name
   AND p.rnum = r.rnum + 1)
SELECT * FROM recurse WHERE rnum = maxnum;

它起作用了，我在不到一秒的時間內得到了 7480 行，但是 SSMS 執行了不少於 2 分 40 秒，最後 8 行令人費解。這些最後幾行並不比第一行複雜（事實上，系統中的數據，rnum並且maxnum永遠不會大於 1），並且由於這些表是專門為模擬這個 SE 問題而創建的，因此不應該有鎖。

問題似乎僅限於遞歸 CTE，因為從上到下選擇partitioned在不到一秒的時間內產生 7488 行。CTE 可能會遇到什麼問題？

執行計劃（使用與簡化模式匹配的設置）：

• 計劃所有記錄
• 只有一個鍵的記錄1

輸出將進入一個視圖，該視圖將支持一個 Sybase 視窗，該視窗將用於搜尋記錄。因此，它需要能夠檢索所有行，儘管程序預設一次提取 1000 行。

遞歸 SQL 查詢的錨部分生成所有 7488 行。在我的機器上，這部分查詢在 100 毫秒內完成。SSMS 不會立即顯示網格結果中的所有 7488 行。它也只為我顯示 7480。我懷疑發生這種情況是因為結果是以數據包的形式發送的，而剩餘的 8 行不足以填滿一個數據包。

與前半部分相比，查詢的後半部分非常昂貴。查看執行計劃，您可以在嵌套循環的內側看到六個聚集索引掃描。這些掃描將對錨部分的每一行至少執行一次。SQL Server 在幾分鐘內總共進行了 44928 次掃描，最終從該部分生成 0 行。一旦這部分執行完成，SQL Server 會將剩餘的行數據包發送到客戶端，您會在已完成查詢的結果網格中看到所有 7488 行。找到最後 8 行 8 行需要 3 分鐘是不正確的。它幾乎立即找到它們。發送它們只需三分鐘。

通過查看實際計劃，您可以看到查詢的下半部分執行了大量工作。注意箭頭的粗細和掃描操作符的執行次數：

這就是查詢緩慢的原因。我沒有任何建議讓你加快速度，因為我不明白你想用這個查詢來完成什麼。

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