For a query over multiple databases, is it faster to do GROUP BY first or UNION first?

Question

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 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.

Answer 1

The Second is Faster

Having 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 TEST1
SELECT 1,23 UNION ALL
SELECT 1,26 UNION ALL
SELECT 1,47 UNION ALL
SELECT 2,13 UNION ALL
SELECT 2,31

CREATE TABLE TEST2 (id INT, a INT)

INSERT INTO TEST2
SELECT 1,18 UNION ALL
SELECT 1,45 UNION ALL
SELECT 2,24 UNION ALL
SELECT 2,42 UNION ALL
SELECT 2,64

/*
    Single Group
*/
SELECT 
    id,
    MIN(a) AS minA
FROM 
(
    SELECT id,a
    FROM TEST1

    UNION ALL

    SELECT id,a
    FROM TEST2
)Data
GROUP BY id

/*
    Multiple Groups
*/
SELECT 
    id,
    MIN(a) AS minA
FROM 
(
    SELECT id,MIN(a) AS a
    FROM TEST1
    GROUP BY id

    UNION ALL

    SELECT id,MIN(a) AS a
    FROM TEST2
    GROUP BY id
)Data
GROUP BY id


DROP TABLE TEST1
DROP TABLE TEST2

General Feedback

You'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.

Answer 2

The second query is more efficient. Although with the current number of tables, you won't feel a performance increase, but as you add tables you may start to notice (although I doubt it given the size of your tables).

I tested this by created my own set of three tables, each with 2500 records and three columns: an ID and two random numbers.

IF OBJECT_ID('[dbo].[x1]','U') IS NOT NULL DROP TABLE [dbo].[x1]
IF OBJECT_ID('[dbo].[x3]','U') IS NOT NULL DROP TABLE [dbo].[x3]
IF OBJECT_ID('[dbo].[x2]','U') IS NOT NULL DROP TABLE [dbo].[x2]

CREATE TABLE [dbo].[x1] ( [TableID] INT IDENTITY(1, 1), [a] INT, [b] INT )
CREATE TABLE [dbo].[x2] ( [TableID] INT IDENTITY(1, 1), [a] INT, [b] INT )
CREATE TABLE [dbo].[x3] ( [TableID] INT IDENTITY(1, 1), [a] INT, [b] INT )

-- TEMPORARY TABLE CREATION
IF OBJECT_ID('tempdb..[#RandomNumbers]') IS NOT NULL    DROP TABLE [#RandomNumbers]
IF OBJECT_ID('tempdb..[#RandomNumbers2]') IS NOT NULL   DROP TABLE [#RandomNumbers2]
IF OBJECT_ID('tempdb..[#ids]') IS NOT NULL              DROP TABLE [#ids]

-- GENERATING TWO SETS OF RANDOM NUMBERS
CREATE TABLE [#ids] ( [RandSeq] INT )
SET NOCOUNT ON
DECLARE @startNum INT 
SELECT  @startNum = 1000
BEGIN WHILE ( @startNum <= 9999 ) BEGIN
            INSERT  INTO [#ids]
                    ( [RandSeq] )
                    SELECT  @startNum
            SELECT  @startNum = @startNum + 1
        END RETURN END
SET NOCOUNT OFF 
GO

CREATE TABLE [#RandomNumbers] ( [TableID] INT IDENTITY(1, 1), [vipRand] INT )
INSERT  INTO [#RandomNumbers] ( [vipRand] ) SELECT [RandSeq] FROM [#ids] ORDER BY NEWID()
CREATE TABLE [#RandomNumbers2] ( [TableID] INT IDENTITY(1, 1), [vipRand] INT )
INSERT  INTO [#RandomNumbers2] ( [vipRand] ) SELECT [RandSeq] FROM [#ids] ORDER BY NEWID()

-- POPULATING TEST TABLES
INSERT INTO [dbo].[x1] ( [a], [b] )
SELECT  TOP 2500 [a].[vipRand], [b].[vipRand]
FROM    [#RandomNumbers] a INNER JOIN [#RandomNumbers2] b ON [a].[TableID] = [b].[TableID]
ORDER BY NEWID()

INSERT INTO [dbo].[x2] ( [a], [b] )
SELECT  TOP 2500 [a].[vipRand], [b].[vipRand]
FROM    [#RandomNumbers] a INNER JOIN [#RandomNumbers2] b ON [a].[TableID] = [b].[TableID]
ORDER BY NEWID()

INSERT INTO [dbo].[x3] ( [a], [b] )
SELECT  TOP 2500 [a].[vipRand], [b].[vipRand]
FROM    [#RandomNumbers] a INNER JOIN [#RandomNumbers2] b ON [a].[TableID] = [b].[TableID]
ORDER BY NEWID()

The next step is to test the two queries you laid out in the question. Make sure you turn on the option to Include Actual Execution plan. We'll also turn on STATISTICS so you can see the number of reads, as well as wiping the cache between queries so we see the true reads.

SET STATISTICS IO ON

--WIPE CACHE
USE [TestDB];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

SELECT  [TableID], MIN([a]), CONVERT(NUMERIC(19, 2), SUM([b]))
FROM (  SELECT  [TableID], MIN([a]) AS [a], SUM([b]) AS [b]
        FROM    [dbo].[x1]
        GROUP BY [TableID]
        UNION 
        SELECT  [TableID], MIN([a]) AS [a], SUM([b]) AS [b]
        FROM    [dbo].[x2]
        GROUP BY [TableID]
        UNION 
        SELECT  [TableID], MIN([a]) AS [a], SUM([b]) AS [b]
        FROM    [dbo].[x3]
        GROUP BY [TableID] ) AS u
GROUP BY [u].[TableID]

--WIPE CACHE
USE [TestDB];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

SELECT  [TableID], MIN([a]), CONVERT(NUMERIC(19, 2), SUM([b]))
FROM (  SELECT  [TableID], [a], [b]
        FROM    [dbo].[x1]
        UNION 
        SELECT  [TableID], [a], [b]
        FROM    [dbo].[x2]
        UNION 
        SELECT  [TableID], [a], [b]
        FROM    [dbo].[x3] ) AS u
GROUP BY [u].[TableID]

If you run these together, you should be able to compare both the reads and the execution plans. Notice first that they are using the same number of logical and physical reads, so we're good there. But look at the execution plans. There is a pretty distinct difference, caused by the number of calculations that are done. In the first query, you are running a set of calculations on the data, and then doing it a second time. By only doing one calculation, the second query has to go through less steps, so you're being more efficient and returning the same results. But only by a little. You're looking at a relative query cost that doesn't show much differentiation, hence my starting comment that it's not that big of a deal either way.

But we can make your query more efficient by changing the UNIONs to UNION ALLs. If you run the next code block with the previous two, you can again see the differences.

--WIPE CACHE
USE [TestDB];
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

SELECT  [TableID], MIN([a]), CONVERT(NUMERIC(19, 2), SUM([b]))
FROM (  SELECT  [TableID], [a], [b]
        FROM    [dbo].[x1]
        UNION ALL
        SELECT  [TableID], [a], [b]
        FROM    [dbo].[x2]
        UNION ALL
        SELECT  [TableID], [a], [b]
        FROM    [dbo].[x3] ) AS u
GROUP BY [u].[TableID]

You'll notice that the execution plan reduces the number of steps, choosing to CONCATENATE the tables rather than MERGE JOIN. In the first two queries, you are specifying a distinct selection of records by using UNION, which you don't really need. UNION ALL will be faster because it doesn't need to sort and join the results to find that distinct selection. For a brief overview, see this question.

Answer 3

I agree with PenutReaper that the second is the better query, but for slightly different reasons.

Views are designed to be used for inclusion in other select statements, and since the only non-aggregated column on the query is the id, the second view makes that column more accessible for joins of the form:

select * from view where id = abc

As for the view itself, I would take the view further, and make it a CTE:

CREATE VIEW  MyView AS
WITH rawdata AS (

    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

)
SELECT id, min(a) AS a, sum(b) AS b
FROM rawdata
GROUP BY id