Question: I'm having the following table layout: four different tables, each containing around 10 to 15 million entries. Three string attributes of each table are the same (let's call them Id, Name1, Name2). Now we want to read all entries having the same Id column but different (Name1,Name2) tuples. It is estimated that less than 0.5 % of all entries are matching.

We've created a view AllEntries (basically a UNION ALL of the relevant attributes over all four tables) and our query looks like this:

```
SELECT *
FROM AllEntries
GROUP BY Id
HAVING COUNT(DISTINCT(Name1)) > 1 OR COUNT(DISTINCT(Name2)) > 1

```

Executing the query in our test database with 2 million entries in each table (i.e. 8 million entries in the view) already takes around 2 to 3 minutes (nice server).

**Q:** Is there any performance improvement possible to improve the performance?

Answer: 
Try a CTE with `ROW_NUMBER()` instead of the traditional `GROUP BY/HAVING` approach:

```
;with cteDups as
(
    Select  *
            ,ROW_NUMBER() Over(Partition By Name1 Order By Id) rn1
            ,ROW_NUMBER() Over(Partition By Name2 Order By Id) rn2
    From    AllEntries
)
Select  *
From    cteDups
Where   rn1 > 1
    Or  rn2 > 1

```
That UNION is killing it  

There is no use of indexes on name1 or name2 after a union  

Cannot create an index on a view with UNION 

You are going to think this is whacked out but give it a try  

It uses indexes and favors a small number of matches  

If you don't have indexes on word1 and word2 then do so 

```
select distinct ta.ID 
  from t1 as ta
  join t1 as tb
    on ta.ID = tb.ID 
   and ( (ta.word1 <> tb.word1) or (ta.word2 <> tb.word2) )
union 
select distinct ta.ID 
  from t1 as ta
  join t2 as tb
    on ta.ID = tb.ID 
   and ( (ta.word1 <> tb.word1) or (ta.word2 <> tb.word2) )
union 
  t1 t3
union 
  t1 t4
union 
  t2 t2
union 
  t2 t3
union 
  t2 t4
union 
  t3 t3
union 
  t3 t4
union
  t4 t4

```