OLTP系統中常見一些匯總需求，好比依據部門計算成本金額，或依據訂單和業務人員，計算每月每人業績金額...等不同維度，所以group by 語法開發人員絕對不陌生，但無法避免大量資料列掃描，如果資料量大且成本高於一般序列執行計畫，SQL Server會使用平行執行計畫來加速，因彙總無須關心資料順序，如以下執行計畫
SELECT [OrderID], [StockItemID] ,'Year'=year([LastEditedWhen])
FROM dbo.OrderLines
GROUP BY [OrderID], [StockItemID],year([LastEditedWhen])
針對上面簡單的SQL陳述句，可說無任何改寫的可能性，建立非叢集索引也無法大大提升效能，那是否有其他更簡單方法大幅提高查詢彙總效能?
如你使用SQL Server 2016以上版本(個人建議)，可建立Columnstore Index來改善需大量掃描資料列的查詢效能
上圖可看到Columnstore Index的高度資料壓縮(使用資料行壓縮，非資料列)、批次執行並最佳化記憶體作業，大幅減少I/O並改善查詢效能
Demo:
/*
💯RiCo技術學院
💯我在teachable和Udemy
https://bit.ly/ricoschool
輕鬆提高彙總效能
相關Sql Script都在個人RiCosNote
https://medium.com/ricos-note
*/
set statistics io,time on;
SELECT [OrderID], [StockItemID] ,'Year'=year([LastEditedWhen])
FROM dbo.OrderLines
GROUP BY [OrderID], [StockItemID],year([LastEditedWhen])
drop INDEX if exists [NCCX_dbo_OrderLines] ON dbo.[OrderLines]
go
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCX_dbo_OrderLines] ON dbo.[OrderLines]
(
[OrderID],
[StockItemID],
LastEditedWhen
)WITH (DROP_EXISTING = off, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [USERDATA]
GO
-- provides information about our segments
SELECT t.[Name] AS TableName,
i.[Name] AS IndexName,
c.[Name] AS ColumnName,
se.segment_id AS SegmentId,
se.row_count AS SegmentRowCount,
se.min_data_id AS MinRowValue,
se.max_data_id AS MaxRowValue
FROM [sys].[column_store_segments] AS se
INNER JOIN [sys].[partitions] AS p
ON p.hobt_id = se.hobt_id
INNER JOIN [sys].[indexes] AS i
on i.OBJECT_ID = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN [sys].[index_columns] AS ic
ON ic.OBJECT_ID = i.OBJECT_ID
AND ic.index_id = i.index_id
AND ic.index_column_id = se.column_id
INNER JOIN [sys].[columns] AS c
ON c.OBJECT_ID = p.OBJECT_ID
AND c.column_id = ic.column_id
INNER JOIN [sys].[tables] AS t
ON t.object_id = i.object_id
WHERE c.[Name] in ('OrderID','StockItemID','LastEditedWhen')
and t.[Name]='OrderLines'
and i.[Name]='NCCX_dbo_OrderLines';
GO
reference:
