My apologies for not substantiating my previous posts earlier, slightly busy:-P Just had few minutes to piece together a quick test harness, please feel free to improve and add to the tests!
😎
USE tempdb;
GO
SET NOCOUNT ON;
--/*
IF OBJECT_ID(N'dbo.TBL_TEST_ACROSS_MULTI_COLUMN') IS NOT NULL DROP TABLE dbo.TBL_TEST_ACROSS_MULTI_COLUMN;
CREATE TABLE dbo.TBL_TEST_ACROSS_MULTI_COLUMN
(
TAMC_ID INT IDENTITY NOT NULL CONSTRAINT PK_DBO_TBL_TEST_ACROSS_MULTI_COLUMN_TAMC_ID PRIMARY KEY CLUSTERED
,COL01 DATE NOT NULL
,COL02 DATE NOT NULL
,COL03 DATE NOT NULL
,COL04 DATE NOT NULL
);
DECLARE @SAMPLE_SIZE INT = 1000000;
;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) 0 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_ACROSS_MULTI_COLUMN(COL01,COL02,COL03,COL04)
SELECT
DATEADD(DAY,ABS(CHECKSUM(NEWID())) % 43000,0)
,DATEADD(DAY,ABS(CHECKSUM(NEWID())) % 43000,0)
,DATEADD(DAY,ABS(CHECKSUM(NEWID())) % 43000,0)
,DATEADD(DAY,ABS(CHECKSUM(NEWID())) % 43000,0)
FROM NUMS NM;
-- */
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @OS_INFO TABLE (S_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
STEP_NAME VARCHAR(100) NOT NULL, ms_ticks BIGINT NOT NULL, process_kernel_time_ms BIGINT NOT NULL, process_user_time_ms BIGINT NOT NULL);
DECLARE @INT_BUCKET INT = 0;
DECLARE @DATE_BUCKET DATE = NULL;
DECLARE @CHAR_BUCKET VARCHAR(10) = '';
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'START' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
SELECT
@INT_BUCKET = MT.TAMC_ID
,@DATE_BUCKET = MT.COL01
,@DATE_BUCKET = MT.COL02
,@DATE_BUCKET = MT.COL03
,@DATE_BUCKET = MT.COL04
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN AS MT;
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'DRY RUN' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('TOP VALUES');
SELECT
@DATE_BUCKET = MA.MaxDate
,@CHAR_BUCKET = MA.ColName
,@INT_BUCKET = MT.TAMC_ID
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN AS MT
CROSS APPLY
(
SELECT TOP(1) VA.LName, VA.LDate
FROM (VALUES ('COL01',MT.COL01)
,('COL02',MT.COL02)
,('COL03',MT.COL03)
,('COL04',MT.COL04)
) VA(LName, LDate)
ORDER BY VA.LDate DESC
) AS MA(ColName, MaxDate)
--OPTION (MAXDOP 1)
;
INSERT INTO @timer(T_TEXT) VALUES('TOP VALUES');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'TOP VALUES' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('MAX GROUP');
SELECT
@INT_BUCKET = TC.TAMC_ID
,@DATE_BUCKET = MAX(MXC.COLVAL) --AS MXVAL
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN TC
CROSS APPLY
(
SELECT 'COL01', TC.COL01 UNION ALL
SELECT 'COL02', TC.COL02 UNION ALL
SELECT 'COL03', TC.COL03 UNION ALL
SELECT 'COL04', TC.COL04
) AS MXC(COLNAME,COLVAL)
GROUP BY TC.TAMC_ID;
INSERT INTO @timer(T_TEXT) VALUES('MAX GROUP');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'MAX GROUP' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('DOUBLE CROSS');
SELECT
@INT_BUCKET = TC.TAMC_ID
,@CHAR_BUCKET = TMC.COLNAME
,@DATE_BUCKET =TMC.COLVAL
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN TC
CROSS APPLY
(
SELECT 'COL01', TC.COL01 UNION ALL
SELECT 'COL02', TC.COL02 UNION ALL
SELECT 'COL03', TC.COL03 UNION ALL
SELECT 'COL04', TC.COL04
) AS MXC(COLNAME,COLVAL)
CROSS APPLY
(
SELECT TOP(1) MXC.COLNAME
,MXC.COLVAL
ORDER BY MXC.COLVAL DESC
)AS TMC(COLNAME,COLVAL);
INSERT INTO @timer(T_TEXT) VALUES('DOUBLE CROSS');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'DOUBLE CROSS' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('UNION ALL');
;WITH BASE_DATA(TAMC_ID,COLVAL) AS
(
SELECT
TC.TAMC_ID
,TC.COL01
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN TC
UNION ALL
SELECT
TC.TAMC_ID
,TC.COL02
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN TC
UNION ALL
SELECT
TC.TAMC_ID
,TC.COL03
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN TC
UNION ALL
SELECT
TC.TAMC_ID
,TC.COL04
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN TC
)
SELECT
@INT_BUCKET = BD.TAMC_ID
,@DATE_BUCKET =MAX(BD.COLVAL)
FROM BASE_DATA BD
GROUP BY BD.TAMC_ID;
INSERT INTO @timer(T_TEXT) VALUES('UNION ALL');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'UNION ALL' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('BINARY CONCAT');
SELECT
@INT_BUCKET = t.TAMC_ID
,@CHAR_BUCKET = CAST(SUBSTRING(MAX(x.DateValue), 5, 4) AS VARCHAR(2))
,@DATE_BUCKET = CAST(SUBSTRING(MAX(x.DateValue), 1, 4) AS DATE)
FROM
dbo.TBL_TEST_ACROSS_MULTI_COLUMN t
CROSS APPLY (VALUES
(CAST(t.COL01 AS BINARY(4)) + CAST('c1' AS BINARY(4))),
(CAST(t.COL02 AS BINARY(4)) + CAST('c2' AS BINARY(4))),
(CAST(t.COL03 AS BINARY(4)) + CAST('c3' AS BINARY(4))),
(CAST(t.COL04 AS BINARY(4)) + CAST('c4' AS BINARY(4))))
x (DateValue)
GROUP BY
t.TAMC_ID;
INSERT INTO @timer(T_TEXT) VALUES('BINARY CONCAT');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'BINARY CONCAT' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
INSERT INTO @timer(T_TEXT) VALUES('UNPIVOT');
SELECT
@INT_BUCKET = X.TAMC_ID
,@CHAR_BUCKET = X.L
,@DATE_BUCKET = X.Val
FROM
(
SELECT
dates.TAMC_ID,
dates.L,
dates.Val,
ROW_NUMBER() OVER (PARTITION BY dates.TAMC_ID ORDER BY dates.Val DESC) AS RID
FROM (
SELECT TAMC_ID,
COL01,
COL02,
COL03,
COL04
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN
) dt UNPIVOT ( Val FOR L IN (COL01,COL02,COL03,COL04) ) AS dates
) AS X
WHERE X.RID = 1;
INSERT INTO @timer(T_TEXT) VALUES('UNPIVOT');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'UNPIVOT' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
;WITH BASE_RESULT AS
(
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
)
,OS_INFO AS
(
SELECT
OT.STEP_NAME
,OT.ms_ticks - OI.ms_ticks AS MS_TICKS
,OT.process_kernel_time_ms - OI.process_kernel_time_ms AS PROC_KERNEL_MS
,OT.process_user_time_ms - OI.process_user_time_ms AS USER_MS
FROM @OS_INFO OI
INNER JOIN @OS_INFO OT
ON OI.S_ID = OT.S_ID - 1
)
SELECT
BR.T_TEXT
,BR.DURATION
,OI.MS_TICKS
,OI.USER_MS
,OI.PROC_KERNEL_MS
FROM BASE_RESULT BR
INNER JOIN OS_INFO OI
ON BR.T_TEXT = OI.STEP_NAME
ORDER BY BR.DURATION;
Results on my old 2nd gen i5 laptop (10^6 rows)
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
--------------- ----------- --------- -------- --------------------
DRY RUN 232014 232 234 0
MAX GROUP 919052 919 920 0
DOUBLE CROSS 1192069 1192 1186 0
BINARY CONCAT 1568090 1568 1575 0
UNION ALL 1791102 1791 1794 0
UNPIVOT 2229127 2229 7644 31
TOP VALUES 3275187 3275 4727 31
Results from a much beefier machine (3rd gen i7)
-----------------------------------------------------------
Sample Size: 1,000
-----------------------------------------------------------
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
------------- --------- -------- ------- --------------
DRY RUN 1000 1 0 0
TOP VALUES 2000 2 0 0
DOUBLE CROSS 2000 2 0 0
BINARY CONCAT 4000 4 0 0
UNPIVOT 5000 5 16 0
UNION ALL 5000 5 0 0
MAX GROUP 5000 5 15 0
-----------------------------------------------------------
Sample Size: 10,000
-----------------------------------------------------------
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
------------- --------- -------- ------- --------------
DRY RUN 2000 2 15 0
DOUBLE CROSS 14000 14 0 0
UNION ALL 15000 16 15 0
TOP VALUES 15000 15 0 0
MAX GROUP 16000 16 32 0
BINARY CONCAT 17000 17 31 0
UNPIVOT 35000 35 32 0
-----------------------------------------------------------
Sample Size: 100,000
-----------------------------------------------------------
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
------------- --------- -------- ------- --------------
DRY RUN 18000 18 16 0
TOP VALUES 44000 44 156 0
DOUBLE CROSS 101000 102 94 0
MAX GROUP 112000 112 109 0
UNPIVOT 112000 113 374 0
BINARY CONCAT 124000 123 125 0
UNION ALL 140000 140 140 0
-----------------------------------------------------------
Sample Size: 1,000,000
-----------------------------------------------------------
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
------------- --------- -------- ------- --------------
DRY RUN 173000 174 172 0
TOP VALUES 407000 407 1279 15
DOUBLE CROSS 651000 651 1404 16
MAX GROUP 1110000 1111 1108 0
UNPIVOT 1187000 1188 3900 31
BINARY CONCAT 1240000 1240 1248 0
UNION ALL 1381000 1381 1373 0
-----------------------------------------------------------
Sample Size: 10,000,000
-----------------------------------------------------------
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
------------- --------- -------- ------- --------------
DRY RUN 1765000 1766 1763 0
TOP VALUES 4145000 4145 11825 32
DOUBLE CROSS 5759000 5760 14228 374
MAX GROUP 8451000 8451 8439 0
BINARY CONCAT 12299000 12300 12293 0
UNION ALL 13594000 13593 13603 0
UNPIVOT 150459000 150460 45256 1498
-----------------------------------------------------------
Just as any testing this simple performance test shows that one size does not necessarily fit all. Obviously the tests are rather incomprehensive but they do portray a fairly strong trend.