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

```
`count(distinct)` is more resource intensive than other aggregation functions. You could try:

```
SELECT *
FROM AllEntries
GROUP BY Id
HAVING min(Name1) <> max(Name1) or min(Name2) <> max(Name2);

```

If you build indexes on `id, Name1` and `id, Name2` in each of the subtables, the following should show a significant performance improvement:

```
select ae.*
from AllEntries ae
where exists (select 1 from subtable1 ae2 where ae.id = ae2.id and ae.Name1 <> ae2.Name1) or
      exists (select 1 from subtable2 ae2 where ae.id = ae2.id and ae.Name1 <> ae2.Name1) or
      . . . 

```

These are split into subqueries to encourage the optimizer to use a different index on each one.