Having several databases with the same table, I need to group the union of the records.The xTables 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 idThe 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 idThe 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.
For a query over multiple databases, is it faster to do GROUP BY first or UNION first?
performance;sql;sql server;comparative review
The Second is FasterHaving just tested a slightly simpler version of this, using only 1 column to be aggregated, I found that the second version is faster. I would also argue that the second is much easier to read.Here is the code I used to test it:CREATE TABLE TEST1 (id INT, a INT)INSERT INTO TEST1SELECT 1,23 UNION ALLSELECT 1,26 UNION ALLSELECT 1,47 UNION ALLSELECT 2,13 UNION ALLSELECT 2,31CREATE TABLE TEST2 (id INT, a INT)INSERT INTO TEST2SELECT 1,18 UNION ALLSELECT 1,45 UNION ALLSELECT 2,24 UNION ALLSELECT 2,42 UNION ALLSELECT 2,64/*    Single Group*/SELECT     id,    MIN(a) AS minAFROM (    SELECT id,a    FROM TEST1    UNION ALL    SELECT id,a    FROM TEST2)DataGROUP BY id/*    Multiple Groups*/SELECT     id,    MIN(a) AS minAFROM (    SELECT id,MIN(a) AS a    FROM TEST1    GROUP BY id    UNION ALL    SELECT id,MIN(a) AS a    FROM TEST2    GROUP BY id)DataGROUP BY idDROP TABLE TEST1DROP TABLE TEST2General FeedbackYou've consistently capitalised your keywords and your indentation is also consistent. Both of these help readability.I recommend using the second query, it doesn't require any modification as far as I can see. You've got a pretty nice query there.