Having several databases with the same table, I need to group the union of the records.
The xTable
s are stored in separate databases named by the year (this is by third-party design and I cannot change it). There are 3 databases in my case but it could be say up to 10. Each of the tables contains about 2500 records with about 300 distinct xID
values.
I have tried two approaches but I do not know which is better. I need to calculate the MIN and SUM of the grouped values. The first approach calculates the values for the years (databases) first, and then calculates it again from the agregated values.
CREATE VIEW dbo.view1 AS
SELECT
id,
MIN(minA) AS a,
CONVERT(numeric(19, 2), SUM(sumB)) AS b
FROM (
SELECT
xID AS id,
MIN(xA) AS minA,
SUM(xB) AS sumB
FROM x2014.dbo.xTable
GROUP BY xID
UNION
SELECT
xID AS id,
MIN(xA) AS minA,
SUM(xB) AS sumB
FROM x2013.dbo.xTable
GROUP BY xID
UNION
SELECT
xID AS id,
MIN(xA) AS minA,
SUM(xB) AS sumB
FROM x2012.dbo.xTable
GROUP BY xID
) AS u
GROUP BY id
The second approach unions all the record first and groups the flatted records later:
CREATE VIEW dbo.view2 AS
SELECT
xID AS id,
MIN(xA) AS a,
CONVERT(numeric(19, 2), SUM(xB)) AS b
FROM (
SELECT
xID,
xA,
xB
FROM x2014.dbo.xTable
UNION
SELECT
xID,
xA,
xB
FROM x2013.dbo.xTable
UNION
SELECT
xID,
xA,
xB
FROM x2012.dbo.xTable
) AS u
GROUP BY id
The execution plans differ and they show only percentage of the calculation time. I am not that good to read them. By intuition, the first approach may take advantage of statistics bound to the separate databases. However, I am not sure how it is implemented.
The view may be used intensively, and I need to decide which form is more efficient.