在限制总结果之前限制联合查询是否更有效

Question

Setup

我正在连接几个表，以自动完成方式从多个来源过滤电子邮件地址。为了简单起见，我在这个例子中只包含 2，但目前我实际上使用的是 6。

使用 SQL Server 2008。

当前查询

SELECT TOP 20 * FROM (
  SELECT
    ID AS id,
    LastName + ', ' + FirstName AS label,
    Email AS identifier,
    'person' AS source_type
  FROM Persons
  WHERE (LastName + ', ' + FirstName LIKE 'a%' OR FirstName + ' ' + LastName LIKE 'a%')
    AND DATALENGTH(Email) > 0 AND Email IS NOT NULL
UNION
  SELECT
    Company AS id,
    Name AS label,
    Email AS identifier,
    'company' AS source_type
  FROM Companies
  WHERE (Name LIKE 'a%')
    AND DATALENGTH(Email) > 0 AND Email IS NOT NULL
) temp ORDER BY label ASC

问题

预购和限制联合查询会更有效或更有效吗？像这样：

SELECT TOP 20 * FROM (
  SELECT TOP 20
    ID AS id,
    LastName + ', ' + FirstName AS label,
    Email AS identifier,
    'person' AS source_type
  FROM Persons
  WHERE (LastName + ', ' + FirstName LIKE 'a%' OR FirstName + ' ' + LastName LIKE 'a%')
    AND DATALENGTH(Email) > 0 AND Email IS NOT NULL
  ORDER BY LastName ASC, FirstName ASC
UNION
  SELECT TOP 20
    ID AS id,
    Name AS label,
    Email AS identifier,
    'company' AS source_type
  FROM Companies
  WHERE (Name LIKE 'a%')
    AND DATALENGTH(Email) > 0 AND Email IS NOT NULL
  ORDER BY Name ASC
) temp ORDER BY label ASC

Answer 1

绝对与UNION .

UNION会产生删除重复项的开销 - 这是一种基于哈希或基于排序的重复删除算法。 无论哪种方式，进入的记录越少越快。

情况与UNION ALL有点不同. SQL Server非常擅长将谓词从外部查询向下推送到内部子查询。 但是，有时子查询可以利用索引(甚至分区(，并且在子查询中包含逻辑可以使优化程序更容易找到此类改进。