I have to run a select statement across several tables. I am sure the tables return different records. I am anyway using UNION ALL.
我必须在几个表中运行一个select语句。我确信这些表会返回不同的记录。我总是使用UNION ALL。
Is it better to use UNION or of UNION ALL in performance terms when I am sure the tables return different records?
当我确定表返回不同的记录时，在性能方面使用UNION或UNION ALL更好吗？
5 个解决方案
#1
19
UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation. See: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison
当您不关心消除重复记录时，UNION ALL将比UNION表现更好，因为您避免了昂贵的不同排序操作。请参阅：SQL SERVER - Union与Union All之间的差异 - 最佳性能比较
#2
5
UNION ALL always is faster, because UNION exclude duplicated entries
UNION ALL总是更快，因为UNION排除了重复的条目
#3
3
UNION implement internally two queries. 1.SELECT which will return a dataset 2.DISTINCT. Anyone who has studied database internals can easily understand that a DISTINCT clause is extremely costly in terms of processing.
UNION在内部实现两个查询。 1.SELECT将返回数据集2.DISTINCT。任何研究过数据库内部的人都可以很容易地理解DISTINCT子句在处理方面成本极高。
If you are pretty sure that the resultant dataset need not have unique rows then we can skip UNION and use UNION ALL instead.
如果您非常确定结果数据集不需要具有唯一行，那么我们可以跳过UNION并使用UNION ALL。
UNION ALL will be same as UNION except that it doesn't fire a DISTINCT internally sparing us costly operations
UNION ALL将与UNION相同，不同之处在于它不会在内部激活DISTINCT，从而使我们无法进行昂贵的操作
#4
1
It is better to use UNION ALL when you know you want all the result rows, whether or not you know they'll be distinct or not. UNION without "all" will always perform the "distinct check", regardless of what the data actually is.
当你知道你想要所有的结果行时，最好使用UNION ALL，无论你是否知道它们是不同的。没有“all”的UNION将始终执行“distinct check”，无论数据实际是什么。
#5
0
Why is UNION ALL faster? Because UNION must do a sort to remove the duplicates. If you do not need to remove duplicates then UNION ALL is the better option, however UNION does have a purpose and should be used when appropriate.
为什么UNION ALL更快？因为UNION必须进行排序以删除重复项。如果您不需要删除重复项，那么UNION ALL是更好的选择，但是UNION确实有用，应该在适当的时候使用。
#1
19
UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation. See: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison
当您不关心消除重复记录时，UNION ALL将比UNION表现更好，因为您避免了昂贵的不同排序操作。请参阅：SQL SERVER - Union与Union All之间的差异 - 最佳性能比较
#2
5
UNION ALL always is faster, because UNION exclude duplicated entries
UNION ALL总是更快，因为UNION排除了重复的条目
#3
3
UNION implement internally two queries. 1.SELECT which will return a dataset 2.DISTINCT. Anyone who has studied database internals can easily understand that a DISTINCT clause is extremely costly in terms of processing.
UNION在内部实现两个查询。 1.SELECT将返回数据集2.DISTINCT。任何研究过数据库内部的人都可以很容易地理解DISTINCT子句在处理方面成本极高。
If you are pretty sure that the resultant dataset need not have unique rows then we can skip UNION and use UNION ALL instead.
如果您非常确定结果数据集不需要具有唯一行，那么我们可以跳过UNION并使用UNION ALL。
UNION ALL will be same as UNION except that it doesn't fire a DISTINCT internally sparing us costly operations
UNION ALL将与UNION相同，不同之处在于它不会在内部激活DISTINCT，从而使我们无法进行昂贵的操作
#4
1
It is better to use UNION ALL when you know you want all the result rows, whether or not you know they'll be distinct or not. UNION without "all" will always perform the "distinct check", regardless of what the data actually is.
当你知道你想要所有的结果行时，最好使用UNION ALL，无论你是否知道它们是不同的。没有“all”的UNION将始终执行“distinct check”，无论数据实际是什么。
#5
0
Why is UNION ALL faster? Because UNION must do a sort to remove the duplicates. If you do not need to remove duplicates then UNION ALL is the better option, however UNION does have a purpose and should be used when appropriate.
为什么UNION ALL更快？因为UNION必须进行排序以删除重复项。如果您不需要删除重复项，那么UNION ALL是更好的选择，但是UNION确实有用，应该在适当的时候使用。