搜尋本站文章

2016-01-29

效能調教:請使用UNION ALL,不用 UNION

以效能觀點,請一律使用 UNION ALL


先讓我們看看UNIONUNION ALL的說明。


UNION ALL
將所有資料列納入結果中。其中包括複本。若未指定,就會移除資料列複本。
UNION
指定組合多個結果集,以及當做單一結果集傳回。會移除資料列複本。

也就是說,若使用UNION,就會額外耗用系統資源,去逐一比對每一筆資料列!

除非,真的需要耗用系統資源去移除重複的資料列,否則就不該使用UNION

請參考以下的SQL陳述式:

--EX1. UNION,移除重複項的資料列
SELECT sid, myDate FROM tblUnion01
UNION
SELECT sid, myDate FROM tblUnion02
GO

--EX2. UNION ALL,不移除重複項的資料列
SELECT sid, myDate FROM tblUnion01
UNION ALL
SELECT sid, myDate FROM tblUnion02
GO


我們分別匯入2千筆、20萬筆資料列來做討論:


以兩千筆資料列為例

SQL陳述式
查詢成本(相對於批次)
實驗筆數
UNION
85%
2,000
UNION ALL
15%
2,000

以二十萬筆資料列為例

SQL陳述式
查詢成本(相對於批次)
實驗筆數
UNION
79%
200,000
UNION ALL
21%
200,000

經由上述的測試結果,使用UNION,就會額外耗用系統資源。
再與UNION ALL比較起來,UNION硬是多耗用4倍的系統資源。

也就是說,如果真的需要耗用系統資源去移除重複的資料列,否則就不該使用 UNION

 分析執行計畫

我們進一步來觀察其所使用的執行計畫:

以兩千筆資料列為例



1:執行計畫--兩千筆資料列

在圖1中,觀察使用UNION陳述式的執行計畫,額外多使用「排序(相異排序)」運算子,光這個運算子就耗用了查詢成本(相對於批次)82%,真是個耗用資源的運算子。


2:「排序(相異排序)」運算子

在圖2中,進一步觀察「排序(相異排序)」運算子,可以看到「估計的CPU成本」是:0.0747874,與「估計的I/O成本」:0.0112613來比較,這是極度耗用CPU資源的運算子,兩者相差七倍。

以二十萬筆資料列為例



3:執行計畫 -- 二十萬筆資料列

在圖3中,觀察使用UNION陳述式的執行計畫,額外多使用了多個運算子,我們以「雜湊比對(彙總)」運算子來討論:

「雜湊比對(彙總)」運算子


4:「雜湊比對(彙總)」運算子

在圖4中,使用「雜湊比對(彙總)」運算子,光這個運算子就耗用了查詢成本(相對於批次)66%,進一步觀察,可以看到這是完全只使用CPU資源的運算子,沒用到任何I/O成本。

結論


以效能觀點,請一律使用 UNION ALL。除非,真的需要耗用系統資源去移除重複的資料列,否則就不該使用UNION




請參考以下的範例程式碼:



-- 建立範例資料表:tblUnion01、tblUnion02,並分別新增20萬筆資料列到其中。
USE tempdb
GO
IF EXISTS(SELECT name FROM sysobjects  WHERE  name = N'tblUnion01' AND type = 'U') 
 DROP TABLE tblUnion01
GO
IF EXISTS(SELECT name FROM sysobjects  WHERE  name = N'tblUnion02' AND type = 'U') 
 DROP TABLE tblUnion02
GO
CREATE TABLE tblUnion01
(sid int,myDate datetime)
GO
CREATE TABLE tblUnion02
(sid int,myDate datetime)
GO
--
SET NOCOUNT ON
GO
DECLARE @CNT INT=1

WHILE @CNT <=200000
BEGIN
 INSERT tblUnion01 VALUES(@CNT, GETDATE());
 INSERT tblUnion02 VALUES(@CNT, GETDATE());

 SET @CNT +=1
END

--
INSERT tblUnion01 VALUES(9999999,GETDATE())

-- 以上是建立資料表






-- 觀察資料表

-- 分別擁有200,000筆資料列
SELECT COUNT(*) FROM tblUnion01; -- 200,001
SELECT COUNT(*) FROM tblUnion02; -- 200,000

--EX1. UNION,移除重複項的資料列
SELECT sid, myDate FROM tblUnion01
UNION
SELECT sid, myDate FROM tblUnion02
GO

--EX2. UNION ALL,不移除重複項的資料列
SELECT sid, myDate FROM tblUnion01
UNION ALL
SELECT sid, myDate FROM tblUnion02
GO




以兩千筆資料列的情境

-- 01_執行計畫_2千筆



-- 02_UNION_成本_0.104821



-- 03_UNION ALL_成本_0.0187726



-- 04_排序_相異排序_成本_0.0860484



以二十萬筆資料列的情境

-- 01_執行計畫_20萬筆



-- 02_UNION_成本_4.96574



-- 03_UNION ALL_1.29249



-- 04_雜湊比對_3.26904



-- 05_平行處理_成本_4.96574





UNION (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms180026(v=sql.120).aspx