2016-01-29

效能調教：請使用UNION ALL，不用 UNION

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

先讓我們看看UNION與UNION 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千筆