用子查詢替換長 GROUP BY 列表

October 14, 2019

這是我在 Stack Overflow 上的問題的轉貼。他們建議在這裡問：
我發現了一篇 2005 年的線上文章，作者聲稱，許多開發人員錯誤地使用了 GROUP BY，您最好將其替換為子查詢。
我已經在我的一個查詢中對其進行了測試，我需要根據另一個表中的連接條目數對搜尋結果進行排序（更常見的應該首先出現）。我最初的經典方法是在一個公共 ID 上連接兩個表，按選擇列表中的每個欄位分組，並按子表的計數對結果進行排序。
現在，來自連結部落格的 Jeff Smith 聲稱，您應該更好地使用子選擇，它完成所有分組，而不是加入該子選擇。檢查這兩種方法的執行計劃時，SSMS 指出，大分組需要 52% 的時間，而子選擇需要 48% 的時間，因此從技術角度來看，子選擇方法似乎實際上稍微快了一點。但是，“改進”的 SQL 命令似乎生成了更複雜的執行計劃（就節點而言）
你怎麼看？您能否詳細說明在這種特定情況下如何解釋執行計劃以及哪個通常是更可取的選項？
SELECT
   a.ID,
   a.ID_AddressType,
   a.Name1,
   a.Name2,
   a.Street,
   a.Number,
   a.ZipCode,
   a.City,
   a.Country
FROM dbo.[Address] a
INNER JOIN CONTAINSTABLE(
   dbo.[Address],
   FullAddress,
   '"ZIE*"',
   5
) s ON a.ID = s.[KEY]
LEFT JOIN dbo.Haul h ON h.ID_DestinationAddress = a.ID
GROUP BY
   a.ID,
   a.ID_AddressType,
   a.Name1,
   a.Name2,
   a.Street,
   a.Number,
   a.ZipCode,
   a.City,
   a.Country,
   s.RANK
ORDER BY s.RANK DESC, COUNT(*) DESC;

SELECT
   a.ID,
   a.ID_AddressType,
   a.Name1,
   a.Name2,
   a.Street,
   a.Number,
   a.ZipCode,
   a.City,
   a.Country
FROM dbo.[Address] a
INNER JOIN CONTAINSTABLE(
   dbo.[Address],
   FullAddress,
   '"ZIE*"',
   5
) s ON a.ID = s.[KEY]
LEFT JOIN (
   SELECT ID_DestinationAddress, COUNT(*) Cnt
   FROM dbo.Haul
   GROUP BY ID_DestinationAddress
) h ON h.ID_DestinationAddress = a.ID
ORDER BY s.RANK DESC, h.Cnt DESC;

如果您將左連接更改dbo.Haul為子查詢，它將ID_DestinationAddress在從掃描中獲取數據後直接計算（流聚合）的這些不同值併計算它們（計算標量）。
這是您在執行計劃中看到的：
然而，當使用該GROUP BY方法時，它只是在數據通過和之間的左連接後進行dbo.Haul分組dbo.[Address]。
好多少取決於 dbo.Haul 的獨特價值比率。更少的唯一值意味著第二個執行計劃的結果更好，因為左連接必須處理更少的值。
第二個查詢的另一個正面結果是只ID_DestinationAddress計算了唯一性，而不是group by中所有列作為一個整體的唯一性。
同樣，您應該測試和驗證查詢、數據集和索引的結果。測試您是否不熟悉執行計劃的一種方法是SET STATISTICS IO, TIME ON;在執行查詢之前進行設置，並通過將這些執行時統計資訊粘貼到諸如statisticsparser之類的工具中來使它們更具可讀性。
測試
一個小測試來顯示數據的差異可以為這些查詢做些什麼。
如果該dbo.Haul表與 FULLTEXT 索引過濾返回的 5 條記錄沒有多少匹配，則差異不是那麼大：
按查詢計劃分組
子查詢查詢計劃
可以提前過濾 1000 行，但是在我的機器上，這兩個查詢的執行時間大約為15 毫秒。
dbo.Haul現在，如果我更改我的數據，使這 5 條記錄在左連接上有更多匹配項：
group by query的區別
   &lt;QueryTimeStats CpuTime="1564" ElapsedTime="1566" /&gt;
並且子查詢變得更加清晰
和統計數據：
&lt;QueryTimeStats CpuTime="680" ElapsedTime="690"/&gt;