UNION 与 DISTINCT 的表现 [英] UNION vs DISTINCT in performance

问题描述

在 SQL 2008 中，我有一个这样的查询:

In SQL 2008, I have a query like so:

QUERY A
UNION 
QUERY B
UNION 
QUERY C

是否会比将所有 3 个查询的结果放入临时表中，然后使用 DISTINCT 对它们进行 SELECT 处理更慢/更快?

Will it be slower/faster than putting the result of all 3 queries in say, a temporary table and then SELECTing them with DISTINCT?

推荐答案

这取决于查询 -- 在不知道查询 A、B 或 C 的复杂性的情况下，它无法回答，因此最好的办法是进行概要分析然后据此判断.

It depends on the query -- without knowing the complexity of queries A, B or C it's not one that can be answered, so your best bet is to profile and then judge based on that.

不过……无论如何，我可能会选择工会:临时表可能非常昂贵，尤其是当它变大时.请记住，使用临时表，您明确地创建了额外的操作，从而增加了 I/O 以减轻磁盘子系统的压力.如果您可以在不求助于临时表的情况下进行选择，那总是(可能)会更快.

However... I'd probably go with a union regardless: a temporary table can be quite expensive, especially as it gets big. Remember with a temporary table, you're explicitly creating extra operations and thus more i/o to stress the disk sub-system out. If you can do a select without resorting to a temporary table, that's always (probably) going to be faster.

此规则肯定有例外(或七个)，因此您最好针对实际大型数据集进行分析，以确保获得一些可靠的数字来做出合适的决定.

There's bound to be an exception (or seven) to this rule, hence you're better off profiling against a realistically large dataset to make sure you get some solid figures to make a suitable decision on.

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