用UNION子查询进行查询需要很长时间 [英] Query with a UNION sub-query takes a very long time

问题描述

对于依赖子查询的某些查询，我一直遇到一个奇怪的问题.它们运行得非常快，直到我在子查询中使用UNION语句为止.然后它们无休止地运行，十分钟后我给了.我现在描述的场景不是我最初开始的，但是我认为它消除了很多可能的问题，但产生了同样的问题.因此，即使这是毫无意义的查询，也请忍受！

I've been having an odd problem with some queries that depend on a sub query. They run lightning fast, until I use a UNION statement in the sub query. Then they run endlessly, I've given after 10 minutes. The scenario I'm describing now isn't the original one I started with, but I think it cuts out a lot of possible problems yet yields the same problem. So even though it's a pointless query, bear with me!

我有一张桌子:

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

如果我执行:

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

…然后运行一秒钟.但是，如果我修改它以使子查询具有UNION，它将运行至少10分钟(在我放弃之前！)

… then it runs in under a second. However, if I modify it so that the sub query has a UNION, it will run for at least 10 minutes (before I give up!)

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

无意义的更改，但它应该产生相同的结果，我不知道为什么需要更长的时间?

A pointless change, but it should yield the same result and I don't see why it should take any longer?

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

Putting the sub-query into a view and calling that instead has the same effect.

任何想法为何会这样?我正在使用SQL Azure.

Any ideas why this would be? I'm using SQL Azure.

问题已解决.请在下面查看我的答案.

Problem solved. See my answer below.

推荐答案

事实证明，问题是由于其中一个索引引起的.tblFavourites在tblUser中包含两个主键(uid)的外键:

It turns out the problem was due to one of the indexes ... tblFavourites contained two foreign keys to the primary key (uid) in tblUser:

userId
otherUserId

这两个列具有相同的定义和相同的索引，但是我发现在原始查询中将userId替换为otherUserId可以解决此问题.

both columns had the same definition and same indexes, but I discovered that swapping userId for otherUserId in the original query solved the problem.

我跑了

ALTER INDEX ALL ON tblFavourites REBUILD

...问题消失了.现在查询几乎立即执行.

... and the problem went away. The query now executes almost instantly.

我对Sql Server/Azure幕后发生的事情不太了解...但是我只能想象它是损坏的索引或什么东西?我经常更新统计信息，但是没有效果.

I don't know too much about what goes on behind the scenes in Sql Server/Azure ... but I can only imagine that it was a damaged index or something? I update statistics frequently, but that had no effect.

谢谢！

----更新

以上内容并不完全正确.它 did 解决了大约20分钟的问题，然后又返回了.我已经与Microsoft支持联系了好几天，看来问题出在tempDB上.他们正在最后寻求解决方案.

The above was not fully correct. It did fix the problem for around 20 minutes, then it returned. I have been in touch with Microsoft support for several days and it seems the problem is to do with the tempDB. They are working on a solution at their end.

这篇关于用UNION子查询进行查询需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！