Performance of UNION on similar queries and group by

Question

Post reply

Performance of UNION on similar queries and group by

franksj

SSC Journeyman

Points: 88
More actions
June 3, 2009 at 12:14 pm

#205821

If I have a query such as the one below I wrote using the Adventureworks Database, is there a more optimal way to write it?
Since the first is really the same as the second, with the exception of an additional group by column, is there a way to do this more effeciently?
Looking at the Execution Plan (attached) it seems to be doing the same work twice.
SELECT CAST(SOD.SalesOrderID AS VARCHAR(25)) SalesOrderID,SOD.ProductID,AVG(orderqty) AVGQTY,
AVG(UnitPrice) UNIRPRICE
FROM SalesLT.SalesOrderDetail SOD
INNER JOIN SalesLT.Product P ON P.ProductID = SOD.ProductID
WHERE P.Color = 'Red'
GROUP BY SOD.SalesOrderID,SOD.ProductID
UNION
SELECT 'AllSalesOrders' SalesOrderID,SOD.ProductID,AVG(orderqty) AVGQTY,
AVG(UnitPrice) UNIRPRICE
FROM SalesLT.SalesOrderDetail SOD
INNER JOIN SalesLT.Product P ON P.ProductID = SOD.ProductID
WHERE P.Color = 'Red'
GROUP BY SOD.ProductID
Thanks 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 1

Hello Frank,

Your query made me feel that you can use the ROLLUP or CUBE t-sql functions.

You know SQL2008 has also introduced the GROUPING SETS.

You will find some samples at Group By Grouping Sets - Rollup and Cube

I'll give a try to myself to get similar results to your query,

Eralper

http://www.kodyaz.com

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

Hi Frank,

Can you please check the following query which I used the GROUP BY GROUPING SETS

SELECT

ISNULL(SalesOrderID,'AllSalesOrders') SalesOrderID, ProductID, AVG(AVGQTY * 1.0) AVGQTY, AVG(UNIRPRICE) UNIRPRICE

FROM (

SELECT

CAST(SOD.SalesOrderID AS VARCHAR(25)) SalesOrderID,

SOD.ProductID,

orderqty AVGQTY,

UnitPrice UNIRPRICE

FROM SalesLT.SalesOrderDetail SOD

INNER JOIN SalesLT.Product P ON P.ProductID = SOD.ProductID

WHERE P.Color = 'Red'

) Cnt

GROUP BY GROUPING SETS(SalesOrderID,ProductID,(),(SalesOrderID,ProductID))

HAVING ProductID IS NOT NULL

ORDER BY ProductID

I hope it is as you wish!

Eralper

T-SQL Development

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

franksj SSC Journeyman Points: 88 More actions · Answer 3

That looks great.

I will try this concept on my real query and let you know how it goes.

Thanks! 🙂

franksj SSC Journeyman Points: 88 More actions · Answer 4

I tried this on my actual environment, and using grouping sets did yield identical results as I had before, but performance was slower than 2 separate group by queries with a UNION.