相同邏輯的不同表現形式會產生非常不同的執行時間

February 16, 2019

假設該widgets_population表記錄了每個已下線的小元件，而該widgets_tested表是已測試的子集。
對於總體中的每個小元件，我們想要確定最近的測試。
下面的兩個查詢顯示了解決方案的兩種不同方法。
然而，第一個查詢像閃電一樣執行，而第二個查詢似乎永遠旋轉。為什麼？第二個查詢是否有明顯的劣勢，或者這只是優化器僥倖之一？
我們意識到在連接謂詞中存在不等式並不是最優的，因為它不能利用預排序和合併，但在我們看來，相關子查詢同樣繁重，因為它必須為每個查詢執行內部查詢外部查詢的行。因此，在我們看來，這兩個查詢應該同樣低效。
SELECT   A.serial_ID,
        (SELECT MAX(B.serial_ID) FROM widgets_tested AS B 
            WHERE A.serial_ID &gt;= B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
;




SELECT   A.serial_ID,
        MAX(B.serial_ID) AS [ID of most recent test]
FROM     widgets_population AS A
        INNER JOIN
        widgets_tested AS B
        ON A.serial_ID &gt;= B.serial_ID
GROUP BY A.serial_ID
;
注意：可能無法提供有關相關表的 DDL 語句和執行計劃的更多資訊，因為這些widgets表只是原始表的已清理替身。我們只是想問問社區，第二個查詢的邏輯結構是否有明顯古怪或低效的地方。

由於您不允許共享有關查詢的更多資訊，例如樣本數據 + 表定義，因此這些測試將基於自己的捏造數據，並且有或沒有索引。測試數據如下。
YMMV。
內連接 + group by 查詢
“壞”查詢將所有內容放入索引假離線，隨後在指數 8M 行上執行嵌套循環連接
整體計劃
索引假離線屬性。
作為連接的結果，該索引假離線創建了超過 8M 條記錄<=。將超過 8M 行的嵌套循環運算符放入流聚合中也會降低性能。
派生表
“更好”的計劃
在使用嵌套循環運算符之前使用流聚合。導致連接更小。流聚合運算符也有區別，這個支持MAX()函式，而另一個（上）支持GROUP BY語句 + 的 partialagg MAX()。
TL; 博士
我的猜測是，內部連接查詢計劃中使用的巨大嵌套循環運算符會導致時間過長。與好計劃的區別在於 Stream 聚合發生在嵌套循環運算符之前。同樣正如@ypercubeᵀᴹ 提到的，它們在邏輯上並不相似。
添加索引
CREATE INDEX IX_widgets_population_serial_ID on widgets_population(serial_ID);
CREATE INDEX IX_widgets_tested_serial_ID on widgets_tested(serial_ID);
使用 15 毫秒 cpu 時間，索引後“好”查詢得到很大改進
這種變化如此劇烈的原因是TOP現在使用運算符。
這個頂級運算符<=使用索引的排序順序為widgets_tested.
“壞”查詢，不是那麼多
GROUP BY在執行&之前執行連接MAX()，其中MAX()是在好計劃中的連接之前計算的。
本例使用的數據
CREATE TABLE  widgets_population(serial_ID int)

CREATE TABLE  widgets_tested(serial_ID int)

SET NOCOUNT ON
DECLARE @I INT = 1;
WHILE @I  &lt;= 1000
BEGIN
INSERT INTO  widgets_population(serial_ID)
VALUES(@I)
INSERT INTO  widgets_tested(serial_ID)
VALUES(@I)
SET @I+=1
END

INSERT INTO  widgets_population(serial_ID)
select serial_ID+1000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+1000 from widgets_tested

INSERT INTO  widgets_population(serial_ID)
select serial_ID+2000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+2000 from widgets_tested