UNION 与 SELECT DISTINCT 和 UNION ALL 性能 [英] UNION versus SELECT DISTINCT and UNION ALL Performance

问题描述

这两者在性能方面有什么区别吗?

Is there any difference between these two performance-wise?

-- eliminate duplicates using UNION
SELECT col1,col2,col3 FROM Table1 
UNION SELECT col1,col2,col3 FROM Table2 
UNION SELECT col1,col2,col3 FROM Table3 
UNION SELECT col1,col2,col3 FROM Table4 
UNION SELECT col1,col2,col3 FROM Table5       
UNION SELECT col1,col2,col3 FROM Table6       
UNION SELECT col1,col2,col3 FROM Table7       
UNION SELECT col1,col2,col3 FROM Table8       

-- eliminate duplicates using DISTINCT    
SELECT DISTINCT * FROM
(     
    SELECT col1,col2,col3 FROM Table1 
    UNION ALL SELECT col1,col2,col3 FROM Table2 
    UNION ALL SELECT col1,col2,col3 FROM Table3 
    UNION ALL SELECT col1,col2,col3 FROM Table4 
    UNION ALL SELECT col1,col2,col3 FROM Table5       
    UNION ALL SELECT col1,col2,col3 FROM Table6       
    UNION ALL SELECT col1,col2,col3 FROM Table7       
    UNION ALL SELECT col1,col2,col3 FROM Table8       
) x   

推荐答案

Union 和 Union all 的区别在于 UNION ALL不会消除重复的行，而是从所有符合查询细节的表中提取所有行并将它们组合到一个表中.

The difference between Union and Union all is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

UNION 语句有效地对结果集执行 SELECT DISTINCT.

A UNION statement effectively does a SELECT DISTINCT on the results set.

如果选择 Distinct from Union All 结果集，则输出将等于 Union 结果集.

If you select Distinct from Union All result set, Then the output will be equal to the Union result set.

CPU 成本性能:

让我用例子来解释:

我有两个疑问.一个是 Union 另一个是 Union All

I have two queries. one is Union another one is Union All

SET STATISTICS TIME ON
GO
 
select distinct * from (select * from dbo.user_LogTime
union all
select * from dbo.user_LogTime) X 
GO

SET STATISTICS TIME OFF

SET STATISTICS TIME ON
GO
 
select * from dbo.user_LogTime
union
select * from dbo.user_LogTime
GO

SET STATISTICS TIME OFF

我确实在 SMSS 的同一个查询窗口中运行了两者.让我们看看 SMSS 中的执行计划:

I did run the both in same query window in SMSS. Lets see the Execution Plan in SMSS:

结果是，使用Union All 和Distinct 的查询将比使用Union 的查询花费更多的CPU 成本.

What happens is, The query with Union All and Distinct will take CPU cost more than Query with Union.

准时表现:

UNION ALL:

(1172 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 39 ms.

UNION:

(1172 row(s) affected)

SQL Server Execution Times:
   CPU time = 10 ms,  elapsed time = 25 ms.

所以Union在性能方面

这篇关于UNION 与 SELECT DISTINCT 和 UNION ALL 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！