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 C

Solution 1:

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 INNERJOIN COMPANIES b
ON
a.ID <> b.ID
and a.Postcode = b.Postcode  
and a.Adres = b.Adres  
and (  
selectCOUNT(COMPANYID)  
from USERS  
where COMPANYID=a.ID  
)>(  
selectCOUNT(COMPANYID)  
from USERS  
where COMPANYID=b.ID  
)

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

Solution 2:

One approach would be to pre-calculate the COMPANYID count before doing the join since you'll be repeatedly calculating it in the main query. i.e. something like:

insertinto@CompanyCount (ID, IDCount)
select COMPANYID, COUNT(COMPANYID)
from USERS
groupby COMPANYID

Then your main query:

select a.ID, a.adres, a.place, a.postalcode
from COMPANIES a
  innerjoin@CompanyCount aCount on aCount.ID = a.ID
  innerjoin COMPANIES b on b.Postcode = a.Postcode and b.Adres = a.Adres
  innerjoin@CompanyCount bCount on bCount.ID = b.ID and aCount.IDCount > bCount.IDCount

If you want all instances of a even though there is no corresponding b then you'd need to have left outer joins to b and bCount.

However you need to look at the query plan - which indexes are you using - you probably want to have them on the IDs and the Postcode and Adres fields as a minimum since you're joining on them.

Solution 3:

1. Build an index on postcode and adres

2. The database probably executes the subselects for every row. (Just guessing here, veryfy it in the explain plan. If this is the case you can rewrite the query to join with the inline views (note this is how it would look in oracle hop it works in sql server as well):

   select distinct a.ID, a.adres, a.place, a.postalcode  
   from 
       COMPANIES a, 
       COMPANIES b,  
   (
       selectCOUNT(COMPANYID) cnt, companyid  
       from USERS
       groupby companyid) cntA,  
   (select COUNT(COMPANYID) cnt, companyid  
       from USERS
       groupby companyid) cntb   
   where a.Postcode = b.Postcode  
   and a.Adres = b.Adres  
   and a.ID<>b.ID
   and cnta.cnt>cntb.cnt