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.
