Cross Database Unions

Question

My warehouse datamart data is split across 3 databases on the same server. This is Proof-Of-Concept project with three extracts that were loaded into individual databases.

While pulling into a cube, I am essentially doing this:

SELECT * FROM DB1.dbo.Fact_Pres
UNION
SELECT * FROM DB2.dbo.Fact_Pres
UNION
SELECT * FROM DB3.dbo.Fact_Pres

Should I actually consolidate the data into ONE table? Will that make my processing any faster?

I have no issues with disk space - I want to implement the best solution.

In either case, can you help me understand why the method you suggest would be optimal?

Answer 1

Consider federating your table using SQL Server's first class partitioning (as opposed to doing it yourself). If you're always select every data point, then maybe yeah, pulling off multiple disks is faster.

But why have multiple databases? You could always stack the three tables into one table, but have that one table implemented atop three drives RAIDed together. This is a more clear cut solution if what you're after is speed.

The federating only makes sense if you're ever selecting specific, adjacent parts of the set. But according to your OP, you're selecting everything, so that eliminates that benefit.

Answer 2

Yes, you definitely should. There is no point in splitting the same table in different databases. If you have problems with hard disk space, think about partitioning your table.

Regarding your comment:

The performance cost isn't THAT huge, but a union performs a merge join, which brings a bit ov an overhead.

In addition to that, are you sure you are using UNION correctly? UNION will eliminate duplicate values. Maybe what you really want to do is UNION ALL?