MSSQL - Union Query

[Mark Gambo]

I am designing a Union query between to tables in order to sum to like fields:

SELECT OBJNUM, Sum(SUM1) AS HASH
FROM (
     SELECT Table1.OBJNum AS OBJNUM, Sum(Table1.EMPNUM) as Sum1
     FROM Table1
     GROUP BY Table1.OBJNum
     
     UNION

     SELECT Table2.OBJNum AS OBJNUM,  Sum(Table2.VENNUM) as Sum1
     FROM Table2
     GROUP BY Table2.OBJNum
     )
GROUP BY OBJNUM;

I was wondering if it would be more efficent if if I just sum'ed the Sum1 Fields in the Main Query like This:

SELECT OBJNUM, Sum(SUM1) AS HASH
FROM (
     SELECT Table1.OBJNum AS CASENUM, Table1.EMPNUM as Sum1
     FROM Table1
     GROUP BY Table1.OBJNum, Table1.EMPNUM

     UNION

     SELECT Table2.OBJNum AS OBJNUM,  Table2.VENNUM as Sum1
     FROM Table2
     GROUP BY Table2.OBJNum, Table2.VENNUM
     )
GROUP BY OBJNUM;

Any thoughts?

[szlamany]

You can answer that question yourself with your data by looking at the EXECUTION PLAN after running the query in QUERY ANALYZER (turn it on under the VIEW menu (I believe) before you run each query)...

But to think out loud...

Each GROUP BY is expensive - the working resultset is built in sorted order - so that new rows can either be insert (if not there) or not inserted (if already there). Then the "aggregation" column is amended with the value...

So - both of your examples have three GROUP BY's. You mine as well get as much bang for your buck and do the SUM() in the two UNION queries...

Now you have to tell me if I'm right

[szlamany]

btw...

UNION is evil

UNION ALL is not...

UNION does an implied DISTINCT - you will actually lose records that are the same - and it's more expensive.

[Mark Gambo]

Steve,
I was playing around at home and I didn't have access to any large amounts of data inorder to check the Execution plan. When I compared it to the data that I had Less than 100 records the execution plans look identical. BTW I ran the execution plan on a SQL2005 - Express DB but at work I'll be running it against MSSQL7.0. Wish me luck [Fingers Crossed]. I'll let you know how it goes.