[SQL][Performance]在 SQL Server 2005/2008 版本中處理累加的問題
最近在跟朋友的討論中，看到他有說明一個很特別的案例，要來處理資料集按照某些排列順序做出累加，在早期 SQL Server 的版本中，多半會使用 temp table 或者是 cursor 的方式來做處理，而到了 SQL Server 2005 版本之後，由於資料庫引擎開始支援 CTE ( Common Table Expression ) - 此部分可以參考小弟另外一篇文章的說明。因此如果遇到累加的時候，透過 CTE 的協助就可以方便許多了。在此我先做一個簡單的範例來說明
首先先利用以下的語法產生一個測試用的 Table
SET NOCOUNT ON
IF OBJECT_ID('Items') IS NOT NULL DROP TABLE Items
DECLARE @Items TABLE
(
Item_Id varchar(10), Item_Name varchar(10), Sales_Qty numeric(10)
)
DECLARE @Ptr INT, @RecordCount INT
SET @Ptr = 0
SET @RecordCount = 5000 -- 範例表的筆數
WHILE @Ptr < @RecordCount
BEGIN
SET @Ptr += 1
INSERT INTO @Items
VALUES ( RIGHT('000000000'+LTRIM(STR(@Ptr)),10 ), RIGHT(newid(),10 ) , ROUND( RAND()*10000,1 ) )
END
SELECT * INTO Items FROM @Items -- 產生測試表格 Items
因此在 SQL Server 2005 之後我們可以利用以下的方式來進行
WITH ItemsByRank AS
(
SELECT *,RANK() OVER ( ORDER BY Sales_Qty DESC, Item_Id) AS Sales_Rank FROM Items
)
SELECT A.*,SUM(B.Sales_Qty) Sales_Cumulative FROM ItemsByRank A
JOIN ItemsByRank B ON A.Sales_Rank >= B.Sales_Rank
GROUP BY A.Item_Id,A.Item_Name,A.Sales_Qty,A.Sales_Rank
ORDER BY A.Sales_Rank
雖然這個方式不如 SQL Server 2012 的 Window Function 來的簡潔，但至少在 SQL Server 2005/2008 都還算是可以接受的方式
-- SQL Server 2012 Window Function
SELECT *,
RANK() OVER ( ORDER BY Sales_Qty DESC) AS Sales_Rank,
SUM(Sales_Qty) OVER ( ORDER BY Sales_Qty DESC) AS Sales_Cumulative
FROM Items
但如果把時間也考量進去，那差異可就大了，在資料筆數少的時候還沒有太大的感覺，但如果資料筆數多的狀況下，那時間隨著資料筆數呈現平方倍數的增加
|
資料數 |
1,000 |
10,000 |
20,000 |
30,000 |
50,000 |
|
CTE |
1s |
25s |
79s |
117s |
45m |
|
Window |
1s |
1s |
1s |
1s |
1s |
而就這麼湊巧，這幾天就剛好遇到一個案件，他的實際資料大約有 90,000 筆資料，而這些資料必須要按照 2 種不同的欄位排序，並且針對排序的結果將商品銷售數量做累加，就算他已經用了非常高檔的設備，整個計算下來還是要花費將近 2hr 的時間，因此希望能在不要升級 SQL Server 的狀況下，找出解決的辦法。
IF OBJECT_ID('tempdb..#Items_with_rank') IS NOT NULL DROP TABLE #Items_with_rank
DECLARE @mock TABLE( rank_index numeric(10) primary key , amount numeric(21) )
-- 建立一個存放排序後的資料暫存 Table , 因為 Table 變數不支援 Index, 所以使用暫存 Table
CREATE TABLE #Items_with_rank( Item_Id varchar(10), Sales_Qty numeric(21), Sales_rank INT primary key );
CREATE INDEX X1 ON #Items_with_rank(Item_Id)
-- 產生按照銷售數量排序的暫存表
INSERT INTO #Items_with_rank
SELECT Item_Id, Sales_Qty, RANK() OVER ( ORDER BY Sales_Qty DESC,Item_Id ) FROM Items ;
declare @ptr numeric(10);
declare @max numeric(10);
select @max=count(*) from Items ;
-- 第一筆資料不用考慮累加
insert into @mock select Sales_rank,Sales_Qty from #Items_with_rank where Sales_rank = 1
set @ptr = 1;
while ( @ptr < @max )
begin
set @ptr = @ptr + 1
-- 利用前一筆累計的數量加上自己的數量，算出到自己的累計值
insert into @mock select Sales_rank,Sales_Qty+amount from #Items_with_rank join @mock on Sales_rank-1 =rank_index where Sales_rank = @ptr
end
-- 列出累加結果
select A.*,C.amount
from Items A
join #Items_with_rank B ON A.Item_Id = B.Item_Id
join @mock C on B.Sales_rank = C.rank_index
order by B.Sales_rank
當採用這樣的方式時，50,000 筆資料計算需要 15sec，雖然不如直接採用 Window Function 的 1sec ，但相對採用 CTE 需要 45mins 要來的好太多了。因此如果您有大量資料需要做到類似的處理時，又沒有辦法採用 SQL Server 2012 以上的版本，那麼可以試試看上述的範例，利用類似 Cursor 的方式來循序處理，這樣在大量資料時會比兩個大資料表相互 JOIN 在做計算的效果還要好上太多了。