德瑞克：SQL Server 學習筆記: 2018/06

使用 Group By，已是耗費資源的動作。若再搭配函數等運算，勢必耗損更多系統資源。

Rewrite and optimize 優化改寫為多段式 Group By 來提升效能

藉由多段式 Group By：

第 1 段 Group By，並未搭配函數等運算，相對輕量地執行 Group By 後，縮減資料列筆數。
第 2 段 Group By，雖然搭配函數等耗用資源的運算，但要處理資料量卻是遠小於原本的資料表。

多段式的 Group By 雖然稍為增加 T-SQL 陳述式的複雜性，但卻確實有提升效能。

項目	單一 Group By	多段式 Group By	比較
T-SQL 陳述式	簡單	稍嫌複雜
Elapsed Time(sec)	0.196690000	0.127646000	節省 35%
CPU Time(sec)	0.018490000	0.013201000	節省 29%
Subtree Cost	0.405689	0.182843	節省 55%

-- Rewrite and optimize

本文包含討論 2 個系統內部的暫存資料表: 'Worktable' 與 'Workfile'

Workfile

用來儲存 hash joins (雜湊聯結) 與 hash aggregates (雜湊彙總) 的暫存結果。

Worktable

用來儲存 Query spool (查詢多工緩衝處理)、lob 變數、XML 變數與 Cursor (資料指標) 的暫存結果。
例如： GROUP BY、 ORDER BY或 UNION 查詢等。

[Performance Tuning] Multi-section of Group By clause

01. 檢視資料表內的資料

Table: Sales.SalesOrderHeader
Column: SalesPersonID, OrderDate, SubTotal
rows: 31465

-- figure 01_Look_at_ data

02. 檢視資料表上的 Index 資訊

composite index with Included Columns: IX_SalesPersonID_OrderDate_Temp

[SalesPersonID] , [OrderDate]
內含資料行的索引(Indexes with Included Columns): ([SubTotal])

-- figure 02_Index_Information

03. 彙總: 各個月份的小計(Sub Total)

GROUP BY 這 3 個：

SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
使用到 2 個資料行： SalesPersonID, OrderDate
其中，OrderDate 都有搭配使用日期函數。以此分組來取得年、月的匯總資料。

-- figure 11_Group_By_Year_Month_OrderDate

04. 檢視 Execution Plan

Subtree Cost: 0.405689
rows: 461
主要耗用資源的 Operator(運算子)是: Index Scan(31%) + Hash Match(63%)

請留意：

Operator: Hash Match(Aggregate)，占用 Execution Plan 整體的 63% 資源。

若要優化效能，該如何下手？

沒有 Missing Index 的輔助建議，還能做什麼？

-- figure 12_Hash_Match_Aggregate_ExecutionPlan

05. 觀察 Performance Data

LastElapsedTime(sec):　0.196690000
LastCPUTime(sec): 0.018490000
LastLogicalReads: 119
LastPhysicalRead: 125
LastRows: 461

-- figure 13_Performance_Data

06. 檢視 STATISTICS IO, TIME 輸出的資訊:

有 2 個系統內部的暫存資料表: Table 'Worktable' 與 Table 'Workfile'

認識 Workfiles

用來儲存 hash joins(雜湊聯結) 與 hash aggregates(雜湊彙總) 的暫存結果。
work files could be used to store temporary results for hash joins and hash aggregates.

Hash joins(雜湊聯結) 用於處理大型、未排序、無索引的輸入。

在複雜查詢下，Hash joins 有利於 intermediate results(中繼結果) 的使用：

一般而言，中繼結果是沒有索引 (除非有明確地儲存到磁碟，然後建立索引)。

通常產生時也不會做適當的排序供 Execution Plan 的下一個作業使用。

Query optimizer 只估計中繼結果的大小。

因為複雜查詢的估計值可能非常不準確，所以處理中繼結果的演算法必須要有效率，
而且萬一中繼結果顯著大於預期時，它的效能還不得惡化得太明顯。

若有 Index 可用，資料已經排序，將可避免使用 Hash joins，避免產生 Workfiles 。

認識 Worktables (中繼工作資料表)

用來儲存 query spool (查詢多工緩衝處理)、lob 變數、XML 變數與 Cursor (資料指標) 的暫存結果。
work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

關聯式引擎在執行 SQL 陳述式中所指定的邏輯作業前，可能需要先建立一個 Worktables。
Worktables 屬於內部資料表，可用來保存中繼結果。
Worktables 會針對特定的 GROUP BY、 ORDER BY或 UNION 查詢而產生。

例如，如果 ORDER BY 子句會參考不在任何索引範圍內的資料行，則關聯式引擎可能需要產生 Worktables，根據所要求的順序來排序結果集。

Worktables 有時候也當作多工緩衝處理使用，可暫時保存執行部份查詢計畫的結果。
Worktables 建置於 tempdb 中，並且會在不再需要時自動卸除。

-- figure 14_STATISTICS IO_TIME

Multi-section of Group By clause 多段式 Group By

01. 改寫為多段式的 Group By

第 1 段 Group By

使用 2 個資料行： SalesPersonID, OrderDate。
但 OrderDate 沒有函數運算。

第 2 段 Group By

使用 3 個資料行： SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
OrderDate 資料行，皆使用函數運算。依據年、月來分組。

-- figure 21_Multi-section of Group By clause

02. 觀察 Execution Plan，與先前已有所不同：

Subtree Cost 降低為: 0.182843
rows: 461
主要耗用資源的 Operator(運算子)是: Index Scan(68%) + Sort (21%)

請留意：

已不復見 Operator: Hash Match(Aggregate)

-- figure 22_ExecutionPlan

03. 觀察 Performance Data

LastElapsedTime(sec):　0.127646000
LastCPUTime(sec): 0.013201000
LastLogicalReads: 119
LastPhysicalRead: 125
LastRows: 461

比較先前的 GROUP BY 陳述式，LastElapsedTime(sec) 與 LastCPUTime(sec) 都降低了。

-- figure 25_Performance_Data

04. 檢視 STATISTICS IO, TIME 輸出的資訊:

僅使用到 1 個系統內部暫存資料表: 'Worktable'

已省去使用 Workfiles

Workfiles 用於存放 hash joins 與 hash aggregates 的暫存結果。
改寫為多段式 Group By，節省系統資源，省去使用 Workfiles。

-- figure 26_STATISTICS IO_TIME

05. 檢視第 1 段 Group By

使用到 2 個資料行： SalesPersonID, OrderDate
但 OrderDate 沒有函數運算。
原本資料表有 31,465 rows，經過 GROUP BY 分組後，已縮減輸出為 1,592 rows。

資料列筆數減量 95% ，只剩下原來的 5%。

-- figure 23_First_section

06. 檢視第 1 段 Group By 的 Execution Plan。

-- figure 24_First_section_Execution Plan

07. 比較兩者

-- figure 41_Compare_TSQL

-- figure 42_Plan_Explorer

-- figure 43_Single_Group_By_Top_Operations

-- figure

-- figure 31_Compare_Execution Plan

結論

多段式的 Group By 雖然稍為增加 T-SQL 陳述式的複雜性，但卻確實有提升效能。

項目	單一 Group By	多段式 Group By	比較
T-SQL 陳述式	簡單	稍嫌複雜
Elapsed Time(sec)	0.196690000	0.127646000	節省 35%
CPU Time(sec)	0.018490000	0.013201000	節省 29%
Subtree Cost	0.405689	0.182843	節省 55%

多段式 Group By 的說明如下：

第 1 段 Group By

使用到 2 個資料行： SalesPersonID, OrderDate
但 OrderDate 沒有函數運算。
原本資料表有 31,465 rows，經過 GROUP BY 分組後，已縮減輸出為 1,592 rows。

資料列筆數減量 95% ，只剩下原來的 5%。

第 2 段 Group By

對少量筆數的資料，執行 Group By

對資料表執行 Group By，已是耗費資源的動作。若再搭配函數等運算，勢必耗損更多系統資源。

藉由多段式 Group By：

第 1 段 Group By，並未搭配函數等運算，相對輕量地執行 Group By 後，縮減資料列筆數。
第 2 段 Group By，雖然搭配函數等耗用資源的運算，但要處理資料量卻是遠小於原本的資料表。

Sample Code

20180623_Multi-section of Group By
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

德瑞克：SQL Server 學習筆記

搜尋本站文章

2018-06-23

[Performance Tuning] Multi-section of Group By clause, 使用多段式 Group By 來提升效能

搜尋本站文章

2018-06-23

[Performance Tuning] Multi-section of Group By clause, 使用 多段式 Group By 來提升效能

[Performance Tuning] Multi-section of Group By clause, 使用多段式 Group By 來提升效能