使用 UNION 子查询的查询需要很长时间

Question

我在某些依赖于子查询的查询上遇到了一个奇怪的问题。它们运行得快如闪电，直到我在子查询中使用 UNION 语句。然后他们就无休无止地跑，我10分钟后就给了。我现在描述的场景不是我一开始的场景，但我认为它消除了很多可能的问题，但却产生了同样的问题。因此，即使这是一个毫无意义的问题，请耐心等待！

我有一个表：

tblUser - 100,000 rows
tblFavourites - 200,000 rows

如果我执行：

SELECT COUNT(*) 
FROM tblFavourites 
WHERE userID NOT IN (SELECT uid FROM tblUser);  

...那么它会在一秒钟内运行。但是，如果我修改它以使子查询具有 UNION，它将运行至少 10 分钟（在我放弃之前！）这是

SELECT COUNT(*) 
FROM tblFavourites 
WHERE userID NOT IN (SELECT uid FROM tblUser UNION SELECT uid FROM tblUser);  

一个毫无意义的更改，但它应该产生相同的结果，我不明白为什么它应该需要更长的时间吗？

将子查询放入视图中并调用它具有相同的效果。

有什么想法吗？我正在使用 SQL Azure。

---

问题解决了。请参阅下面我的回答。

---

Answer 1

UNION 实际上是对组合数据集中的所有字段执行 DISTINCT。它会过滤掉最终结果中的重复内容。

Uid 是否已编入索引？如果不是，则可能需要很长时间，因为查询引擎：

• 生成第一个结果集
• 生成第二个结果集
• 过滤掉哈希表中的所有重复项（即一半记录）

如果不关心重复项（并使用 < code>IN 意味着它们不会），然后使用 UNION ALL 来删除昂贵的排序/过滤步骤。

Answer 2

UNION 生成唯一值，因此 DBMS 引擎进行排序。
在这种情况下，您可以安全地使用 UNION ALL。

Answer 3

UNION 通常通过内存中的临时表来实现。您实际上是将 tblUser 复制到内存中两次，没有索引。那么 tblFavourites 中的每一行都会引发超过 200,000 行的完整表扫描 - 即 200Kx200K=400 亿次双行扫描（因为查询引擎必须从两个表行获取 uid）

如果您的 tblUser 在 uid 上有索引（这绝对是正确的）因为 SQL Azure 中的所有表都必须有聚集索引），那么 tblFavourites 中的每一行都会产生非常快速的索引查找，导致仅 200Kxlog(100K) =200Kx17 = 200K 行扫描，每个扫描有 17 个 b 树索引比较（这比从数据页上的行读取 uid 快得多），因此它应该等于大约 200Kx(3- 4) 或100万次双行扫描。我相信较新版本的 SQL Server 也可能会构建一个仅包含 uid 的临时哈希表，因此本质上它会减少到 200K 行扫描（假设哈希表查找很简单）。

您还应该生成查询计划进行检查。

本质上，如果 tblUser 有索引（应该位于 SQL Azure 上），非 UNION 查询的运行速度会快大约 500,000 倍。

Answer 4

事实证明，问题是由于索引之一... tblFavourites 包含 tblUser 中主键（uid）的两个外键：

userId
otherUserId

两列具有相同的定义和相同的索引，但我发现将 userId 交换为 otherUserId原始查询解决了问题。

我跑了：

ALTER INDEX ALL ON tblFavourites REBUILD

……然后问题就消失了。现在查询几乎立即执行。

我不太了解 Sql Server/Azure 幕后发生的事情......但我只能想象它是损坏的索引或其他什么？我经常更新统计数据，但这没有效果。

谢谢！

---- 更新

以上并不完全正确。它确实解决了大约 20 分钟的问题，然后又恢复了。我已经与 Microsoft 支持人员联系了几天，问题似乎与 tempDB 有关。他们最终正在研究解决方案。

Answer 5

我刚刚遇到这个问题。我有大约 100 万行需要检查，然后我意识到我的一些 ID 位于另一个表中，因此我联合起来在一个“不存在”中获取相同的信息。我从花费大约 7 秒的查询变成在一分钟左右后仅处理 5000 行。这似乎有帮助。我绝对讨厌这个解决方案，但我尝试了很多方法，但最终都以同样极其缓慢的执行计划告终。这个在大约 18 秒内就满足了我的需要。

    DECLARE @PIDS TABLE ([PID] [INT] PRIMARY KEY)
    INSERT INTO @PIDS SELECT DISTINCT [ID] FROM [STAGE_TABLE] WITH(NOLOCK)
    INSERT INTO @PIDS SELECT DISTINCT [OTHERID] FROM [PRODUCTION_TABLE] WITH(NOLOCK)
        WHERE NOT EXISTS(SELECT [PID] FROM @PIDS WHERE [PID] = [OTHERID]

    SELECT (columns needed)
    FROM [ORDER_HEADER] [OH] WITH(NOLOCK)
    INNER JOIN @PIDS ON [OH].[SOME_ID] = [PID]

（是的，我尝试了“WHERE EXISTS IN...”作为最终选择...内连接速度更快）
请让我再说一遍，我个人觉得这真的很难看，但我实际上在我的过程中使用了这个 join 两次，所以从长远来看这会节省我的时间。希望这有帮助。

Answer 6

是否更有意义？

将问题从“不在该表和/或该表中出现的所有 Id 的组合列表中的用户 ID”

改写为

“用户 ID” 不在这张桌子上并且也不在那张桌子上

SELECT COUNT(*) 
FROM tblFavourites 
WHERE userID NOT IN (SELECT uid FROM tblUser) 
AND   userID NOT IN (SELECT uid FROM tblUser);