CODEWITHSUNDEEP
sqlsql-server
Ashok
Ashok

Reputation: 55

SQL Server view performance issue

I want to create a view with three different tables. each table contains some duplicate values. To remove duplicates I have used UNION, but it causes performance issues.

CREATE VIEW [dbo].[UserView]
AS
    SELECT * FROM A
    UNION
    SELECT * FROM B
    UNION
    SELECT * FROM C
END

If I use Select * from A the performance will be faster. But when using the above query creates performance issues. I want to remove duplicates from three tables and increase performance in view

Sample

Table A:

insert into a (empid, empname, managerid) values (1, 'test1', 2)

Table B

insert into b (empid, empname, managerid) values (1, 'test1', 2)
insert into b (empid, empname, managerid) values (2, 'test2', 3)

Table C

insert into c (empid, empname, managerid) values (1, 'test1', 1)

Expected

(1, 'test1', 1)
(2, 'test2', 3)
(1, 'test1', 2)

Upvotes: 0

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

It is a bit painful, but if you have indexes on (empid, empname, managerid) in the tables and no duplicates within a table, then you can use:

select empid, empname, managerid
from a
union all
select empid, empname, managerid
from b
where not exists (select 1
                  from a
                  where b.empid = a.empid and
                        b.empname = a.empname and
                        b.managerid = a.managerid
                 ) 
union all
select empid, empname, managerid
from c
where not exists (select 1
                  from a
                  where c.empid = a.empid and
                        c.empname = a.empname and
                        c.managerid = a.managerid
                 ) and      
      not exists (select 1
                  from b
                  where c.empid = b.empid and
                        c.empname = b.empname and
                        c.managerid = b.managerid
                 ) ;

Basically, this eliminates the duplicates between the tables, so you can use union all instead of union. The index is important for performance.

Upvotes: 1

Related Questions