比较查询采用年龄 [英] comparison query taking ages

问题描述

我的查询很简单：

My query is quite simple:

select  a.ID, a.adres, a.place, a.postalcode  
from COMPANIES a, COMPANIES b  
where a.Postcode = b.Postcode  
and a.Adres = b.Adres  
and (  
select COUNT(COMPANYID)  
from USERS  
where COMPANYID=a.ID  
)>(  
select COUNT(COMPANYID)  
from USERS  
where COMPANYID=b.ID  
)

数据库：sql server 2008 r2

Database: sql server 2008 r2

我想做什么：
COMPANIES的表包含双重条目。我想知道那些连接到最多的用户。所以我只需要更改那些最少的外键。 （我已经知道双倍的id）

What I'm trying to do: The table of COMPANIES contains double entries. I want to know the ones that are connected to the most amount of users. So I only have to change the foreign keys of those with the least. ( I already know the id's of the doubles)

现在需要很多时间才能完成。

Right now it's taking a lot of time to complete. I was wondering if if could be done faster

推荐答案

试试这个版本。它应该只有一点快。 COUNT 很慢。我已添加 a.ID<>

Try this version. It should be only a little faster. The COUNT is quite slow. I've added a.ID <> b.ID to avoid few cases earlier.

select  a.ID, a.adres, a.place, a.postalcode  
from COMPANIES a INNER JOIN COMPANIES b
ON
a.ID <> b.ID
and a.Postcode = b.Postcode  
and a.Adres = b.Adres  
and (  
select COUNT(COMPANYID)  
from USERS  
where COMPANYID=a.ID  
)>(  
select COUNT(COMPANYID)  
from USERS  
where COMPANYID=b.ID  
)

FROM ... INNER JOIN ... ON ... 是连接表的首选SQL构造。它也可能更快。

The FROM ... INNER JOIN ... ON ... is a preferred SQL construct to join tables. It may be faster too.

这篇关于比较查询采用年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！