SQL Server 2008 R2: Multiple UNION on different databases table's

Question

I have two databases namely db1 and db2.

The database db1 contain 1 table namely test1 and database db2 contain two tables namely test2 and test3.

Here is the following table's with some demo records.

Database: db1 Table: test1

Create table test1
(
    ID int,
    hrs int,
    dates date,
    st_date date,
    ed_date date
);

insert into test1 values(1,10,'2000-01-01','1900-01-01','2016-01-01');
insert into test1 values(2,20,'2000-02-01','1900-01-01','2016-01-01');
insert into test1 values(3,30,'2000-03-01','1900-01-01','2016-01-01');
insert into test1 values(4,40,'2000-04-01','1900-01-01','2016-01-01');

Database: db2 Table: test2

create table test2
(
    ID int,
    ID2 int
);

insert into test2 values(1,11);
insert into test2 values(2,22);
insert into test2 values(3,33);
insert into test2 values(4,44);

Database: db2 Table: test3

create table test3
(
    ID int,
    date date
);

insert into test3 values(1,'2000-01-01');
insert into test3 values(2,'2000-02-02');
insert into test3 values(3,'2000-03-03');
insert into test3 values(4,'2000-04-04');

Note: Now i am executing following query by union all three table's but getting performance issue. There are also test2,test3 tables are present in the db1

select nm,sum(x.avghrs)
from
(
select t1.nm, sum(hrs) as avghrs
from db2.test3 t3,
     db2.test2 t2,
     db1.test1 t1
where t1.id = t2.id
    t3.id = t2.id
group by t1.nm

union

select t1.nm, sum(hrs) as avghrs
from db1.test3 t3,
     db1.test2 t2,
     db1.test1 t1
where t1.id = t2.id
    t3.id = t2.id
group by t1.nm

union

select nm, 0 as avghrs
from test1
where dates between st_date and ed_date
) x

group by nm;

Please tell me if there is any need of modification?

Can you include a screenshot of the [execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan)? — zedfoxus, Mar 30 '16 at 12:52
is the `union` necessary with it's extra check for duplicates or is a `union all` enough? — Pred, Mar 30 '16 at 13:01

score 1 · Accepted Answer · answered Mar 30 '16 at 13:02

I think the problem is related to JOINs between columns from tables residing in different databases. You can try the following:

1) mirror the tables in a single database (replicate schema and data)

2) apply the appropriate indexes (at least to contain ids used in JOINs)

3) Change your query to SELECT only from mirrored tables

Also, do you need to perform distinct for your unioned results? If not, UNION should be replaced with UNION ALL to avoid the implicit DISTINCT.

score 0 · Answer 2 · answered Mar 30 '16 at 13:02

0

UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation.

answered Mar 30 '16 at 13:02

Jainish Jariwala

308
4
14

SQL Server 2008 R2: Multiple UNION on different databases table's

2 Answers2