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

```
This will largely depend on your indices, but the last statement with the OR condition is certainly not ideal for a table of this size. Also, I'm not sure why you need a SELECT \* for this... extra IO. Avoid it if you can.

Try something like this....

```
SELECT id, COUNT(name1)
FROM {table}
GROUP BY id
HAVING COUNT(*) > 1

UNION ALL

SELECT id, COUNT(name2)
FROM {table}
GROUP BY id
HAVING COUNT(*) > 1

UNION ALL 

```

etc

This will let you leverage the index on ID and also avoid COUNT DISTINCT, which is a very expensive function as a rule.

If you want to get those specific records, I'd recommend writing a CTE and joining the result of the query above with the data itself.... return all the ids and the names for which count(\*) is higher. 

For as few records as you expect to be duplicated in some way, another option is just 

```
SELECT id, COUNT(*)
FROM {table}
GROUP BY id
HAVING COUNT(*) > 1

```

and then join that with your datatable using id.... that'd avoid the extra work with the union all stuff, and would show you every record for which ID is duplicated. To me that would be preferable anyway... you probably don't want duplicate ID's :-)