Jeff Moden (11/15/2014)
Very clever.
😀
As you know, I'm one of those people that absolutely love Tally-Table-like constructs but, as I've also said, it's not a panacea of performance. In this case, it requires what I believe to be some unnecessary complexity and, although it performs in roughly the same amount of time, does require 3 times the number of reads on the million row example.
My suggestion would be to go with the simplest code in this case especially because it uses only a third of the IO even if it is logical reads.
I fully agree that the simpler method is better when working with a simple transposition, in those cases, the Tally CrossTab is an overkill, but it doesn't take much increase in complexity to make it more attractive. As an example, in the previous sample the transposition can be described as A(B_n,C_n) where n is the number of column pairs, each key(A) will produce n number of lines. By expanding the product to A(BC), that is all combinations of Aircraft and City, the CROSS APPLY UNION method requires either a mapping of the Cartesian join in the subquery, where the code quickly becomes somewhat illegible or an introduction of another CROSS APPLY, which seriously hurts the performance.
😎
Quick code sample for the previous data sample
NOTE: this is a comparison of two unpivoting/transposing methods, this code will NOT produce the correct results for the posted problem!
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @CHAR_BUCKET_01 VARCHAR(10) = '';
DECLARE @CHAR_BUCKET_02 VARCHAR(10) = '';
DECLARE @CHAR_BUCKET_03 VARCHAR(10) = '';
RAISERROR('CROSS APPLY UNION',1,1) WITH NOWAIT;
SET STATISTICS IO, TIME ON;
SELECT
@CHAR_BUCKET_01 = Airline
,@CHAR_BUCKET_02 = d.Aircraft
,@CHAR_BUCKET_03 = d.City
FROM dbo.TBL_SAMPLE_DATA s
CROSS APPLY (
SELECT Aircraft1, City1 UNION ALL
SELECT Aircraft1, City2 UNION ALL
SELECT Aircraft1, City3 UNION ALL
SELECT Aircraft2, City1 UNION ALL
SELECT Aircraft2, City2 UNION ALL
SELECT Aircraft2, City3 UNION ALL
SELECT Aircraft3, City1 UNION ALL
SELECT Aircraft3, City2 UNION ALL
SELECT Aircraft3, City3
) d (Aircraft, City);
SET STATISTICS TIME, IO OFF;
RAISERROR('CROSS APPLY UNION 2',1,1) WITH NOWAIT;
SET STATISTICS IO, TIME ON;
SELECT
@CHAR_BUCKET_01 = s.Airline
,@CHAR_BUCKET_02 = d.Aircraft
,@CHAR_BUCKET_03 = C.City
FROM dbo.TBL_SAMPLE_DATA s
CROSS APPLY (
SELECT Aircraft1 UNION ALL
SELECT Aircraft2 UNION ALL
SELECT Aircraft3
) d (Aircraft)
CROSS APPLY (
SELECT City1 UNION ALL
SELECT City2 UNION ALL
SELECT City3
) c (City)
SET STATISTICS TIME, IO OFF;
RAISERROR('TALLY CASE',1,1) WITH NOWAIT;
SET STATISTICS IO, TIME ON;
;WITH NUMA(N) AS (SELECT N FROM (VALUES (1),(2),(3))AS X(N))
, NUMB(N) AS (SELECT N FROM (VALUES (1),(2),(3))AS X(N))
SELECT
@CHAR_BUCKET_01 = SD.Airline
,@CHAR_BUCKET_02 = CASE
WHEN NA.N = 1 THEN SD.Aircraft1
WHEN NA.N = 2 THEN SD.Aircraft2
WHEN NA.N = 3 THEN SD.Aircraft3
END --AS Aircraft
,@CHAR_BUCKET_03 = CASE
WHEN NB.N = 1 THEN SD.City1
WHEN NB.N = 2 THEN SD.City2
WHEN NB.N = 3 THEN SD.City3
END --AS City
FROM dbo.TBL_SAMPLE_DATA SD
OUTER APPLY NUMA NA
OUTER APPLY NUMB NB
SET STATISTICS TIME, IO OFF;
RAISERROR('TALLY CASE 2',1,1) WITH NOWAIT;
SET STATISTICS IO, TIME ON;
;WITH NUMA(N) AS (SELECT N FROM (VALUES (1),(2),(3))AS X(N))
SELECT
@CHAR_BUCKET_01 = SD.Airline
,@CHAR_BUCKET_02 = CASE
WHEN NA.N = 1 THEN SD.Aircraft1
WHEN NA.N = 2 THEN SD.Aircraft2
WHEN NA.N = 3 THEN SD.Aircraft3
END --AS Aircraft
,@CHAR_BUCKET_03 = CASE
WHEN NB.N = 1 THEN SD.City1
WHEN NB.N = 2 THEN SD.City2
WHEN NB.N = 3 THEN SD.City3
END --AS City
FROM dbo.TBL_SAMPLE_DATA SD
OUTER APPLY NUMA NA
OUTER APPLY NUMA NB
SET STATISTICS TIME, IO OFF;
Output (10^6 rows)
CROSS APPLY UNION
Msg 50000, Level 1, State 1
Table 'TBL_SAMPLE_DATA'. Scan count 5, logical reads 6907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4463 ms, elapsed time = 1822 ms.
CROSS APPLY UNION 2
Msg 50000, Level 1, State 1
Table 'TBL_SAMPLE_DATA'. Scan count 5, logical reads 6907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5568 ms, elapsed time = 1917 ms.
TALLY CASE
Msg 50000, Level 1, State 1
Table 'TBL_SAMPLE_DATA'. Scan count 1, logical reads 6829, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1670 ms, elapsed time = 1685 ms.
TALLY CASE 2
Msg 50000, Level 1, State 1
Table 'TBL_SAMPLE_DATA'. Scan count 1, logical reads 6829, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1653 ms, elapsed time = 1662 ms.
Edit: added a NOTE