September 15, 2016 at 10:12 am
So, does anyone want to run a test where all 50 states are accounted for?
I agree that this has been one of those forums that has been thought-provoking in terms of query patterns. The other one I saw yesterday was this one:
http://www.sqlservercentral.com/Forums/Topic1816618-392-1.aspx
I'll never write a loop to delete TOP(X) rows again.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 16, 2016 at 12:10 am
Here is a 50 column test harness and the results on my old laptop (2nd Gen i5)
😎
USE TEEST;
GO
SET NOCOUNT ON;
--http://www.sqlservercentral.com/Forums/FindPost1817858.aspx
-- TEST HARNESS
DECLARE @timer TABLE (
T_TXT VARCHAR(50) NOT NULL
,T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())
,T_CPU FLOAT NOT NULL DEFAULT (@@CPU_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IDLE FLOAT NOT NULL DEFAULT (@@IDLE * CONVERT(FLOAT,@@TIMETICKS,0))
);
--/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATA SET CREATION
INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');
IF OBJECT_ID(N'dbo.TBL_TEST_UNPIVOT_AGGREGATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE;
CREATE TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE
(
ROW_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_TEST_UNPIVOT_AGGREGATE_ROW_ID PRIMARY KEY CLUSTERED
,COL001 INT NOT NULL
,COL002 INT NOT NULL
,COL003 INT NOT NULL
,COL004 INT NOT NULL
,COL005 INT NOT NULL
,COL006 INT NOT NULL
,COL007 INT NOT NULL
,COL008 INT NOT NULL
,COL009 INT NOT NULL
,COL010 INT NOT NULL
,COL011 INT NOT NULL
,COL012 INT NOT NULL
,COL013 INT NOT NULL
,COL014 INT NOT NULL
,COL015 INT NOT NULL
,COL016 INT NOT NULL
,COL017 INT NOT NULL
,COL018 INT NOT NULL
,COL019 INT NOT NULL
,COL020 INT NOT NULL
,COL021 INT NOT NULL
,COL022 INT NOT NULL
,COL023 INT NOT NULL
,COL024 INT NOT NULL
,COL025 INT NOT NULL
,COL026 INT NOT NULL
,COL027 INT NOT NULL
,COL028 INT NOT NULL
,COL029 INT NOT NULL
,COL030 INT NOT NULL
,COL031 INT NOT NULL
,COL032 INT NOT NULL
,COL033 INT NOT NULL
,COL034 INT NOT NULL
,COL035 INT NOT NULL
,COL036 INT NOT NULL
,COL037 INT NOT NULL
,COL038 INT NOT NULL
,COL039 INT NOT NULL
,COL040 INT NOT NULL
,COL041 INT NOT NULL
,COL042 INT NOT NULL
,COL043 INT NOT NULL
,COL044 INT NOT NULL
,COL045 INT NOT NULL
,COL046 INT NOT NULL
,COL047 INT NOT NULL
,COL048 INT NOT NULL
,COL049 INT NOT NULL
,COL050 INT NOT NULL
);
-- Set the data sample's parameters
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @MAX_VALUE INT = 1025;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_UNPIVOT_AGGREGATE WITH (TABLOCK)
(
ROW_ID,COL001,COL002,COL003,COL004,COL005,COL006,COL007,COL008,COL009,COL010
,COL011,COL012,COL013,COL014,COL015,COL016,COL017,COL018,COL019,COL020
,COL021,COL022,COL023,COL024,COL025,COL026,COL027,COL028,COL029,COL030
,COL031,COL032,COL033,COL034,COL035,COL036,COL037,COL038,COL039,COL040
,COL041,COL042,COL043,COL044,COL045,COL046,COL047,COL048,COL049,COL050
)
SELECT
NM.N
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
FROM NUMS NM;
INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');
-- */
DECLARE @INT_BUCKET INT = 0;
DECLARE @VCHAR_BUCKET VARCHAR(12) = '';
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');
SELECT
@INT_BUCKET = TUA.ROW_ID
,@INT_BUCKET = TUA.COL001
,@INT_BUCKET = TUA.COL002
,@INT_BUCKET = TUA.COL003
,@INT_BUCKET = TUA.COL004
,@INT_BUCKET = TUA.COL005
,@INT_BUCKET = TUA.COL006
,@INT_BUCKET = TUA.COL007
,@INT_BUCKET = TUA.COL008
,@INT_BUCKET = TUA.COL009
,@INT_BUCKET = TUA.COL010
,@INT_BUCKET = TUA.COL011
,@INT_BUCKET = TUA.COL012
,@INT_BUCKET = TUA.COL013
,@INT_BUCKET = TUA.COL014
,@INT_BUCKET = TUA.COL015
,@INT_BUCKET = TUA.COL016
,@INT_BUCKET = TUA.COL017
,@INT_BUCKET = TUA.COL018
,@INT_BUCKET = TUA.COL019
,@INT_BUCKET = TUA.COL020
,@INT_BUCKET = TUA.COL021
,@INT_BUCKET = TUA.COL022
,@INT_BUCKET = TUA.COL023
,@INT_BUCKET = TUA.COL024
,@INT_BUCKET = TUA.COL025
,@INT_BUCKET = TUA.COL026
,@INT_BUCKET = TUA.COL027
,@INT_BUCKET = TUA.COL028
,@INT_BUCKET = TUA.COL029
,@INT_BUCKET = TUA.COL030
,@INT_BUCKET = TUA.COL031
,@INT_BUCKET = TUA.COL032
,@INT_BUCKET = TUA.COL033
,@INT_BUCKET = TUA.COL034
,@INT_BUCKET = TUA.COL035
,@INT_BUCKET = TUA.COL036
,@INT_BUCKET = TUA.COL037
,@INT_BUCKET = TUA.COL038
,@INT_BUCKET = TUA.COL039
,@INT_BUCKET = TUA.COL040
,@INT_BUCKET = TUA.COL041
,@INT_BUCKET = TUA.COL042
,@INT_BUCKET = TUA.COL043
,@INT_BUCKET = TUA.COL044
,@INT_BUCKET = TUA.COL045
,@INT_BUCKET = TUA.COL046
,@INT_BUCKET = TUA.COL047
,@INT_BUCKET = TUA.COL048
,@INT_BUCKET = TUA.COL049
,@INT_BUCKET = TUA.COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA;
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');
;WITH BASE_AGGREGATE AS
(
SELECT
SUM(TUA.COL001) AS SUM_COL001
,SUM(TUA.COL002) AS SUM_COL002
,SUM(TUA.COL003) AS SUM_COL003
,SUM(TUA.COL004) AS SUM_COL004
,SUM(TUA.COL005) AS SUM_COL005
,SUM(TUA.COL006) AS SUM_COL006
,SUM(TUA.COL007) AS SUM_COL007
,SUM(TUA.COL008) AS SUM_COL008
,SUM(TUA.COL009) AS SUM_COL009
,SUM(TUA.COL010) AS SUM_COL010
,SUM(TUA.COL011) AS SUM_COL011
,SUM(TUA.COL012) AS SUM_COL012
,SUM(TUA.COL013) AS SUM_COL013
,SUM(TUA.COL014) AS SUM_COL014
,SUM(TUA.COL015) AS SUM_COL015
,SUM(TUA.COL016) AS SUM_COL016
,SUM(TUA.COL017) AS SUM_COL017
,SUM(TUA.COL018) AS SUM_COL018
,SUM(TUA.COL019) AS SUM_COL019
,SUM(TUA.COL020) AS SUM_COL020
,SUM(TUA.COL021) AS SUM_COL021
,SUM(TUA.COL022) AS SUM_COL022
,SUM(TUA.COL023) AS SUM_COL023
,SUM(TUA.COL024) AS SUM_COL024
,SUM(TUA.COL025) AS SUM_COL025
,SUM(TUA.COL026) AS SUM_COL026
,SUM(TUA.COL027) AS SUM_COL027
,SUM(TUA.COL028) AS SUM_COL028
,SUM(TUA.COL029) AS SUM_COL029
,SUM(TUA.COL030) AS SUM_COL030
,SUM(TUA.COL031) AS SUM_COL031
,SUM(TUA.COL032) AS SUM_COL032
,SUM(TUA.COL033) AS SUM_COL033
,SUM(TUA.COL034) AS SUM_COL034
,SUM(TUA.COL035) AS SUM_COL035
,SUM(TUA.COL036) AS SUM_COL036
,SUM(TUA.COL037) AS SUM_COL037
,SUM(TUA.COL038) AS SUM_COL038
,SUM(TUA.COL039) AS SUM_COL039
,SUM(TUA.COL040) AS SUM_COL040
,SUM(TUA.COL041) AS SUM_COL041
,SUM(TUA.COL042) AS SUM_COL042
,SUM(TUA.COL043) AS SUM_COL043
,SUM(TUA.COL044) AS SUM_COL044
,SUM(TUA.COL045) AS SUM_COL045
,SUM(TUA.COL046) AS SUM_COL046
,SUM(TUA.COL047) AS SUM_COL047
,SUM(TUA.COL048) AS SUM_COL048
,SUM(TUA.COL049) AS SUM_COL049
,SUM(TUA.COL050) AS SUM_COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = X.VAL
FROM BASE_AGGREGATE BA
CROSS APPLY
(
SELECT 'COL001', BA.SUM_COL001 UNION ALL
SELECT 'COL002', BA.SUM_COL002 UNION ALL
SELECT 'COL003', BA.SUM_COL003 UNION ALL
SELECT 'COL004', BA.SUM_COL004 UNION ALL
SELECT 'COL005', BA.SUM_COL005 UNION ALL
SELECT 'COL006', BA.SUM_COL006 UNION ALL
SELECT 'COL007', BA.SUM_COL007 UNION ALL
SELECT 'COL008', BA.SUM_COL008 UNION ALL
SELECT 'COL009', BA.SUM_COL009 UNION ALL
SELECT 'COL010', BA.SUM_COL010 UNION ALL
SELECT 'COL011', BA.SUM_COL011 UNION ALL
SELECT 'COL012', BA.SUM_COL012 UNION ALL
SELECT 'COL013', BA.SUM_COL013 UNION ALL
SELECT 'COL014', BA.SUM_COL014 UNION ALL
SELECT 'COL015', BA.SUM_COL015 UNION ALL
SELECT 'COL016', BA.SUM_COL016 UNION ALL
SELECT 'COL017', BA.SUM_COL017 UNION ALL
SELECT 'COL018', BA.SUM_COL018 UNION ALL
SELECT 'COL019', BA.SUM_COL019 UNION ALL
SELECT 'COL020', BA.SUM_COL020 UNION ALL
SELECT 'COL021', BA.SUM_COL021 UNION ALL
SELECT 'COL022', BA.SUM_COL022 UNION ALL
SELECT 'COL023', BA.SUM_COL023 UNION ALL
SELECT 'COL024', BA.SUM_COL024 UNION ALL
SELECT 'COL025', BA.SUM_COL025 UNION ALL
SELECT 'COL026', BA.SUM_COL026 UNION ALL
SELECT 'COL027', BA.SUM_COL027 UNION ALL
SELECT 'COL028', BA.SUM_COL028 UNION ALL
SELECT 'COL029', BA.SUM_COL029 UNION ALL
SELECT 'COL030', BA.SUM_COL030 UNION ALL
SELECT 'COL031', BA.SUM_COL031 UNION ALL
SELECT 'COL032', BA.SUM_COL032 UNION ALL
SELECT 'COL033', BA.SUM_COL033 UNION ALL
SELECT 'COL034', BA.SUM_COL034 UNION ALL
SELECT 'COL035', BA.SUM_COL035 UNION ALL
SELECT 'COL036', BA.SUM_COL036 UNION ALL
SELECT 'COL037', BA.SUM_COL037 UNION ALL
SELECT 'COL038', BA.SUM_COL038 UNION ALL
SELECT 'COL039', BA.SUM_COL039 UNION ALL
SELECT 'COL040', BA.SUM_COL040 UNION ALL
SELECT 'COL041', BA.SUM_COL041 UNION ALL
SELECT 'COL042', BA.SUM_COL042 UNION ALL
SELECT 'COL043', BA.SUM_COL043 UNION ALL
SELECT 'COL044', BA.SUM_COL044 UNION ALL
SELECT 'COL045', BA.SUM_COL045 UNION ALL
SELECT 'COL046', BA.SUM_COL046 UNION ALL
SELECT 'COL047', BA.SUM_COL047 UNION ALL
SELECT 'COL048', BA.SUM_COL048 UNION ALL
SELECT 'COL049', BA.SUM_COL049 UNION ALL
SELECT 'COL050', BA.SUM_COL050
) AS X(COL,VAL)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');
;WITH BASE_AGGREGATE AS
(
SELECT
SUM(TUA.COL001) AS SUM_COL001
,SUM(TUA.COL002) AS SUM_COL002
,SUM(TUA.COL003) AS SUM_COL003
,SUM(TUA.COL004) AS SUM_COL004
,SUM(TUA.COL005) AS SUM_COL005
,SUM(TUA.COL006) AS SUM_COL006
,SUM(TUA.COL007) AS SUM_COL007
,SUM(TUA.COL008) AS SUM_COL008
,SUM(TUA.COL009) AS SUM_COL009
,SUM(TUA.COL010) AS SUM_COL010
,SUM(TUA.COL011) AS SUM_COL011
,SUM(TUA.COL012) AS SUM_COL012
,SUM(TUA.COL013) AS SUM_COL013
,SUM(TUA.COL014) AS SUM_COL014
,SUM(TUA.COL015) AS SUM_COL015
,SUM(TUA.COL016) AS SUM_COL016
,SUM(TUA.COL017) AS SUM_COL017
,SUM(TUA.COL018) AS SUM_COL018
,SUM(TUA.COL019) AS SUM_COL019
,SUM(TUA.COL020) AS SUM_COL020
,SUM(TUA.COL021) AS SUM_COL021
,SUM(TUA.COL022) AS SUM_COL022
,SUM(TUA.COL023) AS SUM_COL023
,SUM(TUA.COL024) AS SUM_COL024
,SUM(TUA.COL025) AS SUM_COL025
,SUM(TUA.COL026) AS SUM_COL026
,SUM(TUA.COL027) AS SUM_COL027
,SUM(TUA.COL028) AS SUM_COL028
,SUM(TUA.COL029) AS SUM_COL029
,SUM(TUA.COL030) AS SUM_COL030
,SUM(TUA.COL031) AS SUM_COL031
,SUM(TUA.COL032) AS SUM_COL032
,SUM(TUA.COL033) AS SUM_COL033
,SUM(TUA.COL034) AS SUM_COL034
,SUM(TUA.COL035) AS SUM_COL035
,SUM(TUA.COL036) AS SUM_COL036
,SUM(TUA.COL037) AS SUM_COL037
,SUM(TUA.COL038) AS SUM_COL038
,SUM(TUA.COL039) AS SUM_COL039
,SUM(TUA.COL040) AS SUM_COL040
,SUM(TUA.COL041) AS SUM_COL041
,SUM(TUA.COL042) AS SUM_COL042
,SUM(TUA.COL043) AS SUM_COL043
,SUM(TUA.COL044) AS SUM_COL044
,SUM(TUA.COL045) AS SUM_COL045
,SUM(TUA.COL046) AS SUM_COL046
,SUM(TUA.COL047) AS SUM_COL047
,SUM(TUA.COL048) AS SUM_COL048
,SUM(TUA.COL049) AS SUM_COL049
,SUM(TUA.COL050) AS SUM_COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = X.VAL
FROM BASE_AGGREGATE BA
CROSS APPLY
(
SELECT 'COL001', BA.SUM_COL001 UNION ALL
SELECT 'COL002', BA.SUM_COL002 UNION ALL
SELECT 'COL003', BA.SUM_COL003 UNION ALL
SELECT 'COL004', BA.SUM_COL004 UNION ALL
SELECT 'COL005', BA.SUM_COL005 UNION ALL
SELECT 'COL006', BA.SUM_COL006 UNION ALL
SELECT 'COL007', BA.SUM_COL007 UNION ALL
SELECT 'COL008', BA.SUM_COL008 UNION ALL
SELECT 'COL009', BA.SUM_COL009 UNION ALL
SELECT 'COL010', BA.SUM_COL010 UNION ALL
SELECT 'COL011', BA.SUM_COL011 UNION ALL
SELECT 'COL012', BA.SUM_COL012 UNION ALL
SELECT 'COL013', BA.SUM_COL013 UNION ALL
SELECT 'COL014', BA.SUM_COL014 UNION ALL
SELECT 'COL015', BA.SUM_COL015 UNION ALL
SELECT 'COL016', BA.SUM_COL016 UNION ALL
SELECT 'COL017', BA.SUM_COL017 UNION ALL
SELECT 'COL018', BA.SUM_COL018 UNION ALL
SELECT 'COL019', BA.SUM_COL019 UNION ALL
SELECT 'COL020', BA.SUM_COL020 UNION ALL
SELECT 'COL021', BA.SUM_COL021 UNION ALL
SELECT 'COL022', BA.SUM_COL022 UNION ALL
SELECT 'COL023', BA.SUM_COL023 UNION ALL
SELECT 'COL024', BA.SUM_COL024 UNION ALL
SELECT 'COL025', BA.SUM_COL025 UNION ALL
SELECT 'COL026', BA.SUM_COL026 UNION ALL
SELECT 'COL027', BA.SUM_COL027 UNION ALL
SELECT 'COL028', BA.SUM_COL028 UNION ALL
SELECT 'COL029', BA.SUM_COL029 UNION ALL
SELECT 'COL030', BA.SUM_COL030 UNION ALL
SELECT 'COL031', BA.SUM_COL031 UNION ALL
SELECT 'COL032', BA.SUM_COL032 UNION ALL
SELECT 'COL033', BA.SUM_COL033 UNION ALL
SELECT 'COL034', BA.SUM_COL034 UNION ALL
SELECT 'COL035', BA.SUM_COL035 UNION ALL
SELECT 'COL036', BA.SUM_COL036 UNION ALL
SELECT 'COL037', BA.SUM_COL037 UNION ALL
SELECT 'COL038', BA.SUM_COL038 UNION ALL
SELECT 'COL039', BA.SUM_COL039 UNION ALL
SELECT 'COL040', BA.SUM_COL040 UNION ALL
SELECT 'COL041', BA.SUM_COL041 UNION ALL
SELECT 'COL042', BA.SUM_COL042 UNION ALL
SELECT 'COL043', BA.SUM_COL043 UNION ALL
SELECT 'COL044', BA.SUM_COL044 UNION ALL
SELECT 'COL045', BA.SUM_COL045 UNION ALL
SELECT 'COL046', BA.SUM_COL046 UNION ALL
SELECT 'COL047', BA.SUM_COL047 UNION ALL
SELECT 'COL048', BA.SUM_COL048 UNION ALL
SELECT 'COL049', BA.SUM_COL049 UNION ALL
SELECT 'COL050', BA.SUM_COL050
) AS X(COL,VAL)
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = SUM(X.VAL)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
CROSS APPLY
(
SELECT 'COL001', COL001 UNION ALL
SELECT 'COL002', COL002 UNION ALL
SELECT 'COL003', COL003 UNION ALL
SELECT 'COL004', COL004 UNION ALL
SELECT 'COL005', COL005 UNION ALL
SELECT 'COL006', COL006 UNION ALL
SELECT 'COL007', COL007 UNION ALL
SELECT 'COL008', COL008 UNION ALL
SELECT 'COL009', COL009 UNION ALL
SELECT 'COL010', COL010 UNION ALL
SELECT 'COL011', COL011 UNION ALL
SELECT 'COL012', COL012 UNION ALL
SELECT 'COL013', COL013 UNION ALL
SELECT 'COL014', COL014 UNION ALL
SELECT 'COL015', COL015 UNION ALL
SELECT 'COL016', COL016 UNION ALL
SELECT 'COL017', COL017 UNION ALL
SELECT 'COL018', COL018 UNION ALL
SELECT 'COL019', COL019 UNION ALL
SELECT 'COL020', COL020 UNION ALL
SELECT 'COL021', COL021 UNION ALL
SELECT 'COL022', COL022 UNION ALL
SELECT 'COL023', COL023 UNION ALL
SELECT 'COL024', COL024 UNION ALL
SELECT 'COL025', COL025 UNION ALL
SELECT 'COL026', COL026 UNION ALL
SELECT 'COL027', COL027 UNION ALL
SELECT 'COL028', COL028 UNION ALL
SELECT 'COL029', COL029 UNION ALL
SELECT 'COL030', COL030 UNION ALL
SELECT 'COL031', COL031 UNION ALL
SELECT 'COL032', COL032 UNION ALL
SELECT 'COL033', COL033 UNION ALL
SELECT 'COL034', COL034 UNION ALL
SELECT 'COL035', COL035 UNION ALL
SELECT 'COL036', COL036 UNION ALL
SELECT 'COL037', COL037 UNION ALL
SELECT 'COL038', COL038 UNION ALL
SELECT 'COL039', COL039 UNION ALL
SELECT 'COL040', COL040 UNION ALL
SELECT 'COL041', COL041 UNION ALL
SELECT 'COL042', COL042 UNION ALL
SELECT 'COL043', COL043 UNION ALL
SELECT 'COL044', COL044 UNION ALL
SELECT 'COL045', COL045 UNION ALL
SELECT 'COL046', COL046 UNION ALL
SELECT 'COL047', COL047 UNION ALL
SELECT 'COL048', COL048 UNION ALL
SELECT 'COL049', COL049 UNION ALL
SELECT 'COL050', COL050
) AS X(COL,VAL)
GROUP BY X.COL
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = SUM(X.VAL)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
CROSS APPLY
(
SELECT 'COL001', COL001 UNION ALL
SELECT 'COL002', COL002 UNION ALL
SELECT 'COL003', COL003 UNION ALL
SELECT 'COL004', COL004 UNION ALL
SELECT 'COL005', COL005 UNION ALL
SELECT 'COL006', COL006 UNION ALL
SELECT 'COL007', COL007 UNION ALL
SELECT 'COL008', COL008 UNION ALL
SELECT 'COL009', COL009 UNION ALL
SELECT 'COL010', COL010 UNION ALL
SELECT 'COL011', COL011 UNION ALL
SELECT 'COL012', COL012 UNION ALL
SELECT 'COL013', COL013 UNION ALL
SELECT 'COL014', COL014 UNION ALL
SELECT 'COL015', COL015 UNION ALL
SELECT 'COL016', COL016 UNION ALL
SELECT 'COL017', COL017 UNION ALL
SELECT 'COL018', COL018 UNION ALL
SELECT 'COL019', COL019 UNION ALL
SELECT 'COL020', COL020 UNION ALL
SELECT 'COL021', COL021 UNION ALL
SELECT 'COL022', COL022 UNION ALL
SELECT 'COL023', COL023 UNION ALL
SELECT 'COL024', COL024 UNION ALL
SELECT 'COL025', COL025 UNION ALL
SELECT 'COL026', COL026 UNION ALL
SELECT 'COL027', COL027 UNION ALL
SELECT 'COL028', COL028 UNION ALL
SELECT 'COL029', COL029 UNION ALL
SELECT 'COL030', COL030 UNION ALL
SELECT 'COL031', COL031 UNION ALL
SELECT 'COL032', COL032 UNION ALL
SELECT 'COL033', COL033 UNION ALL
SELECT 'COL034', COL034 UNION ALL
SELECT 'COL035', COL035 UNION ALL
SELECT 'COL036', COL036 UNION ALL
SELECT 'COL037', COL037 UNION ALL
SELECT 'COL038', COL038 UNION ALL
SELECT 'COL039', COL039 UNION ALL
SELECT 'COL040', COL040 UNION ALL
SELECT 'COL041', COL041 UNION ALL
SELECT 'COL042', COL042 UNION ALL
SELECT 'COL043', COL043 UNION ALL
SELECT 'COL044', COL044 UNION ALL
SELECT 'COL045', COL045 UNION ALL
SELECT 'COL046', COL046 UNION ALL
SELECT 'COL047', COL047 UNION ALL
SELECT 'COL048', COL048 UNION ALL
SELECT 'COL049', COL049 UNION ALL
SELECT 'COL050', COL050
) AS X(COL,VAL)
GROUP BY X.COL
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL006', SUM(TUA.COL006) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL007', SUM(TUA.COL007) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL008', SUM(TUA.COL008) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL009', SUM(TUA.COL009) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL010', SUM(TUA.COL010) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL011', SUM(TUA.COL011) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL012', SUM(TUA.COL012) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL013', SUM(TUA.COL013) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL014', SUM(TUA.COL014) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL015', SUM(TUA.COL015) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL016', SUM(TUA.COL016) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL017', SUM(TUA.COL017) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL018', SUM(TUA.COL018) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL019', SUM(TUA.COL019) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL020', SUM(TUA.COL020) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL021', SUM(TUA.COL021) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL022', SUM(TUA.COL022) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL023', SUM(TUA.COL023) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL024', SUM(TUA.COL024) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL025', SUM(TUA.COL025) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL026', SUM(TUA.COL026) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL027', SUM(TUA.COL027) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL028', SUM(TUA.COL028) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL029', SUM(TUA.COL029) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL030', SUM(TUA.COL030) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL031', SUM(TUA.COL031) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL032', SUM(TUA.COL032) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL033', SUM(TUA.COL033) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL034', SUM(TUA.COL034) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL035', SUM(TUA.COL035) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL036', SUM(TUA.COL036) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL037', SUM(TUA.COL037) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL038', SUM(TUA.COL038) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL039', SUM(TUA.COL039) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL040', SUM(TUA.COL040) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL041', SUM(TUA.COL041) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL042', SUM(TUA.COL042) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL043', SUM(TUA.COL043) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL044', SUM(TUA.COL044) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL045', SUM(TUA.COL045) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL046', SUM(TUA.COL046) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL047', SUM(TUA.COL047) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL048', SUM(TUA.COL048) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL049', SUM(TUA.COL049) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL050', SUM(TUA.COL050) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL006', SUM(TUA.COL006) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL007', SUM(TUA.COL007) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL008', SUM(TUA.COL008) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL009', SUM(TUA.COL009) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL010', SUM(TUA.COL010) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL011', SUM(TUA.COL011) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL012', SUM(TUA.COL012) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL013', SUM(TUA.COL013) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL014', SUM(TUA.COL014) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL015', SUM(TUA.COL015) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL016', SUM(TUA.COL016) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL017', SUM(TUA.COL017) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL018', SUM(TUA.COL018) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL019', SUM(TUA.COL019) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL020', SUM(TUA.COL020) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL021', SUM(TUA.COL021) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL022', SUM(TUA.COL022) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL023', SUM(TUA.COL023) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL024', SUM(TUA.COL024) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL025', SUM(TUA.COL025) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL026', SUM(TUA.COL026) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL027', SUM(TUA.COL027) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL028', SUM(TUA.COL028) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL029', SUM(TUA.COL029) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL030', SUM(TUA.COL030) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL031', SUM(TUA.COL031) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL032', SUM(TUA.COL032) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL033', SUM(TUA.COL033) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL034', SUM(TUA.COL034) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL035', SUM(TUA.COL035) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL036', SUM(TUA.COL036) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL037', SUM(TUA.COL037) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL038', SUM(TUA.COL038) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL039', SUM(TUA.COL039) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL040', SUM(TUA.COL040) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL041', SUM(TUA.COL041) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL042', SUM(TUA.COL042) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL043', SUM(TUA.COL043) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL044', SUM(TUA.COL044) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL045', SUM(TUA.COL045) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL046', SUM(TUA.COL046) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL047', SUM(TUA.COL047) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL048', SUM(TUA.COL048) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL049', SUM(TUA.COL049) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL050', SUM(TUA.COL050) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');
SELECT
T.T_TXT
,MAX(T.T_CPU) - MIN(T.T_CPU) AS CPU
,MAX(T.T_IDLE) - MIN(T.T_IDLE) AS IDLE
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION;
Results
T_TXT CPU IDLE DURATION
---------------- ---------- ----------- -----------
PRE AGG MD 0 3250000 375000 897992
DRY RUN 1187500 3687500 1250374
PRE AGG MD 1 1750000 5375000 1810552
UNION ALL MD 0 19031250 1687500 5312294
UNION ALL MD 1 9187500 28312500 9612250
POST AGG MD 0 48593750 1906250 12939276
POST AGG MD 1 25218750 76156250 25938152
September 16, 2016 at 1:21 am
There is another way of getting a single scan plan and that is to use an inline tally table, here are two harnesses, 5 and 50 columns respectfully.
😎
5 column harness
USE TEEST;
GO
SET NOCOUNT ON;
--http://www.sqlservercentral.com/Forums/FindPost1817858.aspx
-- TEST HARNESS
DECLARE @timer TABLE (
T_TXT VARCHAR(50) NOT NULL
,T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())
,T_CPU FLOAT NOT NULL DEFAULT (@@CPU_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IDLE FLOAT NOT NULL DEFAULT (@@IDLE * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IO FLOAT NOT NULL DEFAULT (@@IO_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
);
--/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATA SET CREATION
INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');
IF OBJECT_ID(N'dbo.TBL_TEST_UNPIVOT_AGGREGATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE;
CREATE TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE
(
ROW_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_TEST_UNPIVOT_AGGREGATE_ROW_ID PRIMARY KEY CLUSTERED
,COL001 INT NOT NULL
,COL002 INT NOT NULL
,COL003 INT NOT NULL
,COL004 INT NOT NULL
,COL005 INT NOT NULL
);
-- Set the data sample's parameters
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @MAX_VALUE INT = 1025;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_UNPIVOT_AGGREGATE WITH (TABLOCK)
(
ROW_ID,COL001,COL002,COL003,COL004,COL005
)
SELECT
NM.N
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
FROM NUMS NM;
INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');
-- */
DECLARE @INT_BUCKET INT = 0;
DECLARE @VCHAR_BUCKET VARCHAR(12) = '';
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');
SELECT
@INT_BUCKET = TUA.ROW_ID
,@INT_BUCKET = TUA.COL001
,@INT_BUCKET = TUA.COL002
,@INT_BUCKET = TUA.COL003
,@INT_BUCKET = TUA.COL004
,@INT_BUCKET = TUA.COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA;
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');
;WITH BASE_AGGREGATE AS
(
SELECT
SUM(TUA.COL001) AS SUM_COL001
,SUM(TUA.COL002) AS SUM_COL002
,SUM(TUA.COL003) AS SUM_COL003
,SUM(TUA.COL004) AS SUM_COL004
,SUM(TUA.COL005) AS SUM_COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = X.VAL
FROM BASE_AGGREGATE BA
CROSS APPLY
(
SELECT 'COL001', BA.SUM_COL001 UNION ALL
SELECT 'COL002', BA.SUM_COL002 UNION ALL
SELECT 'COL003', BA.SUM_COL003 UNION ALL
SELECT 'COL004', BA.SUM_COL004 UNION ALL
SELECT 'COL005', BA.SUM_COL005
) AS X(COL,VAL)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');
;WITH BASE_AGGREGATE AS
(
SELECT
SUM(TUA.COL001) AS SUM_COL001
,SUM(TUA.COL002) AS SUM_COL002
,SUM(TUA.COL003) AS SUM_COL003
,SUM(TUA.COL004) AS SUM_COL004
,SUM(TUA.COL005) AS SUM_COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = X.VAL
FROM BASE_AGGREGATE BA
CROSS APPLY
(
SELECT 'COL001', BA.SUM_COL001 UNION ALL
SELECT 'COL002', BA.SUM_COL002 UNION ALL
SELECT 'COL003', BA.SUM_COL003 UNION ALL
SELECT 'COL004', BA.SUM_COL004 UNION ALL
SELECT 'COL005', BA.SUM_COL005
) AS X(COL,VAL)
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = SUM(X.VAL)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
CROSS APPLY
(
SELECT 'COL001', COL001 UNION ALL
SELECT 'COL002', COL002 UNION ALL
SELECT 'COL003', COL003 UNION ALL
SELECT 'COL004', COL004 UNION ALL
SELECT 'COL005', COL005
) AS X(COL,VAL)
GROUP BY X.COL
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = SUM(X.VAL)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
CROSS APPLY
(
SELECT 'COL001', COL001 UNION ALL
SELECT 'COL002', COL002 UNION ALL
SELECT 'COL003', COL003 UNION ALL
SELECT 'COL004', COL004 UNION ALL
SELECT 'COL005', COL005
) AS X(COL,VAL)
GROUP BY X.COL
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');
IF OBJECT_ID('tempdb..#RESULT1') IS NOT NULL DROP TABLE #RESULT1;
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 1');
CREATE TABLE #RESULT1 (
COL001 INT
,COL002 INT
,COL003 INT
,COL004 INT
,COL005 INT
)
INSERT INTO #RESULT1
SELECT
SUM(TUA.COL001)
, SUM(TUA.COL002)
, SUM(TUA.COL003)
, SUM(TUA.COL004)
, SUM(TUA.COL005)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', COL001 FROM #RESULT1 UNION ALL
SELECT 'COL002', COL002 FROM #RESULT1 UNION ALL
SELECT 'COL003', COL003 FROM #RESULT1 UNION ALL
SELECT 'COL004', COL004 FROM #RESULT1 UNION ALL
SELECT 'COL005', COL005 FROM #RESULT1
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 1');
IF OBJECT_ID('tempdb..#RESULT2') IS NOT NULL DROP TABLE #RESULT2;
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 0');
CREATE TABLE #RESULT2 (
COL001 INT
,COL002 INT
,COL003 INT
,COL004 INT
,COL005 INT
)
INSERT INTO #RESULT2
SELECT
SUM(TUA.COL001)
, SUM(TUA.COL002)
, SUM(TUA.COL003)
, SUM(TUA.COL004)
, SUM(TUA.COL005)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', COL001 FROM #RESULT2 UNION ALL
SELECT 'COL002', COL002 FROM #RESULT2 UNION ALL
SELECT 'COL003', COL003 FROM #RESULT2 UNION ALL
SELECT 'COL004', COL004 FROM #RESULT2 UNION ALL
SELECT 'COL005', COL005 FROM #RESULT2
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 0');
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 0');
;WITH BASE_DATA AS
(
SELECT
SUM(TUA.COL001) AS COL001
, SUM(TUA.COL002) AS COL002
, SUM(TUA.COL003) AS COL003
, SUM(TUA.COL004) AS COL004
, SUM(TUA.COL005) AS COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
,NUMS(N) AS (SELECT N FROM (VALUES
( 1),( 2),( 3),( 4),( 5)
) AS X(N))
,UNPIVOTED_SET(COL,VAL) AS
(
SELECT
CASE
WHEN NM.N = 1 THEN 'COL001'
WHEN NM.N = 2 THEN 'COL002'
WHEN NM.N = 3 THEN 'COL003'
WHEN NM.N = 4 THEN 'COL004'
WHEN NM.N = 5 THEN 'COL005'
END AS COL
,CASE
WHEN NM.N = 1 THEN BD.COL001
WHEN NM.N = 2 THEN BD.COL002
WHEN NM.N = 3 THEN BD.COL003
WHEN NM.N = 4 THEN BD.COL004
WHEN NM.N = 5 THEN BD.COL005
END AS VAL
FROM BASE_DATA BD
CROSS JOIN NUMS NM
)
SELECT
@VCHAR_BUCKET = US.COL
,@INT_BUCKET = US.VAL
FROM UNPIVOTED_SET US
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 0');
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 1');
;WITH BASE_DATA AS
(
SELECT
SUM(TUA.COL001) AS COL001
, SUM(TUA.COL002) AS COL002
, SUM(TUA.COL003) AS COL003
, SUM(TUA.COL004) AS COL004
, SUM(TUA.COL005) AS COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
,NUMS(N) AS (SELECT N FROM (VALUES
( 1),( 2),( 3),( 4),( 5)
) AS X(N))
,UNPIVOTED_SET(COL,VAL) AS
(
SELECT
CASE
WHEN NM.N = 1 THEN 'COL001'
WHEN NM.N = 2 THEN 'COL002'
WHEN NM.N = 3 THEN 'COL003'
WHEN NM.N = 4 THEN 'COL004'
WHEN NM.N = 5 THEN 'COL005'
END AS COL
,CASE
WHEN NM.N = 1 THEN BD.COL001
WHEN NM.N = 2 THEN BD.COL002
WHEN NM.N = 3 THEN BD.COL003
WHEN NM.N = 4 THEN BD.COL004
WHEN NM.N = 5 THEN BD.COL005
END AS VAL
FROM BASE_DATA BD
CROSS JOIN NUMS NM
)
SELECT
@VCHAR_BUCKET = US.COL
,@INT_BUCKET = US.VAL
FROM UNPIVOTED_SET US
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 1');
SELECT
T.T_TXT
,MAX(T.T_CPU) - MIN(T.T_CPU) AS CPU
,MAX(T.T_IDLE) - MIN(T.T_IDLE) AS IDLE
,MAX(T.T_IO) - MIN(T.T_IO) AS IO
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION;
5 column results
T_TXT CPU IDLE IO DURATION
---------------------- -------- --------- -------- -----------
DRY RUN 218750 656250 0 210534
TALLY MD 1 250000 750000 0 250030
PRE AGG MD 0 250000 625000 0 253252
PRE AGG MD 1 250000 750000 0 260050
UNION ALL TMP MD 1 281250 718750 0 280043
UNION ALL TMP MD 0 281250 843750 0 280050
TALLY MD 0 250000 750000 0 280496
UNION ALL MD 0 1218750 281250 0 372559
UNION ALL MD 1 593750 1906250 0 620099
POST AGG MD 0 3750000 750000 0 1178175
POST AGG MD 1 4937500 6437500 0 2878659
DATA SET GENERATION 3343750 9875000 31250 3411838
50 column harness
USE TEEST;
GO
SET NOCOUNT ON;
--http://www.sqlservercentral.com/Forums/FindPost1817858.aspx
-- TEST HARNESS
DECLARE @timer TABLE (
T_TXT VARCHAR(50) NOT NULL
,T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())
,T_CPU FLOAT NOT NULL DEFAULT (@@CPU_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IDLE FLOAT NOT NULL DEFAULT (@@IDLE * CONVERT(FLOAT,@@TIMETICKS,0))
,T_IO FLOAT NOT NULL DEFAULT (@@IO_BUSY * CONVERT(FLOAT,@@TIMETICKS,0))
);
--/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATA SET CREATION
INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');
IF OBJECT_ID(N'dbo.TBL_TEST_UNPIVOT_AGGREGATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE;
CREATE TABLE dbo.TBL_TEST_UNPIVOT_AGGREGATE
(
ROW_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_TEST_UNPIVOT_AGGREGATE_ROW_ID PRIMARY KEY CLUSTERED
,COL001 INT NOT NULL
,COL002 INT NOT NULL
,COL003 INT NOT NULL
,COL004 INT NOT NULL
,COL005 INT NOT NULL
,COL006 INT NOT NULL
,COL007 INT NOT NULL
,COL008 INT NOT NULL
,COL009 INT NOT NULL
,COL010 INT NOT NULL
,COL011 INT NOT NULL
,COL012 INT NOT NULL
,COL013 INT NOT NULL
,COL014 INT NOT NULL
,COL015 INT NOT NULL
,COL016 INT NOT NULL
,COL017 INT NOT NULL
,COL018 INT NOT NULL
,COL019 INT NOT NULL
,COL020 INT NOT NULL
,COL021 INT NOT NULL
,COL022 INT NOT NULL
,COL023 INT NOT NULL
,COL024 INT NOT NULL
,COL025 INT NOT NULL
,COL026 INT NOT NULL
,COL027 INT NOT NULL
,COL028 INT NOT NULL
,COL029 INT NOT NULL
,COL030 INT NOT NULL
,COL031 INT NOT NULL
,COL032 INT NOT NULL
,COL033 INT NOT NULL
,COL034 INT NOT NULL
,COL035 INT NOT NULL
,COL036 INT NOT NULL
,COL037 INT NOT NULL
,COL038 INT NOT NULL
,COL039 INT NOT NULL
,COL040 INT NOT NULL
,COL041 INT NOT NULL
,COL042 INT NOT NULL
,COL043 INT NOT NULL
,COL044 INT NOT NULL
,COL045 INT NOT NULL
,COL046 INT NOT NULL
,COL047 INT NOT NULL
,COL048 INT NOT NULL
,COL049 INT NOT NULL
,COL050 INT NOT NULL
);
-- Set the data sample's parameters
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @MAX_VALUE INT = 1025;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_TEST_UNPIVOT_AGGREGATE WITH (TABLOCK)
(
ROW_ID,COL001,COL002,COL003,COL004,COL005,COL006,COL007,COL008,COL009,COL010
,COL011,COL012,COL013,COL014,COL015,COL016,COL017,COL018,COL019,COL020
,COL021,COL022,COL023,COL024,COL025,COL026,COL027,COL028,COL029,COL030
,COL031,COL032,COL033,COL034,COL035,COL036,COL037,COL038,COL039,COL040
,COL041,COL042,COL043,COL044,COL045,COL046,COL047,COL048,COL049,COL050
)
SELECT
NM.N
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
,ABS(CHECKSUM(NEWID())) % @MAX_VALUE
FROM NUMS NM;
INSERT INTO @timer(T_TXT) VALUES ('DATA SET GENERATION');
-- */
DECLARE @INT_BUCKET INT = 0;
DECLARE @VCHAR_BUCKET VARCHAR(12) = '';
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');
SELECT
@INT_BUCKET = TUA.ROW_ID
,@INT_BUCKET = TUA.COL001
,@INT_BUCKET = TUA.COL002
,@INT_BUCKET = TUA.COL003
,@INT_BUCKET = TUA.COL004
,@INT_BUCKET = TUA.COL005
,@INT_BUCKET = TUA.COL006
,@INT_BUCKET = TUA.COL007
,@INT_BUCKET = TUA.COL008
,@INT_BUCKET = TUA.COL009
,@INT_BUCKET = TUA.COL010
,@INT_BUCKET = TUA.COL011
,@INT_BUCKET = TUA.COL012
,@INT_BUCKET = TUA.COL013
,@INT_BUCKET = TUA.COL014
,@INT_BUCKET = TUA.COL015
,@INT_BUCKET = TUA.COL016
,@INT_BUCKET = TUA.COL017
,@INT_BUCKET = TUA.COL018
,@INT_BUCKET = TUA.COL019
,@INT_BUCKET = TUA.COL020
,@INT_BUCKET = TUA.COL021
,@INT_BUCKET = TUA.COL022
,@INT_BUCKET = TUA.COL023
,@INT_BUCKET = TUA.COL024
,@INT_BUCKET = TUA.COL025
,@INT_BUCKET = TUA.COL026
,@INT_BUCKET = TUA.COL027
,@INT_BUCKET = TUA.COL028
,@INT_BUCKET = TUA.COL029
,@INT_BUCKET = TUA.COL030
,@INT_BUCKET = TUA.COL031
,@INT_BUCKET = TUA.COL032
,@INT_BUCKET = TUA.COL033
,@INT_BUCKET = TUA.COL034
,@INT_BUCKET = TUA.COL035
,@INT_BUCKET = TUA.COL036
,@INT_BUCKET = TUA.COL037
,@INT_BUCKET = TUA.COL038
,@INT_BUCKET = TUA.COL039
,@INT_BUCKET = TUA.COL040
,@INT_BUCKET = TUA.COL041
,@INT_BUCKET = TUA.COL042
,@INT_BUCKET = TUA.COL043
,@INT_BUCKET = TUA.COL044
,@INT_BUCKET = TUA.COL045
,@INT_BUCKET = TUA.COL046
,@INT_BUCKET = TUA.COL047
,@INT_BUCKET = TUA.COL048
,@INT_BUCKET = TUA.COL049
,@INT_BUCKET = TUA.COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA;
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');
;WITH BASE_AGGREGATE AS
(
SELECT
SUM(TUA.COL001) AS SUM_COL001
,SUM(TUA.COL002) AS SUM_COL002
,SUM(TUA.COL003) AS SUM_COL003
,SUM(TUA.COL004) AS SUM_COL004
,SUM(TUA.COL005) AS SUM_COL005
,SUM(TUA.COL006) AS SUM_COL006
,SUM(TUA.COL007) AS SUM_COL007
,SUM(TUA.COL008) AS SUM_COL008
,SUM(TUA.COL009) AS SUM_COL009
,SUM(TUA.COL010) AS SUM_COL010
,SUM(TUA.COL011) AS SUM_COL011
,SUM(TUA.COL012) AS SUM_COL012
,SUM(TUA.COL013) AS SUM_COL013
,SUM(TUA.COL014) AS SUM_COL014
,SUM(TUA.COL015) AS SUM_COL015
,SUM(TUA.COL016) AS SUM_COL016
,SUM(TUA.COL017) AS SUM_COL017
,SUM(TUA.COL018) AS SUM_COL018
,SUM(TUA.COL019) AS SUM_COL019
,SUM(TUA.COL020) AS SUM_COL020
,SUM(TUA.COL021) AS SUM_COL021
,SUM(TUA.COL022) AS SUM_COL022
,SUM(TUA.COL023) AS SUM_COL023
,SUM(TUA.COL024) AS SUM_COL024
,SUM(TUA.COL025) AS SUM_COL025
,SUM(TUA.COL026) AS SUM_COL026
,SUM(TUA.COL027) AS SUM_COL027
,SUM(TUA.COL028) AS SUM_COL028
,SUM(TUA.COL029) AS SUM_COL029
,SUM(TUA.COL030) AS SUM_COL030
,SUM(TUA.COL031) AS SUM_COL031
,SUM(TUA.COL032) AS SUM_COL032
,SUM(TUA.COL033) AS SUM_COL033
,SUM(TUA.COL034) AS SUM_COL034
,SUM(TUA.COL035) AS SUM_COL035
,SUM(TUA.COL036) AS SUM_COL036
,SUM(TUA.COL037) AS SUM_COL037
,SUM(TUA.COL038) AS SUM_COL038
,SUM(TUA.COL039) AS SUM_COL039
,SUM(TUA.COL040) AS SUM_COL040
,SUM(TUA.COL041) AS SUM_COL041
,SUM(TUA.COL042) AS SUM_COL042
,SUM(TUA.COL043) AS SUM_COL043
,SUM(TUA.COL044) AS SUM_COL044
,SUM(TUA.COL045) AS SUM_COL045
,SUM(TUA.COL046) AS SUM_COL046
,SUM(TUA.COL047) AS SUM_COL047
,SUM(TUA.COL048) AS SUM_COL048
,SUM(TUA.COL049) AS SUM_COL049
,SUM(TUA.COL050) AS SUM_COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = X.VAL
FROM BASE_AGGREGATE BA
CROSS APPLY
(
SELECT 'COL001', BA.SUM_COL001 UNION ALL
SELECT 'COL002', BA.SUM_COL002 UNION ALL
SELECT 'COL003', BA.SUM_COL003 UNION ALL
SELECT 'COL004', BA.SUM_COL004 UNION ALL
SELECT 'COL005', BA.SUM_COL005 UNION ALL
SELECT 'COL006', BA.SUM_COL006 UNION ALL
SELECT 'COL007', BA.SUM_COL007 UNION ALL
SELECT 'COL008', BA.SUM_COL008 UNION ALL
SELECT 'COL009', BA.SUM_COL009 UNION ALL
SELECT 'COL010', BA.SUM_COL010 UNION ALL
SELECT 'COL011', BA.SUM_COL011 UNION ALL
SELECT 'COL012', BA.SUM_COL012 UNION ALL
SELECT 'COL013', BA.SUM_COL013 UNION ALL
SELECT 'COL014', BA.SUM_COL014 UNION ALL
SELECT 'COL015', BA.SUM_COL015 UNION ALL
SELECT 'COL016', BA.SUM_COL016 UNION ALL
SELECT 'COL017', BA.SUM_COL017 UNION ALL
SELECT 'COL018', BA.SUM_COL018 UNION ALL
SELECT 'COL019', BA.SUM_COL019 UNION ALL
SELECT 'COL020', BA.SUM_COL020 UNION ALL
SELECT 'COL021', BA.SUM_COL021 UNION ALL
SELECT 'COL022', BA.SUM_COL022 UNION ALL
SELECT 'COL023', BA.SUM_COL023 UNION ALL
SELECT 'COL024', BA.SUM_COL024 UNION ALL
SELECT 'COL025', BA.SUM_COL025 UNION ALL
SELECT 'COL026', BA.SUM_COL026 UNION ALL
SELECT 'COL027', BA.SUM_COL027 UNION ALL
SELECT 'COL028', BA.SUM_COL028 UNION ALL
SELECT 'COL029', BA.SUM_COL029 UNION ALL
SELECT 'COL030', BA.SUM_COL030 UNION ALL
SELECT 'COL031', BA.SUM_COL031 UNION ALL
SELECT 'COL032', BA.SUM_COL032 UNION ALL
SELECT 'COL033', BA.SUM_COL033 UNION ALL
SELECT 'COL034', BA.SUM_COL034 UNION ALL
SELECT 'COL035', BA.SUM_COL035 UNION ALL
SELECT 'COL036', BA.SUM_COL036 UNION ALL
SELECT 'COL037', BA.SUM_COL037 UNION ALL
SELECT 'COL038', BA.SUM_COL038 UNION ALL
SELECT 'COL039', BA.SUM_COL039 UNION ALL
SELECT 'COL040', BA.SUM_COL040 UNION ALL
SELECT 'COL041', BA.SUM_COL041 UNION ALL
SELECT 'COL042', BA.SUM_COL042 UNION ALL
SELECT 'COL043', BA.SUM_COL043 UNION ALL
SELECT 'COL044', BA.SUM_COL044 UNION ALL
SELECT 'COL045', BA.SUM_COL045 UNION ALL
SELECT 'COL046', BA.SUM_COL046 UNION ALL
SELECT 'COL047', BA.SUM_COL047 UNION ALL
SELECT 'COL048', BA.SUM_COL048 UNION ALL
SELECT 'COL049', BA.SUM_COL049 UNION ALL
SELECT 'COL050', BA.SUM_COL050
) AS X(COL,VAL)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 1');
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');
;WITH BASE_AGGREGATE AS
(
SELECT
SUM(TUA.COL001) AS SUM_COL001
,SUM(TUA.COL002) AS SUM_COL002
,SUM(TUA.COL003) AS SUM_COL003
,SUM(TUA.COL004) AS SUM_COL004
,SUM(TUA.COL005) AS SUM_COL005
,SUM(TUA.COL006) AS SUM_COL006
,SUM(TUA.COL007) AS SUM_COL007
,SUM(TUA.COL008) AS SUM_COL008
,SUM(TUA.COL009) AS SUM_COL009
,SUM(TUA.COL010) AS SUM_COL010
,SUM(TUA.COL011) AS SUM_COL011
,SUM(TUA.COL012) AS SUM_COL012
,SUM(TUA.COL013) AS SUM_COL013
,SUM(TUA.COL014) AS SUM_COL014
,SUM(TUA.COL015) AS SUM_COL015
,SUM(TUA.COL016) AS SUM_COL016
,SUM(TUA.COL017) AS SUM_COL017
,SUM(TUA.COL018) AS SUM_COL018
,SUM(TUA.COL019) AS SUM_COL019
,SUM(TUA.COL020) AS SUM_COL020
,SUM(TUA.COL021) AS SUM_COL021
,SUM(TUA.COL022) AS SUM_COL022
,SUM(TUA.COL023) AS SUM_COL023
,SUM(TUA.COL024) AS SUM_COL024
,SUM(TUA.COL025) AS SUM_COL025
,SUM(TUA.COL026) AS SUM_COL026
,SUM(TUA.COL027) AS SUM_COL027
,SUM(TUA.COL028) AS SUM_COL028
,SUM(TUA.COL029) AS SUM_COL029
,SUM(TUA.COL030) AS SUM_COL030
,SUM(TUA.COL031) AS SUM_COL031
,SUM(TUA.COL032) AS SUM_COL032
,SUM(TUA.COL033) AS SUM_COL033
,SUM(TUA.COL034) AS SUM_COL034
,SUM(TUA.COL035) AS SUM_COL035
,SUM(TUA.COL036) AS SUM_COL036
,SUM(TUA.COL037) AS SUM_COL037
,SUM(TUA.COL038) AS SUM_COL038
,SUM(TUA.COL039) AS SUM_COL039
,SUM(TUA.COL040) AS SUM_COL040
,SUM(TUA.COL041) AS SUM_COL041
,SUM(TUA.COL042) AS SUM_COL042
,SUM(TUA.COL043) AS SUM_COL043
,SUM(TUA.COL044) AS SUM_COL044
,SUM(TUA.COL045) AS SUM_COL045
,SUM(TUA.COL046) AS SUM_COL046
,SUM(TUA.COL047) AS SUM_COL047
,SUM(TUA.COL048) AS SUM_COL048
,SUM(TUA.COL049) AS SUM_COL049
,SUM(TUA.COL050) AS SUM_COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = X.VAL
FROM BASE_AGGREGATE BA
CROSS APPLY
(
SELECT 'COL001', BA.SUM_COL001 UNION ALL
SELECT 'COL002', BA.SUM_COL002 UNION ALL
SELECT 'COL003', BA.SUM_COL003 UNION ALL
SELECT 'COL004', BA.SUM_COL004 UNION ALL
SELECT 'COL005', BA.SUM_COL005 UNION ALL
SELECT 'COL006', BA.SUM_COL006 UNION ALL
SELECT 'COL007', BA.SUM_COL007 UNION ALL
SELECT 'COL008', BA.SUM_COL008 UNION ALL
SELECT 'COL009', BA.SUM_COL009 UNION ALL
SELECT 'COL010', BA.SUM_COL010 UNION ALL
SELECT 'COL011', BA.SUM_COL011 UNION ALL
SELECT 'COL012', BA.SUM_COL012 UNION ALL
SELECT 'COL013', BA.SUM_COL013 UNION ALL
SELECT 'COL014', BA.SUM_COL014 UNION ALL
SELECT 'COL015', BA.SUM_COL015 UNION ALL
SELECT 'COL016', BA.SUM_COL016 UNION ALL
SELECT 'COL017', BA.SUM_COL017 UNION ALL
SELECT 'COL018', BA.SUM_COL018 UNION ALL
SELECT 'COL019', BA.SUM_COL019 UNION ALL
SELECT 'COL020', BA.SUM_COL020 UNION ALL
SELECT 'COL021', BA.SUM_COL021 UNION ALL
SELECT 'COL022', BA.SUM_COL022 UNION ALL
SELECT 'COL023', BA.SUM_COL023 UNION ALL
SELECT 'COL024', BA.SUM_COL024 UNION ALL
SELECT 'COL025', BA.SUM_COL025 UNION ALL
SELECT 'COL026', BA.SUM_COL026 UNION ALL
SELECT 'COL027', BA.SUM_COL027 UNION ALL
SELECT 'COL028', BA.SUM_COL028 UNION ALL
SELECT 'COL029', BA.SUM_COL029 UNION ALL
SELECT 'COL030', BA.SUM_COL030 UNION ALL
SELECT 'COL031', BA.SUM_COL031 UNION ALL
SELECT 'COL032', BA.SUM_COL032 UNION ALL
SELECT 'COL033', BA.SUM_COL033 UNION ALL
SELECT 'COL034', BA.SUM_COL034 UNION ALL
SELECT 'COL035', BA.SUM_COL035 UNION ALL
SELECT 'COL036', BA.SUM_COL036 UNION ALL
SELECT 'COL037', BA.SUM_COL037 UNION ALL
SELECT 'COL038', BA.SUM_COL038 UNION ALL
SELECT 'COL039', BA.SUM_COL039 UNION ALL
SELECT 'COL040', BA.SUM_COL040 UNION ALL
SELECT 'COL041', BA.SUM_COL041 UNION ALL
SELECT 'COL042', BA.SUM_COL042 UNION ALL
SELECT 'COL043', BA.SUM_COL043 UNION ALL
SELECT 'COL044', BA.SUM_COL044 UNION ALL
SELECT 'COL045', BA.SUM_COL045 UNION ALL
SELECT 'COL046', BA.SUM_COL046 UNION ALL
SELECT 'COL047', BA.SUM_COL047 UNION ALL
SELECT 'COL048', BA.SUM_COL048 UNION ALL
SELECT 'COL049', BA.SUM_COL049 UNION ALL
SELECT 'COL050', BA.SUM_COL050
) AS X(COL,VAL)
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('PRE AGG MD 0');
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = SUM(X.VAL)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
CROSS APPLY
(
SELECT 'COL001', COL001 UNION ALL
SELECT 'COL002', COL002 UNION ALL
SELECT 'COL003', COL003 UNION ALL
SELECT 'COL004', COL004 UNION ALL
SELECT 'COL005', COL005 UNION ALL
SELECT 'COL006', COL006 UNION ALL
SELECT 'COL007', COL007 UNION ALL
SELECT 'COL008', COL008 UNION ALL
SELECT 'COL009', COL009 UNION ALL
SELECT 'COL010', COL010 UNION ALL
SELECT 'COL011', COL011 UNION ALL
SELECT 'COL012', COL012 UNION ALL
SELECT 'COL013', COL013 UNION ALL
SELECT 'COL014', COL014 UNION ALL
SELECT 'COL015', COL015 UNION ALL
SELECT 'COL016', COL016 UNION ALL
SELECT 'COL017', COL017 UNION ALL
SELECT 'COL018', COL018 UNION ALL
SELECT 'COL019', COL019 UNION ALL
SELECT 'COL020', COL020 UNION ALL
SELECT 'COL021', COL021 UNION ALL
SELECT 'COL022', COL022 UNION ALL
SELECT 'COL023', COL023 UNION ALL
SELECT 'COL024', COL024 UNION ALL
SELECT 'COL025', COL025 UNION ALL
SELECT 'COL026', COL026 UNION ALL
SELECT 'COL027', COL027 UNION ALL
SELECT 'COL028', COL028 UNION ALL
SELECT 'COL029', COL029 UNION ALL
SELECT 'COL030', COL030 UNION ALL
SELECT 'COL031', COL031 UNION ALL
SELECT 'COL032', COL032 UNION ALL
SELECT 'COL033', COL033 UNION ALL
SELECT 'COL034', COL034 UNION ALL
SELECT 'COL035', COL035 UNION ALL
SELECT 'COL036', COL036 UNION ALL
SELECT 'COL037', COL037 UNION ALL
SELECT 'COL038', COL038 UNION ALL
SELECT 'COL039', COL039 UNION ALL
SELECT 'COL040', COL040 UNION ALL
SELECT 'COL041', COL041 UNION ALL
SELECT 'COL042', COL042 UNION ALL
SELECT 'COL043', COL043 UNION ALL
SELECT 'COL044', COL044 UNION ALL
SELECT 'COL045', COL045 UNION ALL
SELECT 'COL046', COL046 UNION ALL
SELECT 'COL047', COL047 UNION ALL
SELECT 'COL048', COL048 UNION ALL
SELECT 'COL049', COL049 UNION ALL
SELECT 'COL050', COL050
) AS X(COL,VAL)
GROUP BY X.COL
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 1');
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');
SELECT
@VCHAR_BUCKET = X.COL
,@INT_BUCKET = SUM(X.VAL)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
CROSS APPLY
(
SELECT 'COL001', COL001 UNION ALL
SELECT 'COL002', COL002 UNION ALL
SELECT 'COL003', COL003 UNION ALL
SELECT 'COL004', COL004 UNION ALL
SELECT 'COL005', COL005 UNION ALL
SELECT 'COL006', COL006 UNION ALL
SELECT 'COL007', COL007 UNION ALL
SELECT 'COL008', COL008 UNION ALL
SELECT 'COL009', COL009 UNION ALL
SELECT 'COL010', COL010 UNION ALL
SELECT 'COL011', COL011 UNION ALL
SELECT 'COL012', COL012 UNION ALL
SELECT 'COL013', COL013 UNION ALL
SELECT 'COL014', COL014 UNION ALL
SELECT 'COL015', COL015 UNION ALL
SELECT 'COL016', COL016 UNION ALL
SELECT 'COL017', COL017 UNION ALL
SELECT 'COL018', COL018 UNION ALL
SELECT 'COL019', COL019 UNION ALL
SELECT 'COL020', COL020 UNION ALL
SELECT 'COL021', COL021 UNION ALL
SELECT 'COL022', COL022 UNION ALL
SELECT 'COL023', COL023 UNION ALL
SELECT 'COL024', COL024 UNION ALL
SELECT 'COL025', COL025 UNION ALL
SELECT 'COL026', COL026 UNION ALL
SELECT 'COL027', COL027 UNION ALL
SELECT 'COL028', COL028 UNION ALL
SELECT 'COL029', COL029 UNION ALL
SELECT 'COL030', COL030 UNION ALL
SELECT 'COL031', COL031 UNION ALL
SELECT 'COL032', COL032 UNION ALL
SELECT 'COL033', COL033 UNION ALL
SELECT 'COL034', COL034 UNION ALL
SELECT 'COL035', COL035 UNION ALL
SELECT 'COL036', COL036 UNION ALL
SELECT 'COL037', COL037 UNION ALL
SELECT 'COL038', COL038 UNION ALL
SELECT 'COL039', COL039 UNION ALL
SELECT 'COL040', COL040 UNION ALL
SELECT 'COL041', COL041 UNION ALL
SELECT 'COL042', COL042 UNION ALL
SELECT 'COL043', COL043 UNION ALL
SELECT 'COL044', COL044 UNION ALL
SELECT 'COL045', COL045 UNION ALL
SELECT 'COL046', COL046 UNION ALL
SELECT 'COL047', COL047 UNION ALL
SELECT 'COL048', COL048 UNION ALL
SELECT 'COL049', COL049 UNION ALL
SELECT 'COL050', COL050
) AS X(COL,VAL)
GROUP BY X.COL
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('POST AGG MD 0');
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL006', SUM(TUA.COL006) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL007', SUM(TUA.COL007) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL008', SUM(TUA.COL008) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL009', SUM(TUA.COL009) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL010', SUM(TUA.COL010) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL011', SUM(TUA.COL011) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL012', SUM(TUA.COL012) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL013', SUM(TUA.COL013) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL014', SUM(TUA.COL014) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL015', SUM(TUA.COL015) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL016', SUM(TUA.COL016) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL017', SUM(TUA.COL017) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL018', SUM(TUA.COL018) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL019', SUM(TUA.COL019) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL020', SUM(TUA.COL020) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL021', SUM(TUA.COL021) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL022', SUM(TUA.COL022) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL023', SUM(TUA.COL023) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL024', SUM(TUA.COL024) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL025', SUM(TUA.COL025) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL026', SUM(TUA.COL026) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL027', SUM(TUA.COL027) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL028', SUM(TUA.COL028) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL029', SUM(TUA.COL029) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL030', SUM(TUA.COL030) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL031', SUM(TUA.COL031) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL032', SUM(TUA.COL032) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL033', SUM(TUA.COL033) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL034', SUM(TUA.COL034) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL035', SUM(TUA.COL035) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL036', SUM(TUA.COL036) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL037', SUM(TUA.COL037) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL038', SUM(TUA.COL038) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL039', SUM(TUA.COL039) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL040', SUM(TUA.COL040) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL041', SUM(TUA.COL041) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL042', SUM(TUA.COL042) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL043', SUM(TUA.COL043) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL044', SUM(TUA.COL044) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL045', SUM(TUA.COL045) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL046', SUM(TUA.COL046) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL047', SUM(TUA.COL047) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL048', SUM(TUA.COL048) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL049', SUM(TUA.COL049) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL050', SUM(TUA.COL050) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 0');
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', SUM(TUA.COL001) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL002', SUM(TUA.COL002) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL003', SUM(TUA.COL003) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL004', SUM(TUA.COL004) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL005', SUM(TUA.COL005) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL006', SUM(TUA.COL006) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL007', SUM(TUA.COL007) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL008', SUM(TUA.COL008) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL009', SUM(TUA.COL009) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL010', SUM(TUA.COL010) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL011', SUM(TUA.COL011) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL012', SUM(TUA.COL012) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL013', SUM(TUA.COL013) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL014', SUM(TUA.COL014) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL015', SUM(TUA.COL015) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL016', SUM(TUA.COL016) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL017', SUM(TUA.COL017) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL018', SUM(TUA.COL018) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL019', SUM(TUA.COL019) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL020', SUM(TUA.COL020) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL021', SUM(TUA.COL021) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL022', SUM(TUA.COL022) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL023', SUM(TUA.COL023) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL024', SUM(TUA.COL024) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL025', SUM(TUA.COL025) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL026', SUM(TUA.COL026) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL027', SUM(TUA.COL027) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL028', SUM(TUA.COL028) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL029', SUM(TUA.COL029) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL030', SUM(TUA.COL030) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL031', SUM(TUA.COL031) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL032', SUM(TUA.COL032) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL033', SUM(TUA.COL033) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL034', SUM(TUA.COL034) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL035', SUM(TUA.COL035) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL036', SUM(TUA.COL036) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL037', SUM(TUA.COL037) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL038', SUM(TUA.COL038) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL039', SUM(TUA.COL039) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL040', SUM(TUA.COL040) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL041', SUM(TUA.COL041) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL042', SUM(TUA.COL042) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL043', SUM(TUA.COL043) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL044', SUM(TUA.COL044) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL045', SUM(TUA.COL045) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL046', SUM(TUA.COL046) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL047', SUM(TUA.COL047) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL048', SUM(TUA.COL048) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL049', SUM(TUA.COL049) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA UNION ALL
SELECT 'COL050', SUM(TUA.COL050) FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL MD 1');
IF OBJECT_ID('tempdb..#RESULT1') IS NOT NULL DROP TABLE #RESULT1;
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 1');
CREATE TABLE #RESULT1 (
COL001 INT
,COL002 INT
,COL003 INT
,COL004 INT
,COL005 INT
,COL006 INT
,COL007 INT
,COL008 INT
,COL009 INT
,COL010 INT
,COL011 INT
,COL012 INT
,COL013 INT
,COL014 INT
,COL015 INT
,COL016 INT
,COL017 INT
,COL018 INT
,COL019 INT
,COL020 INT
,COL021 INT
,COL022 INT
,COL023 INT
,COL024 INT
,COL025 INT
,COL026 INT
,COL027 INT
,COL028 INT
,COL029 INT
,COL030 INT
,COL031 INT
,COL032 INT
,COL033 INT
,COL034 INT
,COL035 INT
,COL036 INT
,COL037 INT
,COL038 INT
,COL039 INT
,COL040 INT
,COL041 INT
,COL042 INT
,COL043 INT
,COL044 INT
,COL045 INT
,COL046 INT
,COL047 INT
,COL048 INT
,COL049 INT
,COL050 INT
)
INSERT INTO #RESULT1
SELECT
SUM(TUA.COL001)
, SUM(TUA.COL002)
, SUM(TUA.COL003)
, SUM(TUA.COL004)
, SUM(TUA.COL005)
, SUM(TUA.COL006)
, SUM(TUA.COL007)
, SUM(TUA.COL008)
, SUM(TUA.COL009)
, SUM(TUA.COL010)
, SUM(TUA.COL011)
, SUM(TUA.COL012)
, SUM(TUA.COL013)
, SUM(TUA.COL014)
, SUM(TUA.COL015)
, SUM(TUA.COL016)
, SUM(TUA.COL017)
, SUM(TUA.COL018)
, SUM(TUA.COL019)
, SUM(TUA.COL020)
, SUM(TUA.COL021)
, SUM(TUA.COL022)
, SUM(TUA.COL023)
, SUM(TUA.COL024)
, SUM(TUA.COL025)
, SUM(TUA.COL026)
, SUM(TUA.COL027)
, SUM(TUA.COL028)
, SUM(TUA.COL029)
, SUM(TUA.COL030)
, SUM(TUA.COL031)
, SUM(TUA.COL032)
, SUM(TUA.COL033)
, SUM(TUA.COL034)
, SUM(TUA.COL035)
, SUM(TUA.COL036)
, SUM(TUA.COL037)
, SUM(TUA.COL038)
, SUM(TUA.COL039)
, SUM(TUA.COL040)
, SUM(TUA.COL041)
, SUM(TUA.COL042)
, SUM(TUA.COL043)
, SUM(TUA.COL044)
, SUM(TUA.COL045)
, SUM(TUA.COL046)
, SUM(TUA.COL047)
, SUM(TUA.COL048)
, SUM(TUA.COL049)
, SUM(TUA.COL050)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', COL001 FROM #RESULT1 UNION ALL
SELECT 'COL002', COL002 FROM #RESULT1 UNION ALL
SELECT 'COL003', COL003 FROM #RESULT1 UNION ALL
SELECT 'COL004', COL004 FROM #RESULT1 UNION ALL
SELECT 'COL005', COL005 FROM #RESULT1 UNION ALL
SELECT 'COL006', COL006 FROM #RESULT1 UNION ALL
SELECT 'COL007', COL007 FROM #RESULT1 UNION ALL
SELECT 'COL008', COL008 FROM #RESULT1 UNION ALL
SELECT 'COL009', COL009 FROM #RESULT1 UNION ALL
SELECT 'COL010', COL010 FROM #RESULT1 UNION ALL
SELECT 'COL011', COL011 FROM #RESULT1 UNION ALL
SELECT 'COL012', COL012 FROM #RESULT1 UNION ALL
SELECT 'COL013', COL013 FROM #RESULT1 UNION ALL
SELECT 'COL014', COL014 FROM #RESULT1 UNION ALL
SELECT 'COL015', COL015 FROM #RESULT1 UNION ALL
SELECT 'COL016', COL016 FROM #RESULT1 UNION ALL
SELECT 'COL017', COL017 FROM #RESULT1 UNION ALL
SELECT 'COL018', COL018 FROM #RESULT1 UNION ALL
SELECT 'COL019', COL019 FROM #RESULT1 UNION ALL
SELECT 'COL020', COL020 FROM #RESULT1 UNION ALL
SELECT 'COL021', COL021 FROM #RESULT1 UNION ALL
SELECT 'COL022', COL022 FROM #RESULT1 UNION ALL
SELECT 'COL023', COL023 FROM #RESULT1 UNION ALL
SELECT 'COL024', COL024 FROM #RESULT1 UNION ALL
SELECT 'COL025', COL025 FROM #RESULT1 UNION ALL
SELECT 'COL026', COL026 FROM #RESULT1 UNION ALL
SELECT 'COL027', COL027 FROM #RESULT1 UNION ALL
SELECT 'COL028', COL028 FROM #RESULT1 UNION ALL
SELECT 'COL029', COL029 FROM #RESULT1 UNION ALL
SELECT 'COL030', COL030 FROM #RESULT1 UNION ALL
SELECT 'COL031', COL031 FROM #RESULT1 UNION ALL
SELECT 'COL032', COL032 FROM #RESULT1 UNION ALL
SELECT 'COL033', COL033 FROM #RESULT1 UNION ALL
SELECT 'COL034', COL034 FROM #RESULT1 UNION ALL
SELECT 'COL035', COL035 FROM #RESULT1 UNION ALL
SELECT 'COL036', COL036 FROM #RESULT1 UNION ALL
SELECT 'COL037', COL037 FROM #RESULT1 UNION ALL
SELECT 'COL038', COL038 FROM #RESULT1 UNION ALL
SELECT 'COL039', COL039 FROM #RESULT1 UNION ALL
SELECT 'COL040', COL040 FROM #RESULT1 UNION ALL
SELECT 'COL041', COL041 FROM #RESULT1 UNION ALL
SELECT 'COL042', COL042 FROM #RESULT1 UNION ALL
SELECT 'COL043', COL043 FROM #RESULT1 UNION ALL
SELECT 'COL044', COL044 FROM #RESULT1 UNION ALL
SELECT 'COL045', COL045 FROM #RESULT1 UNION ALL
SELECT 'COL046', COL046 FROM #RESULT1 UNION ALL
SELECT 'COL047', COL047 FROM #RESULT1 UNION ALL
SELECT 'COL048', COL048 FROM #RESULT1 UNION ALL
SELECT 'COL049', COL049 FROM #RESULT1 UNION ALL
SELECT 'COL050', COL050 FROM #RESULT1
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 1');
IF OBJECT_ID('tempdb..#RESULT2') IS NOT NULL DROP TABLE #RESULT2;
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 0');
CREATE TABLE #RESULT2 (
COL001 INT
,COL002 INT
,COL003 INT
,COL004 INT
,COL005 INT
,COL006 INT
,COL007 INT
,COL008 INT
,COL009 INT
,COL010 INT
,COL011 INT
,COL012 INT
,COL013 INT
,COL014 INT
,COL015 INT
,COL016 INT
,COL017 INT
,COL018 INT
,COL019 INT
,COL020 INT
,COL021 INT
,COL022 INT
,COL023 INT
,COL024 INT
,COL025 INT
,COL026 INT
,COL027 INT
,COL028 INT
,COL029 INT
,COL030 INT
,COL031 INT
,COL032 INT
,COL033 INT
,COL034 INT
,COL035 INT
,COL036 INT
,COL037 INT
,COL038 INT
,COL039 INT
,COL040 INT
,COL041 INT
,COL042 INT
,COL043 INT
,COL044 INT
,COL045 INT
,COL046 INT
,COL047 INT
,COL048 INT
,COL049 INT
,COL050 INT
)
INSERT INTO #RESULT2
SELECT
SUM(TUA.COL001)
, SUM(TUA.COL002)
, SUM(TUA.COL003)
, SUM(TUA.COL004)
, SUM(TUA.COL005)
, SUM(TUA.COL006)
, SUM(TUA.COL007)
, SUM(TUA.COL008)
, SUM(TUA.COL009)
, SUM(TUA.COL010)
, SUM(TUA.COL011)
, SUM(TUA.COL012)
, SUM(TUA.COL013)
, SUM(TUA.COL014)
, SUM(TUA.COL015)
, SUM(TUA.COL016)
, SUM(TUA.COL017)
, SUM(TUA.COL018)
, SUM(TUA.COL019)
, SUM(TUA.COL020)
, SUM(TUA.COL021)
, SUM(TUA.COL022)
, SUM(TUA.COL023)
, SUM(TUA.COL024)
, SUM(TUA.COL025)
, SUM(TUA.COL026)
, SUM(TUA.COL027)
, SUM(TUA.COL028)
, SUM(TUA.COL029)
, SUM(TUA.COL030)
, SUM(TUA.COL031)
, SUM(TUA.COL032)
, SUM(TUA.COL033)
, SUM(TUA.COL034)
, SUM(TUA.COL035)
, SUM(TUA.COL036)
, SUM(TUA.COL037)
, SUM(TUA.COL038)
, SUM(TUA.COL039)
, SUM(TUA.COL040)
, SUM(TUA.COL041)
, SUM(TUA.COL042)
, SUM(TUA.COL043)
, SUM(TUA.COL044)
, SUM(TUA.COL045)
, SUM(TUA.COL046)
, SUM(TUA.COL047)
, SUM(TUA.COL048)
, SUM(TUA.COL049)
, SUM(TUA.COL050)
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
;WITH BASE_DATA(COL,VAL) AS
(
SELECT 'COL001', COL001 FROM #RESULT2 UNION ALL
SELECT 'COL002', COL002 FROM #RESULT2 UNION ALL
SELECT 'COL003', COL003 FROM #RESULT2 UNION ALL
SELECT 'COL004', COL004 FROM #RESULT2 UNION ALL
SELECT 'COL005', COL005 FROM #RESULT2 UNION ALL
SELECT 'COL006', COL006 FROM #RESULT2 UNION ALL
SELECT 'COL007', COL007 FROM #RESULT2 UNION ALL
SELECT 'COL008', COL008 FROM #RESULT2 UNION ALL
SELECT 'COL009', COL009 FROM #RESULT2 UNION ALL
SELECT 'COL010', COL010 FROM #RESULT2 UNION ALL
SELECT 'COL011', COL011 FROM #RESULT2 UNION ALL
SELECT 'COL012', COL012 FROM #RESULT2 UNION ALL
SELECT 'COL013', COL013 FROM #RESULT2 UNION ALL
SELECT 'COL014', COL014 FROM #RESULT2 UNION ALL
SELECT 'COL015', COL015 FROM #RESULT2 UNION ALL
SELECT 'COL016', COL016 FROM #RESULT2 UNION ALL
SELECT 'COL017', COL017 FROM #RESULT2 UNION ALL
SELECT 'COL018', COL018 FROM #RESULT2 UNION ALL
SELECT 'COL019', COL019 FROM #RESULT2 UNION ALL
SELECT 'COL020', COL020 FROM #RESULT2 UNION ALL
SELECT 'COL021', COL021 FROM #RESULT2 UNION ALL
SELECT 'COL022', COL022 FROM #RESULT2 UNION ALL
SELECT 'COL023', COL023 FROM #RESULT2 UNION ALL
SELECT 'COL024', COL024 FROM #RESULT2 UNION ALL
SELECT 'COL025', COL025 FROM #RESULT2 UNION ALL
SELECT 'COL026', COL026 FROM #RESULT2 UNION ALL
SELECT 'COL027', COL027 FROM #RESULT2 UNION ALL
SELECT 'COL028', COL028 FROM #RESULT2 UNION ALL
SELECT 'COL029', COL029 FROM #RESULT2 UNION ALL
SELECT 'COL030', COL030 FROM #RESULT2 UNION ALL
SELECT 'COL031', COL031 FROM #RESULT2 UNION ALL
SELECT 'COL032', COL032 FROM #RESULT2 UNION ALL
SELECT 'COL033', COL033 FROM #RESULT2 UNION ALL
SELECT 'COL034', COL034 FROM #RESULT2 UNION ALL
SELECT 'COL035', COL035 FROM #RESULT2 UNION ALL
SELECT 'COL036', COL036 FROM #RESULT2 UNION ALL
SELECT 'COL037', COL037 FROM #RESULT2 UNION ALL
SELECT 'COL038', COL038 FROM #RESULT2 UNION ALL
SELECT 'COL039', COL039 FROM #RESULT2 UNION ALL
SELECT 'COL040', COL040 FROM #RESULT2 UNION ALL
SELECT 'COL041', COL041 FROM #RESULT2 UNION ALL
SELECT 'COL042', COL042 FROM #RESULT2 UNION ALL
SELECT 'COL043', COL043 FROM #RESULT2 UNION ALL
SELECT 'COL044', COL044 FROM #RESULT2 UNION ALL
SELECT 'COL045', COL045 FROM #RESULT2 UNION ALL
SELECT 'COL046', COL046 FROM #RESULT2 UNION ALL
SELECT 'COL047', COL047 FROM #RESULT2 UNION ALL
SELECT 'COL048', COL048 FROM #RESULT2 UNION ALL
SELECT 'COL049', COL049 FROM #RESULT2 UNION ALL
SELECT 'COL050', COL050 FROM #RESULT2
)
SELECT
@VCHAR_BUCKET = BD.COL
,@INT_BUCKET = BD.VAL
FROM BASE_DATA BD
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('UNION ALL TMP MD 0');
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 0');
;WITH BASE_DATA AS
(
SELECT
SUM(TUA.COL001) AS COL001
, SUM(TUA.COL002) AS COL002
, SUM(TUA.COL003) AS COL003
, SUM(TUA.COL004) AS COL004
, SUM(TUA.COL005) AS COL005
, SUM(TUA.COL006) AS COL006
, SUM(TUA.COL007) AS COL007
, SUM(TUA.COL008) AS COL008
, SUM(TUA.COL009) AS COL009
, SUM(TUA.COL010) AS COL010
, SUM(TUA.COL011) AS COL011
, SUM(TUA.COL012) AS COL012
, SUM(TUA.COL013) AS COL013
, SUM(TUA.COL014) AS COL014
, SUM(TUA.COL015) AS COL015
, SUM(TUA.COL016) AS COL016
, SUM(TUA.COL017) AS COL017
, SUM(TUA.COL018) AS COL018
, SUM(TUA.COL019) AS COL019
, SUM(TUA.COL020) AS COL020
, SUM(TUA.COL021) AS COL021
, SUM(TUA.COL022) AS COL022
, SUM(TUA.COL023) AS COL023
, SUM(TUA.COL024) AS COL024
, SUM(TUA.COL025) AS COL025
, SUM(TUA.COL026) AS COL026
, SUM(TUA.COL027) AS COL027
, SUM(TUA.COL028) AS COL028
, SUM(TUA.COL029) AS COL029
, SUM(TUA.COL030) AS COL030
, SUM(TUA.COL031) AS COL031
, SUM(TUA.COL032) AS COL032
, SUM(TUA.COL033) AS COL033
, SUM(TUA.COL034) AS COL034
, SUM(TUA.COL035) AS COL035
, SUM(TUA.COL036) AS COL036
, SUM(TUA.COL037) AS COL037
, SUM(TUA.COL038) AS COL038
, SUM(TUA.COL039) AS COL039
, SUM(TUA.COL040) AS COL040
, SUM(TUA.COL041) AS COL041
, SUM(TUA.COL042) AS COL042
, SUM(TUA.COL043) AS COL043
, SUM(TUA.COL044) AS COL044
, SUM(TUA.COL045) AS COL045
, SUM(TUA.COL046) AS COL046
, SUM(TUA.COL047) AS COL047
, SUM(TUA.COL048) AS COL048
, SUM(TUA.COL049) AS COL049
, SUM(TUA.COL050) AS COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
,NUMS(N) AS (SELECT N FROM (VALUES
( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),(10)
,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40)
,(41),(42),(43),(44),(45),(46),(47),(48),(49),(50)
) AS X(N))
,UNPIVOTED_SET(COL,VAL) AS
(
SELECT
CASE
WHEN NM.N = 1 THEN 'COL001'
WHEN NM.N = 2 THEN 'COL002'
WHEN NM.N = 3 THEN 'COL003'
WHEN NM.N = 4 THEN 'COL004'
WHEN NM.N = 5 THEN 'COL005'
WHEN NM.N = 6 THEN 'COL006'
WHEN NM.N = 7 THEN 'COL007'
WHEN NM.N = 8 THEN 'COL008'
WHEN NM.N = 9 THEN 'COL009'
WHEN NM.N = 10 THEN 'COL010'
WHEN NM.N = 11 THEN 'COL011'
WHEN NM.N = 12 THEN 'COL012'
WHEN NM.N = 13 THEN 'COL013'
WHEN NM.N = 14 THEN 'COL014'
WHEN NM.N = 15 THEN 'COL015'
WHEN NM.N = 16 THEN 'COL016'
WHEN NM.N = 17 THEN 'COL017'
WHEN NM.N = 18 THEN 'COL018'
WHEN NM.N = 19 THEN 'COL019'
WHEN NM.N = 20 THEN 'COL020'
WHEN NM.N = 21 THEN 'COL021'
WHEN NM.N = 22 THEN 'COL022'
WHEN NM.N = 23 THEN 'COL023'
WHEN NM.N = 24 THEN 'COL024'
WHEN NM.N = 25 THEN 'COL025'
WHEN NM.N = 26 THEN 'COL026'
WHEN NM.N = 27 THEN 'COL027'
WHEN NM.N = 28 THEN 'COL028'
WHEN NM.N = 29 THEN 'COL029'
WHEN NM.N = 30 THEN 'COL030'
WHEN NM.N = 31 THEN 'COL031'
WHEN NM.N = 32 THEN 'COL032'
WHEN NM.N = 33 THEN 'COL033'
WHEN NM.N = 34 THEN 'COL034'
WHEN NM.N = 35 THEN 'COL035'
WHEN NM.N = 36 THEN 'COL036'
WHEN NM.N = 37 THEN 'COL037'
WHEN NM.N = 38 THEN 'COL038'
WHEN NM.N = 39 THEN 'COL039'
WHEN NM.N = 40 THEN 'COL040'
WHEN NM.N = 41 THEN 'COL041'
WHEN NM.N = 42 THEN 'COL042'
WHEN NM.N = 43 THEN 'COL043'
WHEN NM.N = 44 THEN 'COL044'
WHEN NM.N = 45 THEN 'COL045'
WHEN NM.N = 46 THEN 'COL046'
WHEN NM.N = 47 THEN 'COL047'
WHEN NM.N = 48 THEN 'COL048'
WHEN NM.N = 49 THEN 'COL049'
WHEN NM.N = 50 THEN 'COL050'
END AS COL
,CASE
WHEN NM.N = 1 THEN BD.COL001
WHEN NM.N = 2 THEN BD.COL002
WHEN NM.N = 3 THEN BD.COL003
WHEN NM.N = 4 THEN BD.COL004
WHEN NM.N = 5 THEN BD.COL005
WHEN NM.N = 6 THEN BD.COL006
WHEN NM.N = 7 THEN BD.COL007
WHEN NM.N = 8 THEN BD.COL008
WHEN NM.N = 9 THEN BD.COL009
WHEN NM.N = 10 THEN BD.COL010
WHEN NM.N = 11 THEN BD.COL011
WHEN NM.N = 12 THEN BD.COL012
WHEN NM.N = 13 THEN BD.COL013
WHEN NM.N = 14 THEN BD.COL014
WHEN NM.N = 15 THEN BD.COL015
WHEN NM.N = 16 THEN BD.COL016
WHEN NM.N = 17 THEN BD.COL017
WHEN NM.N = 18 THEN BD.COL018
WHEN NM.N = 19 THEN BD.COL019
WHEN NM.N = 20 THEN BD.COL020
WHEN NM.N = 21 THEN BD.COL021
WHEN NM.N = 22 THEN BD.COL022
WHEN NM.N = 23 THEN BD.COL023
WHEN NM.N = 24 THEN BD.COL024
WHEN NM.N = 25 THEN BD.COL025
WHEN NM.N = 26 THEN BD.COL026
WHEN NM.N = 27 THEN BD.COL027
WHEN NM.N = 28 THEN BD.COL028
WHEN NM.N = 29 THEN BD.COL029
WHEN NM.N = 30 THEN BD.COL030
WHEN NM.N = 31 THEN BD.COL031
WHEN NM.N = 32 THEN BD.COL032
WHEN NM.N = 33 THEN BD.COL033
WHEN NM.N = 34 THEN BD.COL034
WHEN NM.N = 35 THEN BD.COL035
WHEN NM.N = 36 THEN BD.COL036
WHEN NM.N = 37 THEN BD.COL037
WHEN NM.N = 38 THEN BD.COL038
WHEN NM.N = 39 THEN BD.COL039
WHEN NM.N = 40 THEN BD.COL040
WHEN NM.N = 41 THEN BD.COL041
WHEN NM.N = 42 THEN BD.COL042
WHEN NM.N = 43 THEN BD.COL043
WHEN NM.N = 44 THEN BD.COL044
WHEN NM.N = 45 THEN BD.COL045
WHEN NM.N = 46 THEN BD.COL046
WHEN NM.N = 47 THEN BD.COL047
WHEN NM.N = 48 THEN BD.COL048
WHEN NM.N = 49 THEN BD.COL049
WHEN NM.N = 50 THEN BD.COL050
END AS VAL
FROM BASE_DATA BD
CROSS JOIN NUMS NM
)
SELECT
@VCHAR_BUCKET = US.COL
,@INT_BUCKET = US.VAL
FROM UNPIVOTED_SET US
OPTION (MAXDOP 0);
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 0');
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 1');
;WITH BASE_DATA AS
(
SELECT
SUM(TUA.COL001) AS COL001
, SUM(TUA.COL002) AS COL002
, SUM(TUA.COL003) AS COL003
, SUM(TUA.COL004) AS COL004
, SUM(TUA.COL005) AS COL005
, SUM(TUA.COL006) AS COL006
, SUM(TUA.COL007) AS COL007
, SUM(TUA.COL008) AS COL008
, SUM(TUA.COL009) AS COL009
, SUM(TUA.COL010) AS COL010
, SUM(TUA.COL011) AS COL011
, SUM(TUA.COL012) AS COL012
, SUM(TUA.COL013) AS COL013
, SUM(TUA.COL014) AS COL014
, SUM(TUA.COL015) AS COL015
, SUM(TUA.COL016) AS COL016
, SUM(TUA.COL017) AS COL017
, SUM(TUA.COL018) AS COL018
, SUM(TUA.COL019) AS COL019
, SUM(TUA.COL020) AS COL020
, SUM(TUA.COL021) AS COL021
, SUM(TUA.COL022) AS COL022
, SUM(TUA.COL023) AS COL023
, SUM(TUA.COL024) AS COL024
, SUM(TUA.COL025) AS COL025
, SUM(TUA.COL026) AS COL026
, SUM(TUA.COL027) AS COL027
, SUM(TUA.COL028) AS COL028
, SUM(TUA.COL029) AS COL029
, SUM(TUA.COL030) AS COL030
, SUM(TUA.COL031) AS COL031
, SUM(TUA.COL032) AS COL032
, SUM(TUA.COL033) AS COL033
, SUM(TUA.COL034) AS COL034
, SUM(TUA.COL035) AS COL035
, SUM(TUA.COL036) AS COL036
, SUM(TUA.COL037) AS COL037
, SUM(TUA.COL038) AS COL038
, SUM(TUA.COL039) AS COL039
, SUM(TUA.COL040) AS COL040
, SUM(TUA.COL041) AS COL041
, SUM(TUA.COL042) AS COL042
, SUM(TUA.COL043) AS COL043
, SUM(TUA.COL044) AS COL044
, SUM(TUA.COL045) AS COL045
, SUM(TUA.COL046) AS COL046
, SUM(TUA.COL047) AS COL047
, SUM(TUA.COL048) AS COL048
, SUM(TUA.COL049) AS COL049
, SUM(TUA.COL050) AS COL050
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
,NUMS(N) AS (SELECT N FROM (VALUES
( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),(10)
,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40)
,(41),(42),(43),(44),(45),(46),(47),(48),(49),(50)
) AS X(N))
,UNPIVOTED_SET(COL,VAL) AS
(
SELECT
CASE
WHEN NM.N = 1 THEN 'COL001'
WHEN NM.N = 2 THEN 'COL002'
WHEN NM.N = 3 THEN 'COL003'
WHEN NM.N = 4 THEN 'COL004'
WHEN NM.N = 5 THEN 'COL005'
WHEN NM.N = 6 THEN 'COL006'
WHEN NM.N = 7 THEN 'COL007'
WHEN NM.N = 8 THEN 'COL008'
WHEN NM.N = 9 THEN 'COL009'
WHEN NM.N = 10 THEN 'COL010'
WHEN NM.N = 11 THEN 'COL011'
WHEN NM.N = 12 THEN 'COL012'
WHEN NM.N = 13 THEN 'COL013'
WHEN NM.N = 14 THEN 'COL014'
WHEN NM.N = 15 THEN 'COL015'
WHEN NM.N = 16 THEN 'COL016'
WHEN NM.N = 17 THEN 'COL017'
WHEN NM.N = 18 THEN 'COL018'
WHEN NM.N = 19 THEN 'COL019'
WHEN NM.N = 20 THEN 'COL020'
WHEN NM.N = 21 THEN 'COL021'
WHEN NM.N = 22 THEN 'COL022'
WHEN NM.N = 23 THEN 'COL023'
WHEN NM.N = 24 THEN 'COL024'
WHEN NM.N = 25 THEN 'COL025'
WHEN NM.N = 26 THEN 'COL026'
WHEN NM.N = 27 THEN 'COL027'
WHEN NM.N = 28 THEN 'COL028'
WHEN NM.N = 29 THEN 'COL029'
WHEN NM.N = 30 THEN 'COL030'
WHEN NM.N = 31 THEN 'COL031'
WHEN NM.N = 32 THEN 'COL032'
WHEN NM.N = 33 THEN 'COL033'
WHEN NM.N = 34 THEN 'COL034'
WHEN NM.N = 35 THEN 'COL035'
WHEN NM.N = 36 THEN 'COL036'
WHEN NM.N = 37 THEN 'COL037'
WHEN NM.N = 38 THEN 'COL038'
WHEN NM.N = 39 THEN 'COL039'
WHEN NM.N = 40 THEN 'COL040'
WHEN NM.N = 41 THEN 'COL041'
WHEN NM.N = 42 THEN 'COL042'
WHEN NM.N = 43 THEN 'COL043'
WHEN NM.N = 44 THEN 'COL044'
WHEN NM.N = 45 THEN 'COL045'
WHEN NM.N = 46 THEN 'COL046'
WHEN NM.N = 47 THEN 'COL047'
WHEN NM.N = 48 THEN 'COL048'
WHEN NM.N = 49 THEN 'COL049'
WHEN NM.N = 50 THEN 'COL050'
END AS COL
,CASE
WHEN NM.N = 1 THEN BD.COL001
WHEN NM.N = 2 THEN BD.COL002
WHEN NM.N = 3 THEN BD.COL003
WHEN NM.N = 4 THEN BD.COL004
WHEN NM.N = 5 THEN BD.COL005
WHEN NM.N = 6 THEN BD.COL006
WHEN NM.N = 7 THEN BD.COL007
WHEN NM.N = 8 THEN BD.COL008
WHEN NM.N = 9 THEN BD.COL009
WHEN NM.N = 10 THEN BD.COL010
WHEN NM.N = 11 THEN BD.COL011
WHEN NM.N = 12 THEN BD.COL012
WHEN NM.N = 13 THEN BD.COL013
WHEN NM.N = 14 THEN BD.COL014
WHEN NM.N = 15 THEN BD.COL015
WHEN NM.N = 16 THEN BD.COL016
WHEN NM.N = 17 THEN BD.COL017
WHEN NM.N = 18 THEN BD.COL018
WHEN NM.N = 19 THEN BD.COL019
WHEN NM.N = 20 THEN BD.COL020
WHEN NM.N = 21 THEN BD.COL021
WHEN NM.N = 22 THEN BD.COL022
WHEN NM.N = 23 THEN BD.COL023
WHEN NM.N = 24 THEN BD.COL024
WHEN NM.N = 25 THEN BD.COL025
WHEN NM.N = 26 THEN BD.COL026
WHEN NM.N = 27 THEN BD.COL027
WHEN NM.N = 28 THEN BD.COL028
WHEN NM.N = 29 THEN BD.COL029
WHEN NM.N = 30 THEN BD.COL030
WHEN NM.N = 31 THEN BD.COL031
WHEN NM.N = 32 THEN BD.COL032
WHEN NM.N = 33 THEN BD.COL033
WHEN NM.N = 34 THEN BD.COL034
WHEN NM.N = 35 THEN BD.COL035
WHEN NM.N = 36 THEN BD.COL036
WHEN NM.N = 37 THEN BD.COL037
WHEN NM.N = 38 THEN BD.COL038
WHEN NM.N = 39 THEN BD.COL039
WHEN NM.N = 40 THEN BD.COL040
WHEN NM.N = 41 THEN BD.COL041
WHEN NM.N = 42 THEN BD.COL042
WHEN NM.N = 43 THEN BD.COL043
WHEN NM.N = 44 THEN BD.COL044
WHEN NM.N = 45 THEN BD.COL045
WHEN NM.N = 46 THEN BD.COL046
WHEN NM.N = 47 THEN BD.COL047
WHEN NM.N = 48 THEN BD.COL048
WHEN NM.N = 49 THEN BD.COL049
WHEN NM.N = 50 THEN BD.COL050
END AS VAL
FROM BASE_DATA BD
CROSS JOIN NUMS NM
)
SELECT
@VCHAR_BUCKET = US.COL
,@INT_BUCKET = US.VAL
FROM UNPIVOTED_SET US
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('TALLY MD 1');
SELECT
T.T_TXT
,MAX(T.T_CPU) - MIN(T.T_CPU) AS CPU
,MAX(T.T_IDLE) - MIN(T.T_IDLE) AS IDLE
,MAX(T.T_IO) - MIN(T.T_IO) AS IO
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION;
50 column results
T_TXT CPU IDLE IO DURATION
-------------------- --------- ---------- --------- -----------
PRE AGG MD 0 3125000 250000 0 855016
UNION ALL TMP MD 1 3125000 250000 0 856998
UNION ALL TMP MD 0 3093750 281250 0 873477
TALLY MD 0 3062500 437500 0 909602
DRY RUN 1156250 3468750 0 1188951
TALLY MD 1 1656250 4968750 0 1680166
PRE AGG MD 1 1750000 5125000 0 1781094
UNION ALL MD 0 15125000 750000 0 4075105
UNION ALL MD 1 7312500 22062500 0 7511316
POST AGG MD 0 38750000 2500000 0 10563680
DATA SET GENERATION 13625000 41093750 281250 14081678
POST AGG MD 1 26968750 62000000 31250 22776639
September 16, 2016 at 1:27 am
tripleAxe (9/15/2016)
Here is the UNION ALL query using a temp table so that we can have one pass at the input table as suggested by The Dixie Flatline in an earlier post.
I added this method to the test harness and also the one below which uses a tally table to get a single scan.
So far the three methods which are fastest are the Pre aggregated, Temp table and the Tally methods, slightly depending on the number of columns.
😎
;WITH BASE_DATA AS
(
SELECT
SUM(TUA.COL001) AS COL001
, SUM(TUA.COL002) AS COL002
, SUM(TUA.COL003) AS COL003
, SUM(TUA.COL004) AS COL004
, SUM(TUA.COL005) AS COL005
FROM dbo.TBL_TEST_UNPIVOT_AGGREGATE TUA
)
,NUMS(N) AS (SELECT N FROM (VALUES
( 1),( 2),( 3),( 4),( 5)
) AS X(N))
,UNPIVOTED_SET(COL,VAL) AS
(
SELECT
CASE
WHEN NM.N = 1 THEN 'COL001'
WHEN NM.N = 2 THEN 'COL002'
WHEN NM.N = 3 THEN 'COL003'
WHEN NM.N = 4 THEN 'COL004'
WHEN NM.N = 5 THEN 'COL005'
END AS COL
,CASE
WHEN NM.N = 1 THEN BD.COL001
WHEN NM.N = 2 THEN BD.COL002
WHEN NM.N = 3 THEN BD.COL003
WHEN NM.N = 4 THEN BD.COL004
WHEN NM.N = 5 THEN BD.COL005
END AS VAL
FROM BASE_DATA BD
CROSS JOIN NUMS NM
)
SELECT
@VCHAR_BUCKET = US.COL
,@INT_BUCKET = US.VAL
FROM UNPIVOTED_SET US;
Viewing 4 posts - 46 through 48 (of 48 total)
You must be logged in to reply to this topic. Login to reply