UNION VS. UNION ALL 的效能

以前並沒有特別在意 UNION 與 UNION ALL 對於 T-SQL 效能的影響，直到最近 UNION 上萬筆資料後，才深深發現這小小的差異，竟還有很深的學問。

如果將 TableA UNION TableB，得到的結果，會類似將 TableA 與 TableB 的資料匯總之後再進行 Distinct 。所以 UNION 後的結果是不會重複的。

至於另一個 UNION ALL 呢？ TableA UNION ALL TableB ，所得到的會是 TableA 與 TableB 的資料匯總，即便有重複資料，也都會被保留。

解釋過 UNION 與 UNION ALL 後，可以知道 UNION 比人家多了一個 Distinct 的動作，而這一額外動作就會對整個查詢效能造成影響，尤其是當你的資料量很大時，差異就會更明顯。

舉例來說，我建了2個暫存資料表，分別各放幾筆測試資料。

create table #tmp1 (t_Name varchar(30))
create table #tmp2 (t_Name varchar(30))
 
insert into #tmp1(t_Name) values ('paladin')
insert into #tmp1(t_Name) values ('hugo')
insert into #tmp1(t_Name) values ('ken')
insert into #tmp1(t_Name) values ('lillian')
insert into #tmp1(t_Name) values ('panda')
 
insert into #tmp2(t_Name) values ('polly')
insert into #tmp2(t_Name) values ('keen')
insert into #tmp2(t_Name) values ('hana')
insert into #tmp2(t_Name) values ('gemma')

select t_Name from #tmp1
union
select t_Name from #tmp2
 
 
select t_Name from #tmp1
union all
select t_Name from #tmp2

透過「顯示估計執行計畫」，可以得到下面的結果：

透過執行計畫可以瞭解， UNION 多了一個相異排序(Distinct Sort) 動作，這額外的動作，致使他的執行效能比 UNION ALL 慢了些。但是自己實際遇到的資料量，遠比這幾筆測試資料多太多了。於是修改了測試資料的筆數，並將測試數據列述如後。

declare @i_count int
set @i_count=0
 
while (@i_count<100000)  -- 分別用 1000,10000,100000
begin
 insert into #tmp1(t_Name) values ('paladin@@')
 insert into #tmp2(t_Name) values ('panda@@')
 set @i_count=@i_count+1
end

當測試資料達10萬筆之後，兩種語法的效能相差了 3 倍之多。

所以，如果您所要 UNION 的兩個資料表並不會有重複資料或是本身您並不在意有重複資料，改用 UNION ALL 會大大提昇整體 SQL 的查詢效率。

參考：

01.SQL SERVER – Union vs. Union All – Which is better for performance?

http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/

02.The effects UNION in a SQL query

http://webbtechsolutions.com/2009/08/06/the-effects-union-in-a-sql-query/