Grouping Sets vs Native method of Group By - Performance review in SQL Server 2008 by serverku

Previously I have posted for the overview and usage of the Grouping Sets as how can we get the aggregate data of different group sets with GROPING SETS vs Native method of group by.

If you have not read my earlier post for the same, then please read it before go ahead with this demonstration. In this demo i am going to show the performance of Grouping Sets and native method of group by. Let us start the demo here.

-- Creatind database and table 
CREATE DATABASE GroupingDB

GO

USE GroupingDB

GO

IF (OBJECT_ID('GroupingTable') > 0)
DROP TABLE GroupingTable

CREATE TABLE GroupingTable
  (
     MainCategoryName VARCHAR(100)
     ,SubCategoryId   VARCHAR(100)
     ,VALUE           BIGINT
  )

GO 

Now we are going to insert so many records in table for the presentation of demo.

-- Insert some demo records for different group sets
INSERT INTO GroupingTable
SELECT 'Main-1','Sub-1',100
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-1','Sub-2',200
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-2','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-2',400
GO 500 

As I said to check the performance of both of the script using them and check the execution plan.

-- Get aggregate data using native method of group by of different sets
SELECT NULL, NULL,
  SUM(VALUE) as Total
FROM   GroupingTable
Union all
SELECT  MainCategoryName
,NULL
,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY MainCategoryName 
Union all
SELECT  NULL,
  SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY SubCategoryId 
union all
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
  MainCategoryName
  ,SubCategoryId

-- Get aggregate data using Grouping Sets of different sets
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

You can see the data rows of above both scripts are same and given same result set. Now look for execution plan.

(Click on image to enlarge)

This performance review is totally based on the data and depends on the your business requirement. Before implementing this new feature, please check the execution and decide you view.