Eirikur Eiriksson (11/16/2014)

---

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

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.