6
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ The Subtree Cost is often a pretty good metric of how resource intensive a query is. It's in the little yellow box that pops up when you hover over a step on the plan. I recommend creating both views, then querying them in a single batch. It will make it easier to compare the execution plans. \$\endgroup\$ – RubberDuck Oct 30 '14 at 10:12
  • \$\begingroup\$ Also, are you 100% sure these return the same results? \$\endgroup\$ – RubberDuck Oct 30 '14 at 10:13
  • \$\begingroup\$ @RubberDuck: Thanks for the suggestion. Should not the results be the same? The sum of sums should work, the minimum of minimums should also work. But I may have overlooked something. \$\endgroup\$ – pepr Oct 30 '14 at 10:57
  • 1
    \$\begingroup\$ @RubberDuck The results are fine. I tested a similar query as shown in my answer. Logically taking the minimum of minimums just gives you the minimum of the whole. \$\endgroup\$ – PenutReaper Oct 30 '14 at 11:08
6
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ How do you test it is faster? How do you mesure it? \$\endgroup\$ – pepr Oct 30 '14 at 11:44
  • \$\begingroup\$ @pepr I looked at the actual execution plans. The second query had a more efficient plan. \$\endgroup\$ – PenutReaper Oct 30 '14 at 13:41
  • \$\begingroup\$ I have asked the related question at stackoverflow.com/q/26675900/1346705 \$\endgroup\$ – pepr Oct 31 '14 at 13:31
5
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the elaborate explanation. It seems I have a lot of things to learn. ;) \$\endgroup\$ – pepr Oct 31 '14 at 12:57
  • \$\begingroup\$ I have asked the related question at stackoverflow.com/q/26675900/1346705 \$\endgroup\$ – pepr Oct 31 '14 at 13:31
4
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ I am new to CTE. Using it, is the result obtained faster or does it consume less memory? I did read the doc; however, I do not understand the ideas behind. Can you point me to some "big picture" article that introduces CTE? \$\endgroup\$ – pepr Oct 30 '14 at 11:57
  • \$\begingroup\$ I included the link to the CTE in my actual post. The performance of the view, and the memory it uses, is directly related to the query you run when you access the view. Views themselves take no memory, or space. The query you run when you access the view 'duplicates' the logic of the view (or whatever parts of the view are needed), and then goes with that. For example, select id from MyView will probably never do a min or sum, so will take less space/time than select * \$\endgroup\$ – rolfl Oct 30 '14 at 12:05
  • \$\begingroup\$ I did read the doc you linked. Say, I have about 7000 rows in the unioned tables. With the CTE, are the rows stored in memory when the view with CTE is used? If the subexpression were used, would the code/memory structures behind the execution be similar or different in principle? (It may be worth of a separate question on StackOverflow if you suggest it.) \$\endgroup\$ – pepr Oct 30 '14 at 12:47
  • \$\begingroup\$ No, nothing is stored in memory by the CTE. Views (and thus the CTE) are code, not data. The code is included in the query that uses the view, and only the relevant parts of the code that are relevant to the query. There is no data in the CTE... there is just a way to locate the data. The data is 'materialized' when the code is put in to an actual query. \$\endgroup\$ – rolfl Oct 30 '14 at 12:59
  • \$\begingroup\$ Thanks a lot for your help. I can accept only one answer, and PenutReaper was faster ;) \$\endgroup\$ – pepr Oct 31 '14 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.