January 19, 2016 at 10:17 am
As Mr. Magoo said... Thank you for testing Orlando. 🙂
January 19, 2016 at 12:09 pm
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.
January 19, 2016 at 12:12 pm
Jason A. Long (1/19/2016)
Eirikur Eiriksson (1/18/2016)
Nice idea but I see two potential problems, firstly the costly concatenation and substringing with the data type conversion is going to impact the performance probably more than what is gained by the potential stream aggregation in the execution plan. Secondly because of concatenation it is very unlikely to get a parallel plan which probably makes it perform similar to a normal union all for all columns. There are two flavours of unpivoting using cross apply, values and union all, the difference is that the former most likely goes parallel and the latter never goes parallel.😎
Edit: Correction
I can't comment on the above without testing on a larger data set. That said, the "top 1 / order by" method has a sort operator on an expression and the "binary concatenation" method puts the sort on the ID column... Which, in turn, means the sort operator can be eliminated by putting a clustered index on the ID column.
My thought (not proven w/o testing) is that the elimination sort operation should give the "binary concatenation" method the edge.
Jason, feel free to play with the test harness I just posted earlier, my finding is that an operation such as a sort has to be relatively very expensive in order for a multi column sorting compression (such as binary concatenation) to become viable.
😎
January 19, 2016 at 1:10 pm
Eirikur Eiriksson (1/19/2016)
Jason A. Long (1/19/2016)
Eirikur Eiriksson (1/18/2016)
Nice idea but I see two potential problems, firstly the costly concatenation and substringing with the data type conversion is going to impact the performance probably more than what is gained by the potential stream aggregation in the execution plan. Secondly because of concatenation it is very unlikely to get a parallel plan which probably makes it perform similar to a normal union all for all columns. There are two flavours of unpivoting using cross apply, values and union all, the difference is that the former most likely goes parallel and the latter never goes parallel.😎
Edit: Correction
I can't comment on the above without testing on a larger data set. That said, the "top 1 / order by" method has a sort operator on an expression and the "binary concatenation" method puts the sort on the ID column... Which, in turn, means the sort operator can be eliminated by putting a clustered index on the ID column.
My thought (not proven w/o testing) is that the elimination sort operation should give the "binary concatenation" method the edge.
Jason, feel free to play with the test harness I just posted earlier, my finding is that an operation such as a sort has to be relatively very expensive in order for a multi column sorting compression (such as binary concatenation) to become viable.
😎
That would appear to be the case. I'll play with it once I make it back to the house. I've been slammed all day so I haven't had to opportunity to do anything not work related. Thank you for providing an excelent harness. 🙂
January 19, 2016 at 2:36 pm
Throwing in the CASE using Eirikur's wonderful setup yielded this on one million rows on my machine (tangentially, I'm embarrassed that Eirikur's laptop is faster than my test server :blush:):
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
-------------- ----------- ---------- --------- ---------------
DRY RUN 317028 317 312 0
CASE 862074 862 860 0
DOUBLE CROSS 1254111 1256 2391 16
MAX GROUP 1604144 1605 1609 0
BINARY CONCAT 2161191 2161 2157 0
UNION ALL 2331207 2331 2343 0
TOP VALUES 3738339 3738 4657 0
UNPIVOT 4027363 4027 7343 62
The site's apparently not too happy about my posting the entire test harness with the CASE query added, so here's just its piece:
INSERT INTO @TIMER(T_TEXT) VALUES('CASE');
SELECT @INT_BUCKET =TAMC_ID,
@DATE_BUCKET=CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 THEN COL01
WHEN COL02>=COL03 AND COL02>=COL04 THEN COL02
WHEN COL03>=COL04 THEN COL03
ELSE COL04
END,
@CHAR_BUCKET=CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 THEN 'COL01'
WHEN COL02>=COL03 AND COL02>=COL04 THEN 'COL02'
WHEN COL03>=COL04 THEN 'COL03'
ELSE 'COL04'
END
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN;
INSERT INTO @TIMER(T_TEXT) VALUES('CASE');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'CASE' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI
Cheers!
January 19, 2016 at 2:56 pm
Jacob Wilkins (1/19/2016)
Throwing in the CASE using Eirikur's wonderful setup yielded this on one million rows on my machine (tangentially, I'm embarrassed that Eirikur's laptop is faster than my test server :blush:):
Thanks for this Jacob, plugged your snip into the harness and so far it's leading the race, good job indeed!
😎
Here is the complete test so far
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
INSERT INTO @TIMER(T_TEXT) VALUES('CASE');
SELECT @INT_BUCKET =TAMC_ID,
@DATE_BUCKET=CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 THEN COL01
WHEN COL02>=COL03 AND COL02>=COL04 THEN COL02
WHEN COL03>=COL04 THEN COL03
ELSE COL04
END,
@CHAR_BUCKET=CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 THEN 'COL01'
WHEN COL02>=COL03 AND COL02>=COL04 THEN 'COL02'
WHEN COL03>=COL04 THEN 'COL03'
ELSE 'COL04'
END
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN;
INSERT INTO @TIMER(T_TEXT) VALUES('CASE');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'CASE' 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;
And the results on my old laptop
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
--------------- ----------- --------- --------- --------------------
DRY RUN 230000 232 234 0
CASE 520000 527 531 0
TOP VALUES 830002 830 2527 0
MAX GROUP 900002 900 905 0
DOUBLE CROSS 1150001 1148 1139 0
BINARY CONCAT 1490002 1496 1482 0
UNION ALL 1559004 1560 1560 0
UNPIVOT 2202017 2196 7737 31
What is left is to verify that all the code brings back the correct result set although I don't think that is an issue.
The old laptop I'm using is a Dell 6220 but I've made few "adjustments", quite a nifty little thingy;-)
January 19, 2016 at 5:29 pm
Eirikur Eiriksson (1/19/2016)
Jacob Wilkins (1/19/2016)
Throwing in the CASE using Eirikur's wonderful setup yielded this on one million rows on my machine (tangentially, I'm embarrassed that Eirikur's laptop is faster than my test server :blush:):Thanks for this Jacob, plugged your snip into the harness and so far it's leading the race, good job indeed!
😎
As usual, things are never always true...on my desktop the CASE is still hands down winner for this test, but TOP VALUES is a very very very poor loser (14000 ms_ticks vs 200) to CASE, while on yours it does ok.
One thing that, of course, makes a massive difference even without code changes (at least on sql 2016) is placing the CASE statements in the table definition as PERSISTED COMPUTED columns. It makes no noticeable difference to the creation of the test data, which takes 5 seconds on my PC (with or without the computed columns), but halves the run time of CASE, whether you reference the computed columns or not.
The optimiser, in this case, swapped out the CASE statements for the computed columns 😀 (caveat: I believe that it is not guaranteed that the query engine will actually use the persisted values, but it's interesting to see how it can help sometimes)
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
,MAXDATE as (CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 THEN COL01
WHEN COL02>=COL03 AND COL02>=COL04 THEN COL02
WHEN COL03>=COL04 THEN COL03
ELSE COL04
END) PERSISTED
,MAXCOL as (CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 THEN 'COL01'
WHEN COL02>=COL03 AND COL02>=COL04 THEN 'COL02'
WHEN COL03>=COL04 THEN 'COL03'
ELSE 'COL04'
END) PERSISTED
);
Again, I didn't need to change the CASE query to see a benefit from this :w00t: and the execution plan for these two queries came out identical (apart from expression names).
SELECT @INT_BUCKET =TAMC_ID,
@DATE_BUCKET=
CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 AND COL01>=COL05 THEN COL01
WHEN COL02>=COL03 AND COL02>=COL04 AND COL02>=COL05 THEN COL02
WHEN COL03>=COL04 AND COL03>=COL05 THEN COL03
WHEN COL04>=COL05 THEN COL04
ELSE COL05
END,
@CHAR_BUCKET=
CASE WHEN COL01>=COL02 AND COL01>=COL03 AND COL01>=COL04 AND COL01>=COL05 THEN 'COL01'
WHEN COL02>=COL03 AND COL02>=COL04 AND COL02>=COL05 THEN 'COL02'
WHEN COL03>=COL04 AND COL03>=COL05 THEN 'COL03'
WHEN COL04>=COL05 THEN 'COL04'
ELSE 'COL05'
END
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN;
and
SELECT @INT_BUCKET =TAMC_ID,
@DATE_BUCKET=MAXDATE,
@CHAR_BUCKET=MAXCOL
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN;
I might try this again on my dev server at work tomorrow to see how that compares.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 19, 2016 at 5:36 pm
For those interest, here is the CASE query actual execution plan, where the optimiser has swapped out the CASE statement for the COMPUTED columns automatically.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 19, 2016 at 7:22 pm
This looks like a place where SQLCLR could come in handy. A function similar to MySQL's GREATEST is what we're looking for.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 19, 2016 at 7:40 pm
Orlando Colamatteo (1/19/2016)
This looks like a place where SQLCLR could come in handy. A function similar to MySQL's GREATEST is what we're looking for.
+1000...
Unfortunately a CLR doesn't get you out of having to define a preset number of input parameters... Hopefully we'll one day see native GREATEST & LEAST functions that will accommodate any number of parameters (like CHECKSUM or COALESCE).
January 19, 2016 at 8:28 pm
Jason A. Long (1/19/2016)
Orlando Colamatteo (1/19/2016)
This looks like a place where SQLCLR could come in handy. A function similar to MySQL's GREATEST is what we're looking for.+1000...
Unfortunately a CLR doesn't get you out of having to define a preset number of input parameters... Hopefully we'll one day see native GREATEST & LEAST functions that will accommodate any number of parameters (like CHECKSUM or COALESCE).
It is a shame that type of API is not available to us from an implementation standpoint, in SQLCLR nor in T-SQL. One could argue the notion that we are dealing with a DECLARATIVE language imposes that limitation but as you pointed out we have been teased with built-in functions that do this, like COALESCE. A similar shortfall IMHO is the lack of support for overloading.
These two limitations combined have me thinking of a set of table-valued functions like this that can be used with APPLY:
- GREATEST_OF_TWO_DATES(DATE_1, DATE_2) returns 1 row with 1 column containing the greatest value as a DATE
- GREATEST_OF_THREE_DATES(DATE_1, DATE_2, DATE_3) returns same
- and ...
- GREATEST_OF_TWO_INTEGERS(INTEGER_1, INTEGER_2) returns 1 row with 1 column containing the greatest integer
- GREATEST_OF_THREE_INTEGERS(INTEGER_1, INTEGER_2, INTEGER_3) returns same
- and ...
- GREATEST_OF_TWO_DATE_COLUMNS(COLUMN_NAME_1, DATE_1, COLUMN_NAME_2, DATE_2) returns 1 row with two columns, the column name and value for the greatest of the dates
- GREATEST_OF_THREE_NUMBER_COLUMNS(COLUMN_NAME_1, DATE_1, COLUMN_NAME_2, DATE_2, COLUMN_NAME_3, DATE_3) returns same
- and ...
This would be a simple set of logic to implement and I feel like these would compete well with T-SQL and possibly exceed it but the API restrictions make it a very tedious chore...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 20, 2016 at 7:29 am
Just a simple way I would try and get this:
CREATE TABLE #datetest
(
id varchar(8),
L1 date,
L2 date,
L3 date
);
INSERT INTO #datetest (id, L1, L2, L3)
VALUES ('1005', '1-1-16', '1-17-16', '1-10-16');
-- I did the max on the date here in case there is more than one row per ID
SELECT ID, MAX(L1) AS Max_Date, 'L1' AS Column_Name
INTO #datetest2
FROM #datetest
GROUP BY ID
UNION
SELECT ID, MAX(L2) AS Max_Date, 'L2' AS Column_Name
FROM #datetest
GROUP BY ID
UNION
SELECT ID, MAX(L3) AS Max_Date, 'L3' AS Column_Name
FROM #datetest
GROUP BY ID
;
SELECT L.ID, L.Max_Date, L.Column_Name
FROM #datetest2 L
INNER JOIN (SELECT ID, MAX(Max_Date) AS Max_Date
FROM #datetest2
GROUP BY ID) R
ON L.id = R.id
AND L.Max_Date = R.Max_Date
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 20, 2016 at 8:28 am
Eirikur Eiriksson (1/19/2016)
Jacob Wilkins (1/19/2016)
Throwing in the CASE using Eirikur's wonderful setup yielded this on one million rows on my machine (tangentially, I'm embarrassed that Eirikur's laptop is faster than my test server :blush:):Thanks for this Jacob, plugged your snip into the harness and so far it's leading the race, good job indeed!
😎
What is left is to verify that all the code brings back the correct result set although I don't think that is an issue.
The old laptop I'm using is a Dell 6220 but I've made few "adjustments", quite a nifty little thingy;-)
Alas, as I was drifting off to sleep last night I had a terrible realization (made more terrible by the fact that I was thinking about this before sleep :crazy:). The current CASE formulation will fall on its face if one of the columns is NULL. It needs an extra check for whether the value being compared is NULL. That brings its time up pretty noticeably, although it's still a bit faster than the other solutions on my machine.
T_TEXT DURATION MS_TICKS USER_MS PROC_KERNEL_MS
------------------- ----------- ---------- --------- --------------------
DRY RUN 316029 316 312 0
NULL-PROOFED CASE 1090086 1091 1093 0
DOUBLE CROSS 1315118 1316 2531 0
MAX GROUP 1649142 1649 1640 0
BINARY CONCAT 2205218 2205 2203 0
UNION ALL 2366206 2366 2344 0
TOP VALUES 3809346 3809 4954 0
UNPIVOT 4335398 4349 7360 109
Here's the updated CASE I used:
INSERT INTO @TIMER(T_TEXT) VALUES('NULL-PROOFED CASE');
SELECT @INT_BUCKET =TAMC_ID,
@DATE_BUCKET=CASE WHEN (COL01>=COL02 OR COL02 IS NULL) AND (COL01>=COL03 OR COL03 IS NULL) AND (COL01>=COL04 OR COL04 IS NULL) THEN COL01
WHEN (COL02>=COL03 OR COL03 IS NULL) AND (COL02>=COL04 OR COL04 IS NULL) THEN COL02
WHEN (COL03>=COL04 OR COL04 IS NULL) THEN COL03
ELSE COL04
END,
@CHAR_BUCKET=CASE WHEN (COL01>=COL02 OR COL02 IS NULL) AND (COL01>=COL03 OR COL03 IS NULL) AND (COL01>=COL04 OR COL04 IS NULL) THEN 'COL01'
WHEN (COL02>=COL03 OR COL03 IS NULL) AND (COL02>=COL04 OR COL04 IS NULL) THEN 'COL02'
WHEN (COL03>=COL04 OR COL04 IS NULL) THEN 'COL03'
ELSE 'COL04'
END
FROM dbo.TBL_TEST_ACROSS_MULTI_COLUMN;
INSERT INTO @TIMER(T_TEXT) VALUES('NULL-PROOFED CASE');
INSERT INTO @OS_INFO (STEP_NAME,ms_ticks,process_kernel_time_ms,process_user_time_ms)
SELECT
'NULL-PROOFED CASE' AS STEP_NAME
,OSI.ms_ticks
,OSI.process_kernel_time_ms
,OSI.process_user_time_ms
FROM sys.dm_os_sys_info OSI;
I still feel like there must be a better way of doing this (feelings are an accurate indicator, right? :-)). I know I can get the result of the CASE with the minimal number of comparisons (this task should require no more n-1 comparisons, but worst case with the current method is something like (n2-n)/2) by using nested CASE statements, but that's at a huge expense to readability and ease of adapting to fewer/more inputs. I went through the process of writing the ugly nested version up just to test, and it does shave about 10% off the time of the CASE, but the readability goes so far down I don't think it's worth it. I'll keep pondering.
Cheers!
January 20, 2016 at 8:49 am
below86 (1/20/2016)
Just a simple way I would try and get this:
CREATE TABLE #datetest
(
id varchar(8),
L1 date,
L2 date,
L3 date
);
INSERT INTO #datetest (id, L1, L2, L3)
VALUES ('1005', '1-1-16', '1-17-16', '1-10-16');
-- I did the max on the date here in case there is more than one row per ID
SELECT ID, MAX(L1) AS Max_Date, 'L1' AS Column_Name
INTO #datetest2
FROM #datetest
GROUP BY ID
UNION
SELECT ID, MAX(L2) AS Max_Date, 'L2' AS Column_Name
FROM #datetest
GROUP BY ID
UNION
SELECT ID, MAX(L3) AS Max_Date, 'L3' AS Column_Name
FROM #datetest
GROUP BY ID
;
SELECT L.ID, L.Max_Date, L.Column_Name
FROM #datetest2 L
INNER JOIN (SELECT ID, MAX(Max_Date) AS Max_Date
FROM #datetest2
GROUP BY ID) R
ON L.id = R.id
AND L.Max_Date = R.Max_Date
;
An equivalent but slightly better method using UNION ALL is covered in the test harness, mind you that using UNION forces the server to deduplicate the result set which can be quite expensive.
😎
January 20, 2016 at 8:58 am
Eirikur Eiriksson (1/20/2016)
below86 (1/20/2016)
Just a simple way I would try and get this:
CREATE TABLE #datetest
(
id varchar(8),
L1 date,
L2 date,
L3 date
);
INSERT INTO #datetest (id, L1, L2, L3)
VALUES ('1005', '1-1-16', '1-17-16', '1-10-16');
-- I did the max on the date here in case there is more than one row per ID
SELECT ID, MAX(L1) AS Max_Date, 'L1' AS Column_Name
INTO #datetest2
FROM #datetest
GROUP BY ID
UNION
SELECT ID, MAX(L2) AS Max_Date, 'L2' AS Column_Name
FROM #datetest
GROUP BY ID
UNION
SELECT ID, MAX(L3) AS Max_Date, 'L3' AS Column_Name
FROM #datetest
GROUP BY ID
;
SELECT L.ID, L.Max_Date, L.Column_Name
FROM #datetest2 L
INNER JOIN (SELECT ID, MAX(Max_Date) AS Max_Date
FROM #datetest2
GROUP BY ID) R
ON L.id = R.id
AND L.Max_Date = R.Max_Date
;
An equivalent but slightly better method using UNION ALL is covered in the test harness, mind you that using UNION forces the server to deduplicate the result set which can be quite expensive.
😎
Your right, I should have caught that it should have been a Union ALL. I didn't read through all the responses before posting by thoughts, next time I will. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply